:OUT :OUT darn results!

2

December 19, 2016 by Kenneth Fisher

Years ago I worked for a company that had a rather cool way of handling requests. When we ran the request the query we used and the output were saved to a directory on the network. This way if there were any questions about what had been done we had at least a bare minimum of the information needed to review it. However having to hit save on both the query window and the output window was kind of a pain. This is one of those cases of really wishing I knew then what I know now.

Option One

First, go into options and set your queries to SQLCMD mode.

autooutputresults2

The down side to turning this on by default is that SQLCMD mode turns off intellisence. It also turns off the T-SQL debugger if you are one of the rare people that uses it.

Note: I’m showing how to set this by default. It can also be turned on at a query level pretty easily. For the purpose, I’m describing though you’d be using it more often than not so it seems better to turn it on by default and just turn it off as needed.

Now we use the SQLCMD command :OUT. You just pass the path you want the output to go to. I do want to point out that the path is local to the instance. So C:\test\test.txt goes to the C drive of the instance. Because of this, I recommend using the full URL of a shared location. Even so, since I’m logged into my test machine I’m just using the local C drive. Also :OUT overwrites the file each time it’s run.

:OUT c:\requests\REQ0001.txt
-- this is a test
select database_id, name from sys.databases

autooutputresults3

And then you still have to save the query itself.


Option Two

First, set SSMS to include the queries in the text result set.

autooutputresults

Then run your query.

autooutputresults4

And lastly, save the output.


One way or the other you have to save at least one file. The query or the output. You would think that you could combine both methods. Include the query in the output and save the output automatically. But unfortunately, it doesn’t work. Either way, you do it you end up having to save one file (the query or the output). But that’s still better than nothing, right?

2 thoughts on “:OUT :OUT darn results!

  1. dewitte says:

    Thanks! I can definitely see some uses for both options.

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 1,646 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: