How to capture everything from Message Tab from SQL Server to C# Application?

Question

Little bit of background of my Application.

I am working on a File Watcher windows service using C# that looks for .bak files in a particular folder and then use it to restore the database that file belongs to.

The Restored Database has a Stored Procedure that calls 10 different stored procedure. It's the File Watcher's functionality to execute the stored procedure after the Restore is done.

The Stored Procedure is [1_IMPORT_DATA_AND_PROCESS_ALL] which calls 10 different stored procedure within itself.

This is the Method which is executing the Stored Procedure after the restore is complete.

// Trigger Stored Procedure after restore. 
private void triggerSP(String connectionStr)
{
    // This doesn't open the Connection. conn.Open() has to be explicitly called.
    
    SqlConnection conn = new SqlConnection(connectionStr);
    try
    {
    
    conn.Open();
    conn.FireInfoMessageEventOnUserErrors = true;
    // Capture messages returned by SQL Server.
    conn.InfoMessage += delegate (object sender, SqlInfoMessageEventArgs e)
    {
        
        message += " -> " + e.Message + " -> ";
    };
    //conn.InfoMessage += new SqlInfoMessageEventHandler(cn_InfoMessage);
    
    //.create a command object identifying the stored procedure.
    SqlCommand cmd = new SqlCommand("[dbo].[1_IMPORT_DATA_AND_PROCESS_ALL]", conn);
    cmd.CommandTimeout = 0;
    // 2. set the command object so it knows to execute a stored procedure.
    cmd.CommandType = CommandType.StoredProcedure;

    // Add a check here as well.
    // execute the command.
    SqlDataReader rdr = cmd.ExecuteReader();


    string[] info = new string[] { "Message: \n" + message };
    WriteToFile(info);

    // Since we are not using - using block we have to explicitly call Close() to close the connection.
    conn.Close();
    }
    catch (SqlException SqlEx){
    string[] error = new string[3] ;

    string msg1 = "Errors Count:" + SqlEx.Errors.Count;
    string msg2 = null;

    foreach (SqlError myError in SqlEx.Errors)
        msg2 += myError.Number + " - " + myError.Message + "/" ;

    conn.InfoMessage += delegate (object sender, SqlInfoMessageEventArgs e)
    {
        message += "\n" + e.Message;
    };

    error[0] = msg1;
    error[1] = msg2;
    error[2] = message;

    WriteToFile(error);
    }

    finally
    {
    //call this if exception occurs or not
    //in this example, dispose the WebClient
    conn.Close();
    }

}

Problem

I am only getting back the Message outputs from the very first stored procedure i.e [1_IMPORT_DATA_AND_PROCESS_ALL] and not from the stored procedure which are being called from within [1_IMPORT_DATA_AND_PROCESS_ALL] as shown bellow.

As soon as the 1st Sp calls another SP my codes stops reading the Messages.

I want to capture all the Messages that are being printed, something Like this (image below), which are the actual messages that are being printed when I execute the SP in SSMS.

This particular line is fetching the Messages from the SP

conn.InfoMessage += delegate (object sender, SqlInfoMessageEventArgs e)
    {
        message += " -> " + e.Message + " -> ";
    };

So far I have referred to everything form this question and it's derivatives.

I can't change the Stored Procedure now, I can only make changes to my C# Application.

Hope my question is clear.

Answer 1

Good day,

Note! This message is not marked as "community wiki" and as such it written by a specific person under his name and this is not a shared article. If you have comments then please use the comments instead of changing the content, which the OP meant to provide (for example extra learning points in the content). Thanks!

In the following script I present an example of handling nested Stored Procedure error.The basic Idea is to use TRY/CATCH in order to prevent raising the error and stop the transaction, and the OUTPUT is used to return the error information to the upper level SP

This is only a basic example...

CREATE or ALTER PROCEDURE L1 (
    @InputInt int,
    @ErrMessage NVARCHAR(MAX) OUTPUT,
    @ErrNum INT OUTPUT
)AS
    SELECT @@NESTLEVEL AS 'Inner Level'; -- this information present the level of the SP during the execution. It is not needed for the solution but for the sake of the learning and understanding of nested SP
    Select 'Start L1'

    BEGIN TRY  
        -- When the ionput is 0 we Generate a divide-by-zero error.  
        SELECT 1/@InputInt;  
    END TRY  
    BEGIN CATCH
        SET @ErrMessage = ERROR_MESSAGE()
        SELECT @ErrMessage
    END CATCH;

    SET @ErrNum = @@ERROR
    IF (@ErrNum > 0) Begin
       SELECT 'L1 error Number: ' + CONVERT(NVARCHAR(10), @ErrNum)
       Return
    END
    ELSE
       select 'L1 OK'
GO

CREATE or ALTER PROCEDURE L2 (
    @InputInt int
) AS   
    Declare @ErrMessage NVARCHAR(MAX) = '', @ErrNum INT = 0
    SELECT @@NESTLEVEL AS 'Outer Level';
    BEGIN TRY
        EXEC L1 @InputInt, @ErrMessage, @ErrNum;
    END TRY
    BEGIN CATCH
        SELECT 'There was error!'
        select @@ERROR
    END CATCH
GO

EXECUTE L2 1 -- OK
GO

EXECUTE L2 0; --Raise error in the nested stored procedures 
GO