CODEWITHSUNDEEP
sqldebuggingsql-server-2008file
JustBeingHelpful
JustBeingHelpful

Reputation: 18980

logging detailed information to file in SQL Server

Does anyone have some code to simply log some detailed information to a file within a SQL query (or stored procedure or trigger)? I'm not looking for anything fancy. All I want to do is to quickly put debug information into my SQL, much like folks do for JavaScript debugging using alerts. I looked at using Lumigent, but that seems like overkill for what I want to do. I don't care what the format of the logging is in. Below is a simple example of what I'd like to do.

Example:

DECLARE @x int;
SET @x = '123'
-- log the value of @x

============

9/6/2011 @ 4:01pm update

I tried the sqlcmd below, which works well. But it doesn't work well when there are 100 parameters on a stored procedure when I want to debug. In that case, I need to go put a break-point in my client code, then get the value of each argument. Then go and type out the exec command, and then look at the output file. All I want to do is put one simple line of code into my SQL (perhaps calling another stored procedure if it takes more than one line of code), that writes a variable value to a file. That's it. I'm just using this for debugging purposes.

Upvotes: 1

Views: 1984

Answers (2)

Aaron Bertrand
Aaron Bertrand

Reputation: 280429

Now with more background I think I can promote my comment to an answer:

Why does it have to be a file? If this is just during debugging, can't you just as easily log to a table, and when you want to see the recent results:

SELECT TOP (n) module, parameters, etc. 
    FROM logTable 
    ORDER BY DateCreated DESC;

You can simplify the logging or at least make it easier to replicate from procedure to procedure by having a stored procedure that takes various arguments such as @@PROCID and others to centralize the logging. See this article I wrote for some ideas there - it's geared to just logging once per stored procedure call but you could certainly call it as often as you like inside any stored procedure.

This seems like much less hassle than using an archaic file-based log approach. You're already using a database, take advantage!

If you're committed to using a file for whatever reason (it might help to understand or counter if you enumerate those reasons), then the next best choice would likely be CLR, as already mentioned. A complete solution in this case might be beyond the scope of this single question, but there are tons of examples online.

Upvotes: 0

JNK
JNK

Reputation: 65187

One pretty easy method is to use either OSQL or SQLCMD to run your procedure. These are command-line methods for executing SQL commands/procedures/scripts.

With those utilities you can pipe the output (what would normally appear in the "Messages" tab in SSMS) to a text file.

If you do this, in your example the code would be:

DECLARE @x int;
SET @x = '123'
PRINT @x

If you are running the same procedure multiple times, you can just save it a a one-line batch file to make it very easy to test.

Upvotes: 2

Related Questions