May 10, 2011 at 11:43 am
How do I capture the output of the print command within t-sql and dump it to a text file which is datestamped?
May 10, 2011 at 12:08 pm
from TSQL? you can't; what you'd have to do instead is to change the PRINT command to a SELECT; then you can put it in a variable or a table and do something with it.
in .NET and ADODB, the PRINT statements are exposed as Information Messages to the Command Object
that's what you see happening in SSMS; the behind the scenes SQLCommand's error messages and info messages get directed to the richtextbox on the Messages Tab.
so instead of Print, you might do something like :
declare @Results varchar(max)
SET @Results=''
--'Do Stuff
--Print 'Data Accomplished'
SELECT @Results= @Results + 'Data Accomplished'
--'Do More Stuff
--'do something with the string in @Results
Lowell
May 10, 2011 at 1:24 pm
Here's the MS data on PRINT: http://msdn.microsoft.com/en-us/library/ms176047.aspx
The MS article on returning data to a client application (http://msdn.microsoft.com/en-us/library/ms189583.aspx) includes this:
The Database Engine can return information to the caller in one of two ways:
1.Errors
The errors from sys.messages with a severity of 11 or higher.
Any RAISERROR statement with a severity of 11 or higher.
2.Messages
The output of the PRINT statement.
The output of several DBCC statements.
The errors from sys.messages with a severity of 10 or lower.
Any RAISERROR statement with a severity of 10 or lower.
Applications using APIs such as ActiveX Data Object (ADO) and OLE DB cannot generally distinguish between errors and messages. In Open Database Connectivity (ODBC) applications, messages generate a SQL_SUCCESS_WITH_INFO function return code, and errors usually generate a SQL_ERROR return code. The difference is most pronounced in DB-Library, in which errors are returned to the application error-handler function, and messages are returned to the application message-handler function. Similarly, when using the SqlClient provider, errors cause the SqlException exception to be thrown; messages do not alter control flow and can be intercepted by application code by registering a callback for the InfoMessage event handler.
Data from a PRINT command can be captured by some types of SQL connections, it's a question of knowing the right events, et al, to handle. The article referenced above has more information on this, and links to further articles with even more.
However, T-SQL has none of those capabilities, since it's not event-aware (nor is it meant to be). Nor, actually, can T-SQL write to a text file, regardless of what you want to write.
From T-SQL, you can call other DLLs that have the ability to write to text files. CLR procedures are one example, BCP is another. Those can be called by T-SQL, and they can write for you. And a CLR proc could, theoretically, use a connection that would intercept a PRINT message, I think. Start from the articles I linked to about PRINT and data returns, and then dig into CLR coding a bit, and you can probably come up with a way to do this.
It will be easier to pass a value to a CLR procedure, as Lowell outlined, and have it use that as content to write to a file. If that'll do what you want.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply