SQL syntax not recognized in SQL2FetchXML converter

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
LVL 1
apollo7Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongSoftware Team LeadCommented:
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 GuideCommented:
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
apollo7Author 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?
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
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/
apollo7Author 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 GuideCommented:
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..
apollo7Author 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?
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
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)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
apollo7Author 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 GuideCommented:
Yes, that's correct.. Kindly let me know for more details..
apollo7Author Commented:
This answered my question, thanks
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Welcome, glad to point you in the right direction..
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Dynamics

From novice to tech pro — start learning today.