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.

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

And then you still have to save the query itself.

Option Two

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

Then run your query.

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?

Filed under: Microsoft SQL Server, SQLServerPedia Syndication, SSMS Tagged: microsoft sql server, sqlcmd, SSMS