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
Step 1For 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.
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"
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.
@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.
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.