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


2 comments:

Anonymous 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.

Anonymous 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).