which cmd or function can transfer printed message to a sql file?

Question

Post reply

which cmd or function can transfer printed message to a sql file?

lvzheng

Valued Member

Points: 61
More actions
October 21, 2010 at 3:34 am

#230113

after a query in management studio, i get a result and a information,how can i transer the information message to a sql file by any cmd or function or sp? thanks

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Adiga One Orange Chip Points: 27284 More actions · Answer 1

Check if the "Results to File" option in SSMS meets your requirement.

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga

lvzheng Valued Member Points: 61 More actions · Answer 2

thank you ,but i want a function or procedure to meet that

Lowell SSC Guru Points: 323518 More actions · Answer 3

lvzheng (10/21/2010)
thank you ,but i want a function or procedure to meet that

you won't be able to capture the messages via TSQL. the information is generated, but not capturable via TSQL.

you'll need to copy/paste them manually, or as Adiga suggested, when using SSMS and Text mode, you can capture results to file.

outside of that, if you can write something in .NET, you have to add an eventAddHandler on the SQL connection.InfoMessage, then you could write something to automatically log any messages that hit the connection.

that's actually the same way SSMS does it (to put the messages in the Message tab window); it's just SSMS is not set up to log the messages.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 4

You can always raise an error to the SQL log file.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

lvzheng Valued Member Points: 61 More actions · Answer 5

lvzheng

Valued Member

Points: 61

October 22, 2010 at 12:12 am

#1240727

got it .thank you

Hardy21 SSCrazy Eights Points: 9708 More actions · Answer 6

You can also use "xp_logevent"

like: EXEC xp_logevent 60000, <<your message>>, informational

It Logs a user-defined message in the SQL Server log file and in the Windows Event Viewer. xp_logevent can be used to send an alert without sending a message to the client.

Thanks

lvzheng Valued Member Points: 61 More actions · Answer 7

then how can i get them from Windows Event Viewer?

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 8

lvzheng (10/22/2010)
then how can i get them from Windows Event Viewer?

xp_readerrorlog. See the following article for a bit more information...

http://www.sqlteam.com/article/using-xp_readerrorlog-in-sql-server-2005

BUT, now that you've said that, WHY do you feel it necessary to write something to a text file only to read it using T-SQL later on? Why not just write to a table to begin with?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Lowell SSC Guru Points: 323518 More actions · Answer 9

Jeff's right on target.

no matter what, if a message was generated by SQL(i.e. #row(s) affected or PRINT statements), you cannot read it into a variable with TSQL, whether you wanted to use the xp_* functions or not.

If you are trying to capture error messages raised, that is certainly possible, without trying to fiddle with messages at all.

you can use the @@ROWCOUNT to buidl the same message SSMS does.

If you are printing something, you can insert it to a table instead of using print.

The better question is what are you trying to do that you think you need to capture the messages?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!