Professional Web Applications Themes

Capturing the plain-text output of a stored procedure - Microsoft SQL / MS SQL Server

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 yzer, 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' ...

  1. #1

    Default Capturing the plain-text output of a stored procedure

    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 yzer, 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


    Dimitris Guest

  2. #2

    Default Re: Capturing the plain-text output of a stored procedure

    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]
    - com

    "Dimitris" <com> wrote in message
    news:3f439188$0$209$zen.co.uk... 
    command 
    the 
    (in 
    file 
    1. 
    my 


    Nicholas Guest

  3. #3

    Default Re: Capturing the plain-text output of a stored procedure

    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.cfm?DepartmentID=800
    http://www.sqlserverfaq.com
    http://www.mssqlserver.com/faq
    -----------------------

    "Dimitris" <com> wrote in message
    news:3f439188$0$209$zen.co.uk... 
    command 
    of the 
    output (in 
    on file 
    file 1. 
    (6.420 
    file 1. 
    1. 
    (3.358 
    1. 
    that my 


    Dan Guest

  4. #4

    Default Re: Capturing the plain-text output of a stored procedure

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

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

    "Dimitris" <com> wrote in message
    news:3f439188$0$209$zen.co.uk... 
    command 
    the 
    (in 
    file 
    1. 
    my 


    Nicholas Guest

Similar Threads

  1. Stored Procedure Output using ASP
    By m3ckon in forum ASP Database
    Replies: 1
    Last Post: May 10th, 10:21 PM
  2. Capturing Stored Proc Print Output from ASP?
    By Mario T. Lanza in forum ASP Database
    Replies: 7
    Last Post: November 11th, 03:45 PM
  3. how to script the text file output from stored procedure?
    By === in forum Microsoft SQL / MS SQL Server
    Replies: 7
    Last Post: August 15th, 09:54 PM
  4. Stored Procedure with Image Output
    By Mark in forum Microsoft SQL / MS SQL Server
    Replies: 0
    Last Post: July 1st, 09:57 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139