SSMS Results: A Cautionary Tale

As a person who messes with SQL Server there is a pretty strong probability that you are using SQL Server Management Studio (SSMS). It is a very full featured tool, but does have a couple of problems and quirks (like crashing on large result sets due to it reaching the maximum memory allocation for a 32bit process).

The other day I came across another quirk, maybe even a bug, which can happen when returning and working with XML results, which could lead you to copy incorrect data…

Let’s take a look at the problem using a very simple example.

Here we are going to return a single row of data, with three columns, one of which is XML.

SELECT
Col1 = 'These are not the results you are looking for' ,
Col2 = CAST('This is what you really expected.' AS XML) ,
Col3 = 'You were not looking for this either.'

SMSQuirk-MainResults

From here you can click on the XML text to view the data (while not a big deal for this, when reviewing large sets of data it can be really important, or for copying and pasting for something else)

SMSQuirk-ExpectedXML

 

Now, using one of a little known feature of SSMS we can click and drag on the header of  the Col2 column and drag it over to be the first column. It still shows the correct result data.

SMSQuirk-MainResultsReordered

The problem comes if we click on the XML again to show it

SMSQuirk-IncorrectResults

 

As you can see we are not returning the correct information any longer. We now show the results from Col1, where we expect Col2. If you wanted to take this a step further try reordering the columns again. You’ll find whichever position the XML column is in, it will return the original contents of that column rather than the actual visible content.

I’ve been able to reproduce this with SSMS 2012, 2014, and the 2016 CTPs.

While this isn’t a huge deal, it could cause you problems if you return multiple XML columns with different results, and like to reorder things in SSMS. So just be careful when clicking on things out there, the results might not be what you expect.

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s