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.

asked 29 May '11, 07:02

Breck%20Carter's gravatar image

Breck Carter
27.4k424585837
accept rate: 21%

edited 30 May '11, 04:38

Volker%20Barth's gravatar image

Volker Barth
30.0k294448654

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

(14 Jun '11, 03:30) Volker Barth
Replies hidden

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

(14 Jun '11, 06:21) Breck Carter

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

(14 Jun '11, 06:52) Volker Barth

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

permanent link

answered 30 May '11, 03:27

Martin's gravatar image

Martin
8.6k115149237
accept rate: 14%

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

permanent link

answered 30 May '11, 04:37

Volker%20Barth's gravatar image

Volker Barth
30.0k294448654
accept rate: 32%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×102
×28
×11

question asked: 29 May '11, 07:02

question was seen: 1,662 times

last updated: 14 Jun '11, 06:52