SQL syntax not recognized in SQL2FetchXML converter

apollo7
apollo7 used Ask the Experts™
on
Dynamics 365 9.1

We are trying to convert SSRS reports from SQL to FetchXML.  When running the following SQL through the SQL2FetchXML converter, the site crashes.  Note: the site works on other SQL code.

SELECT Cast(right(year(@StartDate),2) as Varchar(2)) +
  CASE
  WHEN Len(datepart(wk, @StartDate)) = 1 Then  '0' + Cast(datepart(wk, @StartDate) as varchar(2))
  ELSE Cast(datepart(wk, @StartDate) as varchar(2)) End WeekStart,
  Cast(right(year(@EndDate),2) as Varchar(2)) +
CASE
  WHEN Len(datepart(wk, @EndDate)) = 1 Then  '0' + Cast(datepart(wk, @EndDate) as varchar(2))
  ELSE Cast(datepart(wk, @EndDate) as varchar(2)) End WeekEnd

Open in new window


Can you see any SQL syntax errors in the above code?

Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Ryan ChongSoftware Team Lead

Commented:
SQL statement posted looks ok and no error

you can create a New Query in SSMS, paste into it and Parse (Ctrl + F5) it
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
Above SQL Query syntax is correct provided you have declared the variables @StartDate and @EndDate properly..
However, FetchXML converter doesn't support the usage of variables and hence your query isn't working..
Please be noted that FetchXML converter only works if the operation is supported by FetchXML, more details below..
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.
http://www.sql2fetchxml.com/Help.aspx

Author

Commented:
Thanks for your comment - FetchXML converter doesn't support the usage of variables.  

However, if I need to use variables, what is the accepted format supported by FetchXML?
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
I don't think you have any straight forward converter available to convert SQL with variables to FetchXML format..
But you can manually build FetchXML with Parameters(variables) like mentioned below..
https://blogs.msdn.microsoft.com/crm/2011/02/23/fetch-xml-based-reports-bits-pieces/

Author

Commented:
Thanks, that is a very helpful blog - based on what I read, I should be able to create the FetchXML that I need.  For example, what changes would be needed to the following SQL query to convert it to FetchXML?  Are there any SQL statements that cant be converted to FetchXML?

SELECT Cast(right(year(@StartDate),2) as Varchar(2)) +
  CASE
  WHEN Len(datepart(wk, @StartDate)) = 1 Then  '0' + Cast(datepart(wk, @StartDate) as varchar(2))
  ELSE Cast(datepart(wk, @StartDate) as varchar(2)) End WeekStart,
  Cast(right(year(@EndDate),2) as Varchar(2)) +
CASE
  WHEN Len(datepart(wk, @EndDate)) = 1 Then  '0' + Cast(datepart(wk, @EndDate) as varchar(2))
  ELSE Cast(datepart(wk, @EndDate) as varchar(2)) End WeekEnd

Open in new window

Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
Just checking your query again, there is no tables involved and hence I don't think you can apply these variables to your FetchXML..
Basically, FetchXML parameters can be assigned for the normal query with additional filter operations and the SQL Functions like DATEPART or CAST or CASE or RIGHT might not be supported as is in FetchXML..

Author

Commented:
Thank you, as you probably can tell, the SQL above is used to define a Start Date and End Date to define a Week.  So without @StartDate and @EndDate to capture the input from the report parameters, what can I use in FetchXML to define the beginning and ending of the week to be reported on?
SQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018
Commented:
Yes, I understand that you are capturing the start week count and end week count and the code works well in SQL..
But it isn't in the recognized format for FetchXML and hence would suggest the below:
1. Declare parameters as mentioned in the link above(reposting for reference)
https://blogs.msdn.microsoft.com/crm/2011/02/23/fetch-xml-based-reports-bits-pieces/
2. From the application layer do the week count comparison and pass the values to FetchXML to get it work..
(As I'm more of SQL person, don't know how to guide you on the application code level)

Author

Commented:
Thanks, I think I get it.  So from the blog, it appears that using pre-filtering can provide the values for the parameters.
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
Yes, that's correct.. Kindly let me know for more details..

Author

Commented:
This answered my question, thanks
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
Welcome, glad to point you in the right direction..

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial