The forum will experience an outage sometime between February 10 at 7:00pm EST and February 12 at 11:59 EST for installation of security updates. The actual time and duration of the outage are unknown but attempts will be made to minimize the downtime. We apologize for any inconvenience.

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
26.6k418576824
accept rate: 21%

edited 30 May '11, 04:38

Volker%20Barth's gravatar image

Volker Barth
29.3k287438645

@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.6k114149237
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
29.3k287438645
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:

×101
×28
×11

question asked: 29 May '11, 07:02

question was seen: 1,626 times

last updated: 14 Jun '11, 06:52