asked on

I have tried a number of ways to convert the SQL stored procedure code into Fetchxml but I am getting errors

Dynamics 365 9.1 online, CRM 2016 On prem, SSRS reports to Fetchxml

I am trying to convert an SSRS report to FetchXML that uses a stored procedure as the query type for the MSCRM_Datasource. The goal is to create Fetchxml to run the report on D365 Online.

I have tried a number of ways to convert the SQL stored procedure code into Fetchxml but I am getting errors.

This code converts into FetchXml without error:

select  attributename, value, attributevalue,langid,objecttypecode from account

Open in new window

But when I add other parts of the stored procedure code, I get the following errors:
Error: Error occurred when parsing the SQL script: Unsupported statement type: #stringmap

select  attributename, value, attributevalue,langid,objecttypecode
into #stringmap  from stringmapbase
where langId = '1033'

Open in new window

What is the best approach given a complicated sp that uses functions that are unsupported in fetchxml?
PowerBI? Convert the sp to other code?

Thanks

Chinmay Patel

Hi apollo7,

select  attributename, value, attributevalue,langid,objecttypecode
from stringmapbase
where langId = '1033'

Open in new window

into was the problem keyword. SQL's INTO keyword copies data to a new table. # indicates a local temp table. If your query is too complex and is using a lot of optionsets then this was very useful when you had a direct access to database.

With Fetch it has to be done on case on case basis. The above fetch will give you the necessary results. In case, you want to reuse it, using another dataset(in the same report) that has necessary optionsets is another easy way out.

Regards,
Chinmay.

apollo7

ASKER

Thanks Chinmay, this is excellent. I have put some other SQL code from the same report. Can you tell me if the unsupported SQL can be modified and converted to Fetch?

Error occurred when parsing the SQL script: Unsupported statement type: SiteReady

select value from stringmapbase where attributename = 'fei_install_site_ready' and langId = '1033' and attributevalue =  fei_install_site_ready SiteReady		
,iif(foa.fei_oa_config_status=1,'Yes','No' as ConfigFrozen
,dbo.fn_UTCToTzSpecificLocalTime(foa.fei_oa_config_freeze_date, 
			us.TimeZoneBias,
			us.TimeZoneDaylightBias,
			us.TimeZoneDaylightYear,
			us.TimeZoneDaylightMonth,
			us.TimeZoneDaylightDay,
			us.TimeZoneDaylightHour,
			us.TimeZoneDaylightMinute,
			us.TimeZoneDaylightSecond,
			0,
			us.TimeZoneDaylightDayOfWeek,
			us.TimeZoneStandardBias,
			us.TimeZoneStandardYear,
			us.TimeZoneStandardMonth,
			us.TimeZoneStandardDay,
			us.TimeZoneStandardHour,
			us.TimeZoneStandardMinute,
			us.TimeZoneStandardSecond,
			0,
			us.TimeZoneStandardDayOfWeek) as ConfigFreezeDate

Open in new window

Error occurred when parsing the SQL script: Unsupported statement type: if

if object_id('tempdb..#QuarterYearTable') is not null drop table #QuarterYearTable
select * into #QuarterYearTable from dbo.FEI_Split(@QY_,',')

Open in new window

ASKER CERTIFIED SOLUTION

Chinmay Patel

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

apollo7

ASKER

Thanks Chinmay, I am personally against trying to use FetchXML for these reports. I appreciate your help.

I have tried a number of ways to convert the SQL stored procedure code into Fetchxml but I am getting errors

Dynamics 365 9.1 online, CRM 2016 On prem, SSRS reports to Fetchxml

Thanks Chinmay, this is excellent. I have put some other SQL code from the same report. Can you tell me if the unsupported SQL can be modified and converted to Fetch? Error occurred when parsing the SQL script: Unsupported statement type: SiteReady

Error occurred when parsing the SQL script: Unsupported statement type: if

Thanks Chinmay, this is excellent. I have put some other SQL code from the same report. Can you tell me if the unsupported SQL can be modified and converted to Fetch?

Error occurred when parsing the SQL script: Unsupported statement type: SiteReady