SQL转换成FetchXML的工具网站 使用说明 RRS feed

  • 常规讨论

  • Charts and Reports in CRM are very powerful, a lot of people using Advanced Find to generate the FetchXML to help them get some ideas about writing FetchXML, but Advanced Find has some limitations, such as cannot aggregate, no table hint, no unique filter, cannot order, top number, alias, group by and etc.

    CRM里面的图表、报表都非常强大,很多人使用高级查找来生成FetchXML帮助他们编写相关查询语句。但是微软CRM中的高级查找有一些局限,并不能生成FetchXML支持的所有类型的查询语句,如表的nolock, 排序,top,别名,group by, 分页等等。


    Today, a new tool go online for those developers who knows SQL, but not familiar with FetchXML! Please go to to have a try.


    Any feedback will be appreciated.

     欢迎反馈及建议!也欢迎加入群149371424 (目前有400多微软CRM专业人士的QQ群) 进来讨论交流!

    Here is the FAQ:常见问题:

    • Q1: What's the table name for the query? 表名用什么?
    • A1: table name will be the entity name, such as "account", "contact", "task" etc. 用实体的逻辑名
    • Q2: What functionalities are supported by this tool? 这个工具支持哪些功能
    • A2: Features supported by FetchXML will be supported by this tool, but since FetchXML only support part of SQL features, so, please be aware of this. 这个工具支持所有fetchxml支持的查询功能
      • We can support: top, distinct, nolock, where, order by, inner join, left outer join, aggregate (count, max, min, avg, sum), group by, alias 
      • CRM functions ( using eq and x prefix, for example:  where createdon = xlastyear() or modifiedon=xyesterday(), if any parameters, you can put them in the function, like modifiedon = lastxweeks(4) or modifiedon = inorafterfiscalperiodandyear(4,2006)
      • For link entity conditions, they should be put along with the nearest join conditions, for example, select, c.firstname, c.lastname from account a inner join contact c on a.accountid=c.parentcustomerid and (contact related conditions) where (account related conditions) 当需要写连接实体的筛选条件时,我们需要把连接实体的条件写在on 的条件里面,如示例。


    Here are some samples:


    • Simple table query:

    Select name from account where name like '%Kingswaysoft%'

    • Query with top, nolock, order by

    Select top 10 name from account with (nolock) where creditlimit > 1000000 order by creditlimit

    • Query with Join and with alias

    select, c.firstname, c.lastname from account a inner join contact c on a.accountid=c.parentcustomerid and (c.telephone1 is not null or c.telephone2 is not null or c.telephone3 is not null) where a.creditlimit > 10000


    • Aggregate

    Select count(*) from account

    Select count(distinct name) from account

    Select avg(creditlimit) from account

    • Group by

    Select count(accountid) from account group by accountratingcode

    • Paging (using SQL 2012 syntax)

    select * from task order by subject offset 10 rows fetch next 10 rows only


    Send a mail to Weifeng
    Welcome to CRM QQ Super Group:149371424

    2013年3月15日 5:20