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?
Background
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.
The Solution
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:
;WITH FilteredAccount AS
And that solved the problem and the reports ran successfully.
