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.

Speaking at SQLSaturday Göteborg #433

sqlsat433_web

Another great news comes today. I was selected as a speaker in Gothenburg event (to be frankly, I prefer the native name of the city Göteborg). My session is about a SQL Server transaction log internals and how to troubleshoot issues you might face when you’re not paying enough attention to transaction log.

It seems it would be a very good time for me – a week in Europe from SQLSat Oslo to SQLSat Göteborg. I hope it will be cool!

Here is the full schedule of the conference http://www.sqlsaturday.com/433/sessions/schedule.aspx

If you want to get a free training, deep your knowledge or ask a difficult question, you’re welcome. And don’t forget to invite your colleagues/co-workers/friends/spouse/children 🙂 who are interesting in SQL Server to join us in first-ever SQLSaturday in Sweden!

Speaking at SQLSaturday Oslo #414

sqlsatOslo2015

I’m very proud to be a part of this great event in Norway. That’s my lucky chance to visit Oslo, meet the colleagues and talk about SQL Server. I’ll present a session about transaction log and how to troubleshoot the issues that can cause database to stop or degrade its productivity.

Here you can find more information about SQLSat Oslo 2015

SSDT Unit Test Connection For Validation. Really?

When you create database unit test project, you need to setup connections to your database. I always use only one connection to run the code and test conditions. But I was curious what is the second connection for? Where does that validation occur?

unit test connections

Now I’ve spent some time to check when this validation connection is really used. I’ve created a simple unit test and placed a simple command to check user name in every part of unit test:

Here are the results of my checks.

Part of the unit test Connection used
Test initialize Connection 2
Pre-test Connection 2
Test execution Connection 1
Post-test Connection 2
Test cleanup Connection 2

As you see, only test execution phase uses connection 1 (“execution”), whereas all the other phases use connection 2 (“validation”) if you use 2 connections. If you use only one connection, so all the steps will be run under this unique username.

It’s a bit strange that SSDT uses a term “validation” for a connection for pre- and post-test steps. But that’s out of our will 🙂