aug 14 Ingen kategorier

Microsoft CRM SSRS report errors using CRMAF_ AND WITH (NOLOCK)

Hi,

I had an old report issue today and coulden’t remember the solutino so it took some time to find the right fix for it even that I have had this issue in the past….

The problem was that I had build quite a big SQL statement and it was all working in SQL Studio manager and in Visual Studio but when I deployed it to the CRM deployment and ran the report I’ve got an error that it could not execute.

In the eventlog of the report server I’ve received 3 errors:

ERROR 1:

Report Server (MSSQLSERVER) cannot load the TERADATA extension.

ERROR 2:

Report Server (MSSQLSERVER) cannot load the SQLPDW extension.

ERROR 3:

Report data set execution failure. Error: Incorrect syntax near the keyword ‘WITH’.

Incorrect syntax near the keyword ‘with’. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

Incorrect syntax near the keyword ‘AS’.

Incorrect syntax near the keyword ‘AS’.

Incorrect syntax near the keyword ‘AS’.

Incorrect syntax near the keyword ‘AS’.

Incorrect syntax near the keyword ‘AS’.

Incorrect syntax near the keyword ‘with’. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

Incorrect syntax near the keyword ‘with’. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

Incorrect syntax near the keyword ‘with’. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

Incorrect syntax near the keyword ‘with’. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

The first 2 errors was just misleading and did not have anything to do with the report and to solve them it’s just adding the extension to the report server – just google it there is plenty regarding that.

The last error came because I in my SQL statement was using CRMAF_>>TABLENAME<< WITH (NOLOCK) and CRM does not support that!

So remember to exclude the NOLOCK statement on those tables.

To make sure using a NOLOCK feature you have to use READ_COMMITED instead which I have made a description of here: http://www.anjep.com/2012/01/want-to-optimize-ssrs-report-execution/

Have fun :-)

Share on FacebookTweet about this on TwitterShare on LinkedInShare on Google+

Skriv et svar

Din e-mailadresse vil ikke blive offentliggjort. Krævede felter er markeret med *