SQL Server 2008R2 Causes Severe Error in Merge Output

I’m developing a solution that uses MERGE operation including OUTPUT statement. The development was done in SQL Server Data Tools project with target platform SQL Server 2008. All was well while I’ve been testing it on SQL Server 2014. But when I tried to deploy it on SQL Server 2008 R2 without any service pack, it failed. I got the following errors:

Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.

I’ve tried to apply service packs (from SP1 to SP3), but it didn’t help to solve the problem.

Now I took the stored procedure that causes the SQL Server to dump and started to cut pieces of code to find the exact place. That was the MERGE command, and particularly the OUTPUT statement. It uses @action, inserted.columns and deleted.columns. I’ve checked documentation for SQL Server 2008R2, the statement was correct.

At the last I’ve noticed a post in stackoverflow.com about calculated column in CTE target. Wow! That’s my case – I’m really using CTE target where I filter only first rows with window function. Here is an abridged piece of code:

The problem was that I use SELECT * for simplicity. Therefore calculated field row_num was also included in the target CTE in MERGE command. When I replaced the * to a list of explicitly quailfied fields, the error disappeared.

So, if you use MERGE command with OUTPUT statement in SQL Server 2008 R2 and your target is a common-table expression (CTE), don’t include calculated fields in target CTE.

Leave a Reply

Your email address will not be published. Required fields are marked *