3

I have a CLR stored procedure that (among other things) calls a TSQL stored procedure. The TSQL stored procedure runs some dynamic SQL, and it prints the SQL before it runs for debugging purposes. Nothing from the PRINT statements shows up in the client. What is a good way to allow us to see these commands when we need to troubleshoot?

Sample code:

C# code in dll

public static void Print_CLR()
{
    using (SqlConnection conn = new SqlConnection("context connection=true"))   
    {
            conn.Open();
            using (SqlCommand c = new SqlCommand("exec dbo.Print_TSQL", conn))
            {   
            c.ExecuteNonQuery();    
        }
    }
}

Proc called by CLR

CREATE PROC DBO.Print_TSQL AS
PRINT 'WTF'
GO
--Exposing proc to SQL
CREATE PROC dbo.Print_CLR AS
EXTERNAL NAME 
[CLRUtility].[CLRUtility.CLRUtility].Print_CLR
GO
--Executing, nothing is PRINTed for the client
EXEC dbo.Print_CLR
6

In order to capture messages (either from PRINT or RAISERROR('', 1, 10) or like it) in .NET (regardless of calling a Stored Procedure or ad hoc SQL), you need to set up a method that will get called by the SqlConnection.InfoMessage event.

The basic implementation is as follows:

[SqlProcedure()]
public static void Print_CLR()
{
    using (SqlConnection conn = new SqlConnection("context connection=true"))   
    {
        conn.InfoMessage += new SqlInfoMessageEventHandler(CaptureMessage);
        conn.Open();

        using (SqlCommand c = new SqlCommand("exec dbo.Print_TSQL", conn))
        {   
            c.ExecuteNonQuery();    
        }
    }
}

private static void CaptureMessage(object sender, SqlInfoMessageEventArgs args)
{
  // do something with args.Message

  return;
}

At this point, you still need to do something with the Message. While there are a few options, some of them have security implications due to the environment. SQL Server's CLR host is highly constrained compared to the main CLR running on the OS that is used for Windows Apps, Console Apps, and ASP.NET apps.

If you use the static method approach as shown above, then what you do with the Message will impact the PERMISSION_SET value for the assembly containing this code:

  • If you simply want to print the message back to the user, then you can do something like the following which can be done in a SAFE assembly:

    SqlContext.Pipe.Send(args.Message);
    
  • If you want to write the messages to a file, then you can do something like the following which can be done in an EXTERNAL_ACCESS assembly:

    File.AppendAllText(FILE_PATH_CONSTANT, args.Message);
    

    This might cause extra latency and not be worth it. But on the other hand, if the process fails in the middle, whatever was written to the file would still be there, so there is definitely some benefit to this approach.

  • But if you want to store the messages in a variable to do something with later, then you will need a static class variable since the method to capture the message is static. For example, you can declare private static m_Messages StringBuilder = new StringBuiler("");. Then, in your CaptureMessage method you can do the following:

    m_Messages.AppendLine(args.Message);
    

    This seems simple enough. And for most any environment outside of SQLCLR it is. However, using a static variable in SQLCLR requires that the assembly have a PERMISSION_SET of UNSAFE, and it is best to avoid that if at all possible. And the reason that the assembly is required to be marked as UNSAFE is that the AppDomain where this code is running is shared across all Sessions / SPIDs. Hence, a static class variable is also shared across Sessions and that can lead to very odd behaviour.

Thankfully, all hope is not lost if you want to collect messages to an instance variable. To do this, you need to define the handler inline using an anonymous delegate. The anonymous method is within the same scope as the rest of your code so it has access to local variables. This is quite handy as it allows you to easily store the messages in an assembly that has a PERMISSION_SET of SAFE (which is highly preferred).

[SqlProcedure()]
public static void Print_CLR()
{
    StringBuilder _Messages = new StringBuilder("");

    using (SqlConnection conn = new SqlConnection("context connection=true"))   
    {
        conn.InfoMessage += delegate(object sender, SqlInfoMessageEventArgs args)
        {
            SqlConnection _Connection = (SqlConnection)sender;
            // _Connection.DataSource is the "Server" of the ConnectionString
            // _Connection.Database is the CURRENT database of the Connection

            _Messages.Append(args.Message);

            return;
        };

        conn.Open();

        using (SqlCommand c = new SqlCommand("exec dbo.Print_TSQL", conn))
        {   
            c.ExecuteNonQuery();    
        }
    }
}
  • PowerShell version sqlskills.com/blogs/jonathan/… SO answer stackoverflow.com/q/1880471/181965 – billinkc Jul 29 '15 at 21:22
  • @billinkc What does this have to do with PowerShell? That is way off topic in terms of this question, which is very much specific to SQLCLR. The SO question does have an answer that shows the proper syntax, but has no explanation as to how it pertains to SQLCLR, and that specific context is very important given the security implications of the standard approach. So no, this question is not a duplicate and should not have been closed. Please re-open it. Thanks. – Solomon Rutzky Jul 30 '15 at 4:03
  • 1
    Thanks srutzky! I was able to use to your first example to get my code working (the assembly is already UNSAFE because we are doing filesystem operations, so that is not a concern), but I'd like to understand the value of using the delegate. Can you clarify what you mean about the static class variable? My 'CaptureMessage' code is just SqlContext.Pipe.Send(args.Message). – msgisme Jul 30 '15 at 20:35
  • @msgisme I updated my answer. For just doing the Pipe.Send() it doesn't matter. But if you're storing to a static variable it could make a difference as static variables are shared across sessions which is rarely desirable. Fortunately, what you are doing can be done in a SAFE or EXTERNAL_ACCESS assembly. Regarding your statement of "the assembly is already UNSAFE because we are doing filesystem operations": that is not good. File system and network operations only require EXTERNAL_ACCESS which is what you should be using. Don't use UNSAFE unless absolutely necessary. – Solomon Rutzky Aug 6 '15 at 21:05
  • 1
    @srutzky thanks for the update. I found that we are using UNSAFE because there are static variables in the code. I was able to remove them and now we are running in EXTERNAL_ACCESS. Thanks again for the help! – msgisme Aug 6 '15 at 22:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.