The MESSAGE Statement In SQL Server

Question: 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.

Answer: When this question was asked on the SQL Anywhere Forum, three answers were given:

• xp_cmdshell,

• xp_logevent and

• raiserror.

I chose the first answer: Call SQL Server's xp_cmdshell procedure to execute a Windows ECHO statement which uses the ">>" redirection operator to append a string to a file.

Here's the code, warts and all:

-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO

BEGIN TRY
   DROP PROCEDURE write_diagnostic_message
END TRY
BEGIN CATCH
   -- ignore error
END CATCH
GO  

CREATE PROCEDURE write_diagnostic_message
   @message VARCHAR ( 1000 ) 
AS
BEGIN
   DECLARE @cmd VARCHAR ( 2000 )
   SET NOCOUNT ON
   SET @cmd = 'ECHO ' + CONVERT ( VARCHAR, CURRENT_TIMESTAMP, 121 ) + ' ' + @message + ' >>c:\temp\messages.txt'
   EXEC master..xp_cmdshell @cmd, no_output
END
GO

The crap stuff on lines 1 through 12 is necessary to prevent xp_cmdshell from failing with an error message.

The BEGIN TRY and BEGIN CATCH blocks on lines 14 through 20 are a quick-and-dirty way to execute a statement (in this case DROP PROCEDURE) which may or may not work and you don't care which.

The procedure defined on lines 22 through 31 provides a slightly-easier-to-call interface to the xp_cmdshell solution:

• The DECLARE on line 26 is necessary because the EXEC statement can't handle expressions passed as arguments to stored procedures.

• The SET NOCOUNT ON on line 27 is more crap stuff required by SQL Server; you can read about it in the "Best Practices" section of the page documenting the CREATE PROCEDURE statement.

• The SET statement on line 28 uses CONVERT to append the current date and time in front of the @message parameter, and builds an ECHO ... >>filespec command."

• The EXEC statement on line 29 passes the ECHO command to xp_cmdshell, and the no_output option prevents xp_cmdshell itself from trying to display anything on screen.

Here's how to code a call to write_diagnostic_message from another SQL Server procedure:

EXEC write_diagnostic_message 'This is a test'

Here's the output in c:\temp\messages.txt:

2011-06-13 06:00:59.897 This is a test 

Caution: Don't try to pass an expression as the argument to write_diagnostic_message, that won't work in SQL Server. You'll need a DECLARE another variable and use SET to fill it from the expression, and then pass the variable to write_diagnostic_message.

Question: Why do people use SQL Server after using SQL Anywhere?

Answer: Because they are forced to by management? ...it's a continuing mystery to me.

---