Monday, June 13, 2011

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:

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
-- To update the currently configured value for advanced options.
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
-- To update the currently configured value for this feature.

DROP PROCEDURE write_diagnostic_message
-- ignore error

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

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?'s a continuing mystery to me.


alexeyk77 said...

in mssql in easy way possible to easy get any query plan from SP or compound statements without voodoo.
EM very good tool, much better than Sybase Central.
+free OLAP
+many services e.t.c.

alexeyk77 said...

+ optimiser has real multi local database support (queries that using tables from different local databases works fast, opposite to SA which can't execute it effectively because of proxy table limitations).