In my previous article, I walked
you through a general logging process to write out simple logging information to a table
so that it could read/reported on, etc. You can see it be clicking here. But I actually got a few responses from people who want to
be able to log directly to .txt files.
I thought about for a while... My initial thought was to
write all of the log stuff out to a temporary table then bcp it out... but I figured that
could be overkill and potentially cumbersome to implement, especially if the .txt file you
want write to already exists. So I thought about it a little more came up with this simple
variation on my table-based logging process that will allow
you to write logging information to general .txt files. This could be used any place T-SQL
is welcome (stored procedures, query windows, triggers, and SQL Query Windows within DTS).
Here I'll outline: The process, Why it works,
Caveats
The Process |
| Step 1 |
For
the information you wish to write out to a .txt file, assign it to a variable... so if the
rowset is the result of a query just use the select @myvar
= query_goes_here syntax of select. |
| Step 2 |
Using
another variable, call it @cmdtxt
(varchar (255)), build a command-line string with the following format:
select @cmdtxt = "echo " + @myvar +
" >> drive:\path\filename.txt" |
| Step 3 |
Execute
this string using the extended stored procedure xp_cmdshell:
exec master..xp_cmdshell @cmdtxt |
Here is an example
This will take the numeric count from
authors, convert it to varchar (string) and effectively write that number to an NT operating system text file.
declare
@count_rows as int,
@cmdtxt as varchar(255)
select @count_rows = count(*) from pubs..authors
select @cmdtxt = "echo " + convert(varchar(5),@count_rows) + " >>
c:\temp\myfile.txt"
exec master..xp_cmdshell @cmdtxt
Why it works
The NT/DOS echo command will
just print whatever text follows it. The '>>' part tells the operating system to
copy the output of that line to the file specified by drive:\path\filename.txt.
Therefore, whatever string you build and store and build within @cmdtxt, it will be
written to the file that you specified. If the file does not exist, this command will
create it. If the file exists, it will write a line (or append) to it. If you wish to
'reset' the file each time, use '>' instead of '>>'. Using '>' will overwrite
the file if it exists. So, in the above example, only the number of rows in the
pubs..authors table will be written to the c:\temp\myfile.txt file.
Caveats
There are a couple of things you do need to be aware of when you use this: Make sure you or the id that will be executing this
stored procedure/query have the proper authority to execute xp_cmdshell. This involves
some security stuff which is outlined pretty clear in SQL Books Online (BOL).
Make sure that the Service Account (Usually your SQL
Agent) that you have setup to interact with the Operating System has the NT Security
clearance to write out to the directory\file what you are trying to write to.
I think there is a limitation on how long the
resulting @cmdtxt line can be. Being that I have background in DOS, my rule of thumb is
that @cmdtxt shouldn't be any longer than 255 characters. I just tested out that theory
and NT did allow me to put way more than 255 characters into a file, but I am sure there
is a max.. At an absolute max, SQL Server will only allow a maximum value of 8000 for the
size of a varchar datatype.
If you're going to use this in a trigger or stored
procedure, you may wish to invoke the no_output option so that no response (from the
xp_cmdshell command) output comes back to the calling trigger or stored procedure.
If you're really up to it, and it's a concern, look up info
on the 'exec' command on BOL so that when you do execute xp_cmdshell, you can capture a
return value to a variable to make sure the write was successful.... but perhaps that
could be another article within itself...
This is a simple solution that I am sure could probably be
improved on. I thank Gaurav Manhindra who first asked me if this was possible to do in a
simple manner. Hopefully you’ll find it useful too. If you haven't seen it already,
click here to see a SQL Table-based solution that I use to
log information from my processes.
If you have any questions about this process, please feel free to email me.
Mike Metcalf
» 
Comment and Contribute
