|
How do I write diagnostic/trace messages to a text file during the execution of a stored procedure on Microsoft SQL Server 2008, similar to SQL Anywhere's MESSAGE ... TO CONSOLE statement? I need this facility to help diagnose and test MSS stored procedures called from MobiLink scripts. |
|
you could use something like this, written by Narayana Vyas Kondreddi: SET NOCOUNT ON DECLARE @execstr VARCHAR(255) SET @execstr = RTRIM('echo ' + COALESCE(LTRIM(@msg),'-') + CASE WHEN (@overwrite = 1) THEN ' > ' ELSE ' >> ' END + RTRIM(@file)) EXEC master..xp_cmdshell @execstr SET NOCOUNT OFF or use instead xp_logevent { error_number , 'message' } [ , 'severity' ] to log to the event log |
|
I would use RAISERROR ... WITH LOG to write to the current SQL Server error log file, cf. raiserror('Volker''s test message', 0, 1) with log I guess RAISERROR with a low severity (0-10, 0 in this case) should not interfere with the execution of the stored procedure as they should be treated as information/warning, not as a real error. That being said, SQL Anywhere's MESSAGE statement seems so much easier to understand and use. But that's a fact you do already know:) |
@Breck: Now that you blogged about this topic, could you add why to prefer one solution over the other? Is it that xp_cmdshell can use an arbitrary file as output - though SA's MESSAGE statement does not allow that, either?
I'm asking just out of curiosity because I have never seriously tried to use "MESSAGE" with MS SQL Server - and I can't answer the final question on your blog article as well:)
The xp_cmdshell solution is simple(r)... it just writes your text to your file. I am sure the other two solutions work, but I am quite lazy. The xp_logevent solution requires you to hunt around in the Event Viewer to find your stuff... I think... I couldn't be bothered to do the research :) Same thing with RAISERROR... where the heck is the SQL Server error log file? Don't know, don't care :) The faster I can find and fix my bugs and ship the code off to my client, the happier I am. (I just know someone's going to say "debugger"... I don't use SQL Anywhere's debugger, why would I use SQL Server's? Does it even have a debugger? Again... don't know, don't care... Real Programmers Don't Use Debuggers :)
That's very reasonable (though I do use debuggers...).
AFAIK, the ERRORLOG file (and its "ancestors" ERRORLOG.1, ERRORLOG.2 etc.) are usually stored in the LOG subdirectory of the MS SQL Server installation. And yes, I had to lookup that, too:)