Sending Query Results to Others

March 28, 2012

Sending Query Results to Others

Filed under: Miscelleaneous SQL,SQLServerPedia Syndication,Technical Articles — Michael J. Swart @ 12:00 pm

I think most of you have all been here before. You want to know the results of a query, but you have to get it from a colleague through email or some other proxy:

So how do you usually do it? How do you ask someone to send you the results of a query? There’s a million ways to answer that question and I’ve discovered that none of them are perfect. Depending on the situation, different ways are better than others. So let’s pick a situation and explore the best way to ask for those query results.

The Situation

The facts were these…

Goal: I want the results of the following query. It retrieves the top 20 cached queries and plans based on total consumed cpu.
I have to rely on my friend to run the query for me.
My friend is not a database administrator but is comfortable around SQL Server Management Studio
I want to minimize or simplify instructions.
I don’t want any truncated results.
I want to assure my friend that these queries are safe.
For this situation, I’m not worried how my friend gets the data to me (email, ftp, etc…). I only care that my friend is able to persist the data to some file.

And to complete the situation, here’s that query I’m curious about…

-- TOP 20 queries (by CPU)
SELECT TOP (20)
    [TotalCPU] = total_worker_time,
    [TotalElapsedTime] = total_elapsed_time,
    [ExecutionCount] = execution_count,
    [AverageCPUInMicroseconds] = cast(total_worker_time / (execution_count + 0.0) as money),
    [DBName] = DB_NAME(ST.dbid),
    [ObjectName] = OBJECT_NAME(ST.objectid, ST.dbid),
    [QueryText] = (SELECT [processing-instruction(q)] = CASE
            WHEN [sql_handle] IS NULL THEN ' '
            ELSE (SUBSTRING(ST.TEXT,(QS.statement_start_offset + 2) / 2,
                (CASE
                        WHEN QS.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX),ST.text)) * 2
                        ELSE QS.statement_end_offset
                        END - QS.statement_start_offset) / 2))
            END
			FOR XML PATH(''), type),
    [QueryPlan] = qp.query_plan
FROM sys.dm_exec_query_stats QS
CROSS APPLY sys.dm_exec_sql_text([sql_handle]) ST
CROSS APPLY sys.dm_exec_query_plan ([plan_handle]) QP
ORDER BY total_worker_time DESC

Notice that the results of this query contains xml, and that xml might contain commas, spaces, tabs, newlines and all the typical delimiters. That makes my job trickier. So now I’m going to list a number of methods and their pros and cons. I can think of a number of methods off the top of my head.

Method 1: Cut and paste into Excel
Method 2: Store results as tables in a temporary DB and back it up
Method 3: Using the export data wizard
Method 4: Use Management Studio’s results-to-file
Method 5: Use BCP along with SQLCMD mode
Method 6: Output the whole thing using FOR XML PATH (Spoiler alert, I like this one best!)

Method 1: Cut and Paste into Excel

This is most people’s usual method and it works in 90% of all cases because Excel is so versatile. However in this particular case, the whitespace in the query plans and the sql text mess up some of the rows and formatting, one field in each row. I get something like this, ugh:

But there’s a better way using Excel. It’s not too hard to use the Data Connection Wizard. So it’s not too much work to guide my friend through it either. If I can, then this is a really useful nice way to pass around data. That wizard is accessed like this:

Method 2: Store Results As Tables in a Temporary DB and Back It Up

That can be done with a single T-SQL script which my friend should be able to run no problem. The script looks something like this:

create database myresults
go
-- TOP 20 queries (by CPU)
SELECT TOP (20)
    [TotalCPU] = total_worker_time,
    [TotalElapsedTime] = total_elapsed_time,
    [ExecutionCount] = execution_count,
    [AverageCPUInMicroseconds] = cast(total_worker_time / (execution_count + 0.0) as money),
    [DBName] = DB_NAME(ST.dbid),
    [ObjectName] = OBJECT_NAME(ST.objectid, ST.dbid),
    [QueryText] = (SELECT [processing-instruction(q)] = CASE
            WHEN [sql_handle] IS NULL THEN ' '
            ELSE (SUBSTRING(ST.TEXT,(QS.statement_start_offset + 2) / 2,
                (CASE
                        WHEN QS.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX),ST.text)) * 2
                        ELSE QS.statement_end_offset
                        END - QS.statement_start_offset) / 2))
            END
			FOR XML PATH(''), type),
    [QueryPlan] = qp.query_plan
INTO myresults.dbo.myresultset
FROM sys.dm_exec_query_stats QS
CROSS APPLY sys.dm_exec_sql_text([sql_handle]) ST
CROSS APPLY sys.dm_exec_query_plan ([plan_handle]) QP
ORDER BY total_worker_time DESC
go
 
backup database myresults to disk = 'C:\Users\Michael Swart\Desktop\Output.bak'
go
 
drop database myresults
go

The best part of this is that the data is backed up perfectly, the data captures all the data with no truncation. Xml fields are stored as xml fields and once restored, it’s easy to access the resultset exactly as it was on the target computer.

The bad news is that although it should be fairly safe, it’s not as easy to assure my friend that the query above changes nothing. It does! It creates a whole database and backs it up. It then drops that database.

Hang on a second, if my friend can use this method, that means my friend (the non-dba) is authorized to drop databases. Yikes! What’s up with that?

Method 3: Using the Export Data Wizard

It seems like SQL Server’s “Export Data” wizard was just built for this job. That can be accessed here:

But I don’t recommend it, not one bit. It launches a wizard which takes me through a set of choices, and no matter what I choose, it always seems to choke on the xml column.

It’s clear that this Export Data Wizard is using SQL Server Integration Services (SSIS) under the covers. How do I know it’s SSIS under the covers? Well if the clumsy data type handling weren’t a giveaway (zing!), the wizard can save the job I specified as an SSIS package.

The SSIS shows through too much to recommend to my friend, who has no dreams of becoming a B.I. Maestro.

So it seems like this method is only appropriate for those situations when cutting and pasting to Excel would be just as appropriate. And it’s kind of hard to write instructions simpler than “Cut and paste into Excel”.

Method 4: Use Management Studio’s Results-To-File

Most people use the query window’s default setting, Results-To-Grid. Sometimes there’s a use for Results-To-Text (like when using sp_helptext for example). But what about Results-To-File? I can set that using the Query window’s context menu here:

I can also enable results to file with Ctrl+Shift+F. I had to look that up though. There’s a reason I haven’t learned that shortcut by heart. It’s because results-to-file doesn’t work very well. As far as I can tell, it’s like specifying results-to-text and then pasting that into a text file.

The biggest problem here is that depending on the results, the data gets truncated easily and the newlines and tabs in the data get mixed up with the rest of the formatting. By the way, I’m also out of luck (and for the same reason) if I use SSMS to save grid results.

Method 5: Use BCP, along with SQLCMD mode

If I can instruct my friend to use a query window in SQLCMD mode, then I can write a script which will get a bcp script to export data. Enable SQLCMD mode by choosing it from the Query menu in SSMS:

And the script I would have my friend run looks like this:

!!bcp "Select 'query omitted' as [BigLongQuery]" queryout "c:\temp\desktop\output5.dat" -SSWARTLAPTOP -T -n

which I could then get back on my side using bcp or using OPENROWSET.

This method is okay. It’s kind of awkward (and any instructions to my friend will be equally as awkward) and it always takes some work to get the syntax just right. But BCP is kind of like Rob Lowe, he hasn’t changed in years and he’s really not that bad.

Method 6: Output the Whole Thing Using FOR XML PATH

Hey! Now I’m getting somewhere! XML was built for this kind of thing. All I do is take the query, and append FOR XML PATH to it and send the query to my friend saying “Run this and paste the results into notepad.” Here’s the whole query:

SELECT TOP (20)
    [TotalCPU] = total_worker_time,
    [TotalElapsedTime] = total_elapsed_time,
    [ExecutionCount] = execution_count,
    [AverageCPUInMicroseconds] = cast(total_worker_time / (execution_count + 0.0) as money),
    [DBName] = DB_NAME(ST.dbid),
    [ObjectName] = OBJECT_NAME(ST.objectid, ST.dbid),
    [QueryText] = (SELECT [processing-instruction(q)] = CASE
            WHEN [sql_handle] IS NULL THEN ' '
            ELSE (SUBSTRING(ST.TEXT,(QS.statement_start_offset + 2) / 2,
                (CASE
                        WHEN QS.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX),ST.text)) * 2
                        ELSE QS.statement_end_offset
                        END - QS.statement_start_offset) / 2))
            END
			FOR XML PATH(''), type),
    [QueryPlan] = qp.query_plan
FROM sys.dm_exec_query_stats QS
CROSS APPLY sys.dm_exec_sql_text([sql_handle]) ST
CROSS APPLY sys.dm_exec_query_plan ([plan_handle]) QP
ORDER BY total_worker_time DESC
FOR XML PATH

And the results that are sent back are immediately accessible without any processing.

I think this method works best for this particular situation. It hits my goals perfectly. Now if you dear reader want to use this method in your situation, there’s just some extra caveats:

SSMS restricts XML data results to 2 megabytes, although that’s configurable.
If you query binary fields (say query plan handles for example), then you might be surprised if the results come back in base64 encoding rather than hex which is a bit of a pain.

Other methods

Powershell I know you powershell pros are eager to give a one line script that does everything here. So lets hear it the comments! I know that powershell treats sets of objects (rather than unix’s cshell which deals with text). So I’m curious how well it serializes objects to a file.

SQL Server Integration Services I haven’t forgotten about SSIS either, it’s the tool that was built for moving data from here to there in some format (any here, any there, any format). But I just couldn’t get the instructions simple enough for this scenario.

-- Comments (21)

21 Comments »

I use PowerPivot for Excel, so it’s a mutation of Excel method.

Comment by Szymon — March 28, 2012 @ 3:36 pm
Very nice Szymon,

But it’s my friend who needs to use PowerPivot, and she may or may not be familiar with PowerPivot. So this solution might be less ideal because of this goal: “I want to minimize or simplify instructions.”

Comment by Michael J. Swart — March 28, 2012 @ 3:52 pm
Michael,
When you said “serializes objects to a file” were you expecting the results of the query to be attached to the email, or appear in the body of the email? Here’s an example a method I’ve used with PowerShell to make the results appear in the body of the email as a nicely formatted HTML table. http://sqlvariant.com/wordpress/index.php/2011/02/emailing-tempdb-query-results-to-paul-randal-with-powershell/

Comment by Aaron Nelson — March 29, 2012 @ 8:56 am
Hi Aaron,

Attaching a file to email or in the body of the email itself it doesn’t really matter. Your link answers that question nicely. I never knew about Convert-ToHTML. That cmdlet seems really cool.

The trick isn’t really sending email automatically. A big part of the challenge is making it as easy as possible for my friend to follow instructions for this task. And so the interesting thing is that although I’m the one creating the script (which can be as fancy as we need it to be), I’m not the one running it. So I can see a couple things:
- Running Powershell isn’t super-easy for the novice. Do we recommend sqlps? Or do we suggest running Powershell ISE with instructions to load the SQL snapins?
- Your example mentions using invoke-sqlcmd2, a 3rd party cmdlet which has to be installed and that complicates the instructions further, but I don’t think we need it for this example since we’re only returning a single resultset. So invoke-sqlcmd would be fine, right?
But those aren’t terribly complicated problems to overcome, so Powershell turns out to be a nice method. I suspected it would 🙂

Comment by Michael J. Swart — March 29, 2012 @ 9:33 am
Great (and thorough) post Michael.
For anyone who wants to export to excel – I’ve got a video that shows how to output to file and then ‘import’ into Excel:
http://www.sqlservervideos.com/video/adhoc-reporting/

–Mike

Comment by Michael K. Campbell — March 29, 2012 @ 3:02 pm
Hi Mike, Thanks for visiting!

Your video explores my “Method 4” above. It’s good to know that stuff, but it may not be ideal in this particular situation.

You bump up the characters per column to 1024 from the default of 256. 8K is the maximum for that setting and even that’s not enough for many xml query plans. Also the new lines in the sql text still create a problem when importing into Excel.

Comment by Michael J. Swart — March 29, 2012 @ 3:36 pm
Why not create a stored procedure you can have them execute with enough parameters to meet 1 or several query needs. You control all of the code, they never know nor care what’s going on under the covers. All they need to know is when Michael calls up and says I need you to send me queryData, or perfData I run exec usp_send_michael_stuff (‘queryData’ or ‘perfdata’ or ???). Dump the results as you are now or in an email automatically and leave a nice message so they know it executed properly.

Comment by Chris — March 29, 2012 @ 7:06 pm
You know what Chris, that approach shows a lot of promise. Especially if the engagement is less transient or more permanent. I’ll give it some thought.

Comment by Michael J. Swart — March 29, 2012 @ 7:12 pm
When I try method 6 I get the following which seems less than useful. Am I doing something wrong?

XML_F52E2B61-18A1-11d1-B105-00805F49916B
140364757721966251198180681739771.4145DBAUsp_Errorlog_Notification1423223359142351573811587561228.2339XATESSXAESPASWorkStationEvnConfigValuesSelPr<StmtSimple StatementText="CREATE PROCEDURE SXAESPASWorkStationEvnConfigValuesSelPr /*'******************************************************************************* 'Copyright (c) 2009 Eclipsys Technologies Corporation. All Rights Reserved. ' ' ' ' PROPRIETARY NO

Comment by Chris — April 2, 2012 @ 12:59 pm
Hi Chris,
Hmm… Here’s my guess. You’re running the query while results-to-text was on. Try it again with results-to-grid (in SSMS, this is Ctrl+D)
If I’m right, you should see a xml link you can click on. It’s a little more useful. Good luck and let me know if that’s right.

Comment by Michael J. Swart — April 2, 2012 @ 1:06 pm
I am using results-to-grid When I click on the box then SSMS opens a new window with a great long XML document. But I can’t see how this is useful. I can’t read the XML document whereas I can easily see the data in Excel. I cannot cut and paste this XML code into NOTEPAD. I just am not sure what to do with this XML

Comment by Chris — April 2, 2012 @ 1:27 pm
I see, the point here is to demonstrate how to get data from the person running the query to myself or some other database professional.
I was able to paste this large xml document into notepad and save it. I’m not sure where you had trouble doing that. My instructions to my friend would then simply be to save the xml document and send it along. The end (as far as my friend is concerned).

As for what I would do with it, that’s a bit off topic for this post, but I used an example that demonstrates a kind of inspection of the queries seen on a server that are the highest consumers of cpu: When I inspect the xml document, I see something like this:

I can immediately see the top query here took 105 milliseconds of cpu time and was executed once since last server reboot. The query is accessible if I wanted to inspect it and so is the plan.

If I wanted to look at it in Excel, I could import that data but it would take some work. The whole point is that I’m willing to do that work if it makes my friend’s life easier (see goals above).

Comment by Michael J. Swart — April 2, 2012 @ 1:48 pm
Great post!

Comment by Ignacio Salom — April 3, 2012 @ 2:49 am
In the nonSQL world we’re considering putting in a “create diagnostic bundle”.

So I guess everything would be wrapped up in a neat little package! Really, I mean that. Sorry if it sounded sarcastic.

Comment by Dave S — April 3, 2012 @ 9:41 am
In the nonSQL world we’re considering putting in a “create diagnostic bundle” button.

So I guess everything would be wrapped up in a neat little package! Really, I mean that. Sorry if it sounded sarcastic.

Comment by Dave S — April 3, 2012 @ 9:41 am
Very good Homer… er… I mean Dave.

Comment by Michael J. Swart — April 3, 2012 @ 9:59 am
[…] Sending Query Results to Others – I can’t believe that none of us have actually talked about this before! It’s one of those things we all have to do and have our own favourites. A brilliant comparison of techniques from Michael J. Swart (Blog|Twitter) this week. […]

Pingback by Something for the Weekend – SQL Server Links 06/04/12 - John Sansom SQL Server DBA in the UK — April 5, 2012 @ 4:51 pm
Great post, Michael. The query you used was also very helpful to me! Thanks.

Comment by Don Kolenda — April 25, 2012 @ 11:02 pm
Thanks Don! Glad you liked it.
Now if only I could figure out an easy way to have others collect sp_whoisactive results.

Comment by Michael J. Swart — April 26, 2012 @ 12:05 pm
If a third-party tool is an option, SSMSBoost has an awesome function that lets you script grids/selections to Excel as XML and then save as xlsx.

Comment by Eric — August 11, 2016 @ 10:00 pm
Thanks Eric… In the exact scenario I was mentioning above, I want to minimize instructions and I’d have to convince my friend that installing 3rd party software is safe. SSMS is a viable option, but I’d love to see “Copy for Excel” as an out-of-the-box SSMS feature.

Comment by Michael J. Swart — August 23, 2016 @ 9:03 am