|
Save Query Output in SQL Server
There are requirements in various forums that you would like to save the output
of the query. There are a number of solutions given. In this code
snippet I will give you a method to use this from a script point of view. For
the given task I create a generic proc that will facilitate in extracting
this data into a file. The procedure looks like:
Create Procedure
ee_DumpOutput (
@StrRights
nvarchar(4000),
@FileName
varchar(50)
)
AS
Begin
DECLARE @FullFileName Varchar(500)
SET @FullFileName = 'c:\' +
@FileName
SET @StrRights = 'osql -Slocalhost -E -q"SET
NOCOUNT
ON;' + @StrRights +'" -s"," -n -w8000 -o"' +
@FullFileName +'"'
Exec master..xp_cmdShell @StrRights,
NO_OUTPUT
END
GO
The
procedure has two parameter, one to accept the statement to execute and the
second requires the filename that you would like to create. A typical call to
the code looks like this.
Exec ee_dumpOutput 'select
EmployeeID,LastName,FirstName,Title,TitleOfCourtesy,
BirthDate,HireDate,City,Region,PostalCode,Country from
northwind..employees' ,'test.txt'
The
basic assumption here is that the code is executed in Integrated authentication
mode. We add a comma as the delimiter. Hence, these can be taken as a .csv
file. Some of the limitations include the fact that we cannot take large
text as they would have escape characters that would wrap the text. Even in
fields like Address, we can assume that we will have comma's inside the text.
Hence they will get as separate columns. These are some of the limitations.
Nevertheless, for simple requirements this script is surely of help.
Having
said that there are also other or rather simpler methods like using "Results
to File" (Ctrl + Shift + F) from the Query Analyzer
window. This will help you save the output of a batch executed.
In
the above example I've used the simple example with osql, the same
implementation can be extended with isql and bcp command line utilities that
give you the same functionality.
|