What's an MSS 2008 equivalent for MESSAGE TO CONSOLE?

[Breck_Carter]

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.

[VolkerBarth]

@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:)

[Breck_Carter]

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 🙂

[VolkerBarth]

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:)

[MCMartin · Accepted solution]

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

[VolkerBarth]

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:)