Capturing the plain-text output of a stored procedure

[Dimitris]

I'm using ADO.NET to call a stored procedure that runs the "BACKUP" command
on selected databases. Is there any way I can capture the text output of the
stored proc and return it to ADO.NET for displaying in my application?

When I run the stored procedure from SQL Query Analyzer, I get an output (in
the messages tab) like:

Processed 1496 pages for database 'OldBO', file 'TB_BackOffice_Data' on file
1.
Processed 1 pages for database 'OldBO', file 'TB_BackOffice_Log' on file 1.
BACKUP DATABASE successfully processed 1497 pages in 1.909 seconds (6.420
MB/sec).
Processed 120 pages for database 'Profile', file 'Profile_Data' on file 1.
Processed 1 pages for database 'Profile', file 'Profile_Log' on file 1.
BACKUP DATABASE successfully processed 121 pages in 0.293 seconds (3.358
MB/sec).
Processed 104 pages for database 'Quotes', file 'Quotes_Data' on file 1.
Processed 1 pages for database 'Quotes', file 'Quotes_Log' on file 1.

etc...

This is the information I'd like to capture (plus any other messages that my
be created from "PRINT" statements within the sp).

Thanks,
Dimitris

[Dan Guzman]

Informational messages can be retrieved by handling the your connection
object InfoMessage event. This includes PRINT and RAISERROR statements
(severity < 11).

--
Hope this helps.

Dan Guzman
SQL Server MVP

-----------------------
SQL FAQ links (courtesy Neil Pike):

http://www.ntfaq.com/Articles/Index....partmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
-----------------------

"Dimitris" <sp**@hotmail.com> wrote in message
news:3f*********************@lovejoy.zen.co.uk...

I'm using ADO.NET to call a stored procedure that runs the "BACKUP" command on selected databases. Is there any way I can capture the text output of the stored proc and return it to ADO.NET for displaying in my application?

When I run the stored procedure from SQL Query Analyzer, I get an output (in the messages tab) like:

Processed 1496 pages for database 'OldBO', file 'TB_BackOffice_Data' on file 1.
Processed 1 pages for database 'OldBO', file 'TB_BackOffice_Log' on file 1. BACKUP DATABASE successfully processed 1497 pages in 1.909 seconds (6.420 MB/sec).
Processed 120 pages for database 'Profile', file 'Profile_Data' on file 1. Processed 1 pages for database 'Profile', file 'Profile_Log' on file 1. BACKUP DATABASE successfully processed 121 pages in 0.293 seconds (3.358 MB/sec).
Processed 104 pages for database 'Quotes', file 'Quotes_Data' on file 1. Processed 1 pages for database 'Quotes', file 'Quotes_Log' on file 1.

etc...

This is the information I'd like to capture (plus any other messages that my be created from "PRINT" statements within the sp).

Thanks,
Dimitris

[Nicholas Paldino [.NET/C# MVP]]

Dimitris,

What I think you want to do is get the connection for the server. Once
you have the instance of the SqlConnection, you can hook up to the
InfoMessage event, which ^should^ be fired when there is message information
sent with the resultset.

Hope this helps.
--
- Nicholas Paldino [.NET/C# MVP]
- ni**************@exisconsulting.com

"Dimitris" <sp**@hotmail.com> wrote in message
news:3f*********************@lovejoy.zen.co.uk...

I'm using ADO.NET to call a stored procedure that runs the "BACKUP" command on selected databases. Is there any way I can capture the text output of the stored proc and return it to ADO.NET for displaying in my application?

When I run the stored procedure from SQL Query Analyzer, I get an output (in the messages tab) like:

Processed 1496 pages for database 'OldBO', file 'TB_BackOffice_Data' on file 1.
Processed 1 pages for database 'OldBO', file 'TB_BackOffice_Log' on file 1. BACKUP DATABASE successfully processed 1497 pages in 1.909 seconds (6.420
MB/sec).
Processed 120 pages for database 'Profile', file 'Profile_Data' on file 1.
Processed 1 pages for database 'Profile', file 'Profile_Log' on file 1.
BACKUP DATABASE successfully processed 121 pages in 0.293 seconds (3.358
MB/sec).
Processed 104 pages for database 'Quotes', file 'Quotes_Data' on file 1.
Processed 1 pages for database 'Quotes', file 'Quotes_Log' on file 1.

etc...

This is the information I'd like to capture (plus any other messages that my be created from "PRINT" statements within the sp).

Thanks,
Dimitris

[Nicholas Paldino [.NET/C# MVP]]

Also, if you could not set the reply address to valid address that is
not yours (such as) sp**@hotmail.com, I would appreciate it.

--
- Nicholas Paldino [.NET/C# MVP]
- ni**************@exisconsulting.com

"Dimitris" <sp**@hotmail.com> wrote in message
news:3f*********************@lovejoy.zen.co.uk...

I'm using ADO.NET to call a stored procedure that runs the "BACKUP" command on selected databases. Is there any way I can capture the text output of the stored proc and return it to ADO.NET for displaying in my application?

When I run the stored procedure from SQL Query Analyzer, I get an output (in the messages tab) like:

Processed 1496 pages for database 'OldBO', file 'TB_BackOffice_Data' on file 1.
Processed 1 pages for database 'OldBO', file 'TB_BackOffice_Log' on file 1. BACKUP DATABASE successfully processed 1497 pages in 1.909 seconds (6.420
MB/sec).
Processed 120 pages for database 'Profile', file 'Profile_Data' on file 1.
Processed 1 pages for database 'Profile', file 'Profile_Log' on file 1.
BACKUP DATABASE successfully processed 121 pages in 0.293 seconds (3.358
MB/sec).
Processed 104 pages for database 'Quotes', file 'Quotes_Data' on file 1.
Processed 1 pages for database 'Quotes', file 'Quotes_Log' on file 1.

etc...

This is the information I'd like to capture (plus any other messages that my be created from "PRINT" statements within the sp).

Thanks,
Dimitris