Help

What's SQL2FetchXML?

SQL2FetchXML is a utility that helps you convert SQL script to FetchXML, a proprietary query language that is used in Microsoft Dynamics CRM. The service is offered through www.sql2fetchxml.com website, which is maintained and supported by KingswaySoft, a software company specializing in offering integration software and solutions for Microsoft Dynamics CRM.

How does it work?

You would first visit http://www.sql2fetchxml.com website, where you can enter your SQL script and click the "Convert" button to convert it into FetchXML query.

It should be noted that the conversion is limited to the capabilities of FetchXML query itself. In other words, the converter will not be able to overcome any limitations that FetchXML might have.

How should I specify table and column names in SQL script?

Table name should be the corresponding CRM entity's logical name, such as "account", "contact", "task" etc. Likewise, you would use CRM field's logical name for the columns in your SQL script. Please note that CRM entity name and field name should both be in lower case. The converter will not convert the case for you, and there is no way that the utility can detect the correctness of them, so you should make sure the supplied entity name(s) and field name(s) are correct.

 Is the tool capable of converting any SQL script?

Yes and No. The utility is capable of converting any SQL script, as long as it is supported by FetchXML. For any SQL script that's not supported by FetchXML, the utility will simply not convert or the produced FetchXML cannot be executed in CRM.

What SQL keywords are supported ?

The supported SQL keywords include SELECT, FROM, WHERE, AND, OR, LIKE, GROUP BY, ORDER BY, AS, TOP, DISTINCT, NOLOCK, INNER JOIN, LEFT OUTER JOIN, ON, IN, etc.

Are aggregations supported?

Yes, aggregations are supported. You can use COUNT, MAX, MIN, AVG, and SUM in your SQL script.

Can I use CRM filtered views in my SQL script?

Yes, you can. The converter will remove any "Filtered" prefix from your SQL script.

How do we support FetchXML operators, such as next-x-weeks, last-year, today, yesterday, on, eq-userid, etc.?

Those FetchXML operators will be supported by converting them to SQL functions in the format that it doesn't contain the dash (-) characters. So next-x-weeks should be converted to nextxweeks(n), today to today(), eq-userid to equserid(), and so on. The following is an example using such SQL functions which will be converted to FetchXML operators after the conversion.

SELECT * FROM contact 
WHERE createdon = lastxmonths(6) 
  AND modifiedon = yesterday()
  AND ownerid = equserid()

In case that the FetchXML operator is a SQL keyword such as "on", "in", you would need to add a "x" character as prefix in order to avoid the conflicts, so it would be xon('2011-01-11T00:00:00'), such as

SELECT * FROM contact
WHERE createdon = xon('2011-01-11T00:00:00')

Can you give some samples?

Absolutely, the following are some typical samples.

• Simple entity query
  

  SELECT name FROM account 
  WHERE name LIKE '%KingswaySoft%'

• Query with top, nolock and order by
  

  SELECT TOP 10 name 
  FROM account WITH (nolock) 
  WHERE creditlimit > 1000000 
  ORDER BY creditlimit

• Query with Join and with alias
  

  SELECT a.name, 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

• Aggregation
  

  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