Debugging stored procedures, without using SSMS 2008 Debugger, or the Visual Studio debugger (output variable values?)

Question

I have a SQL Server 2005 database with some Stored Procedures (SP) that I would like to debug...essentially I would just like to check variable values at certain points throughout the SP execution.

I have SSMS 2008, but when I try to use the debugger, I get an error that it can't debug SQL Server 2005 databases. And I can't use the Visual Studio debugger (by stepping into the SP via Server Explorer) because Remote Debugging is blocked by our firewall, and I'm rightfully not allowed to touch the firewall.

So my question is how can I check variable values at certain points in the SP execution? Is there some way to output those values somewhere, perhaps along with some text?

Answer 1

You can write to a text file using the xp_cmdshell stored procedure.

DECLARE @cmd sysname, @var sysname
SET @var = 'Hello world'
SET @cmd = 'echo ' + @var + ' > var_out.txt'
EXEC master..xp_cmdshell @cmd

http://msdn.microsoft.com/en-us/library/ms175046%28SQL.90%29.aspx

Note that this procedure may be disabled. The procedure allows execution of executable files so it should be disabled when not needed. See the following on how to enable the procedure.

http://msdn.microsoft.com/en-us/library/ms190693%28v=SQL.90%29.aspx