One of my local customers recently moved from Small Business Server 2003 to Small Business Server 2008. Among other things, this required a move from SQL Server 2005 to SQL Server 2008 as well.
The movement of the databases went pretty much without issue, but we ran into some strange behavior with some of the custom CRM reports they had created. The error looked something like this:
Not very helpful at all.
After reviewing the report in Visual Studio and still not finding any issues, I finally started looking at the system itself. Thatâ€™s when I found this in the SQL Serverâ€™s Event Log:
Report data set execution failure. Error: 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 ‘,’.
Hmm. It keeps getting better and better. Now what exactly does that mean?
It turns out that when the SQL commands they had written to populate the report were sent to the SQL server, they were combined with other SQL commands.
Unfortunately, SQL didnâ€™t like the way the commands were combined. It wanted you to use a semi-colon ( ; ) to separate the commands so it could determine how the commands should be run.
Which is exactly what the error message states.
To fix the issue, all I did was to add a semi-colon ( ; ) to the beginning of the custom SQL statement, which looked something like this:
And that solved the problem and the reports ran successfully.