SQL SERVER – Automatically Store Results of Query to File with sqlcmd

I receive lots of email everyday and I do answer almost every email I receive. However, I prefer to answer all the technical questions on my facebook page. With the help of social media it is very easy to reach out to multiple people with the same problem. Here is the recent question which I received on my facebook page.

“How I automatically execute my T-SQL script and save output of the query (resultset) in a different file automatically? I do not want to use SSMS”

Well, very interesting and simple question. It is very easy to direct the output of the query to a new file with the help of sqlcmd.

Let us work on this together.

Step 1: Create SQL file which you want to execute. I have created SQL file testquery.sql and saved it in my drive.

Step 2: Execute following sqlcmd command on prompt:

sqlcmd -i SQLFile.sql -S ServerLocation -E -o OutFileName.txt

If you are using windows authentication execute following script

sqlcmd -i testquery.sql -S localhost -E -o TestResults.txt

If you are using username and password run following script

sqlcmd -i testquery.sql -S localhost -U pinaldave -P password -o TestResults.txt

Step 3: Open the file generated by sqlcmd in the drive

This is indeed a very simple process. If you have any question, you can go to my Facebook Page and ask question.

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

6 thoughts on “SQL SERVER – Automatically Store Results of Query to File with sqlcmd

  1. You can rename the file in a seperate step to get a unique name by adding a date in a roundabout way using a windows variable, eg:
    REM SQLCMD set filename variable name
    REM -U user, P password, Q quit after query, nocount on, -o output file -h-1 remove column headers, -W remove trailing spaces
    sqlcmd -S server -U user -P pass -Q”SET NOCOUNT ON; SELECT REPLACE( CONVERT(VARCHAR,MAX(StartDate),103) + ‘_’ + CONVERT(VARCHAR,MAX(EndDate),103),’/’,” ) FROM database.dbo.DateRecord” -oDateVar.txt -h-1 -W

    REM create environment variable PGvar
    set /p DateVar= < DateVar.txt

    REM rename files based on variable
    rename OriginalOutput.txt OriginalOutput_%DateVar%.txt

  2. I have executed many query but in my output file I only get one line, whereas when I run the sql scripts manually from query analyser it shows (1 rows affected) multiple times down the results window, how can i get these multiple rows of output to show in my output text file using SQLCMD..?

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