Início / dba / Perguntas / 108498
Accepted
msgisme
Asked: 2015-07-30 12:58:52 +0800 CST

Como capturar instruções PRINT do TSQL chamado pelo procedimento armazenado CLR

  • 772

Eu tenho um procedimento armazenado CLR que (entre outras coisas) chama um procedimento armazenado TSQL. O procedimento armazenado TSQL executa algum SQL dinâmico e imprime o SQL antes de ser executado para fins de depuração. Nada das PRINTdeclarações aparece no cliente. Qual é uma boa maneira de nos permitir ver esses comandos quando precisamos solucionar problemas?

Código de amostra:

Código C# em 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();    
        }
    }
}

Processo chamado por 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
sql-server sql-clr

1 respostas

  1. Best Answer

    Para capturar mensagens (de PRINTou RAISERROR('', 1, 10)como ele) em .NET (independentemente de chamar um procedimento armazenado ou SQL ad hoc), você precisa configurar um método que será chamado pelo evento SqlConnection.InfoMessage .

    A implementação básica é a seguinte:

    [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;
}

    Neste ponto, você ainda precisa fazer algo com a Mensagem. Embora existam algumas opções, algumas delas têm implicações de segurança devido ao ambiente. O host CLR do SQL Server é altamente restrito em comparação com o CLR principal em execução no sistema operacional usado para aplicativos do Windows, aplicativos de console e aplicativos ASP.NET.

    Se você usar a staticabordagem do método conforme mostrado acima, o que você fizer com a Message afetará o PERMISSION_SETvalor do assembly que contém este código:

    • Se você simplesmente deseja imprimir a mensagem de volta para o usuário, pode fazer algo como o seguinte, que pode ser feito em um SAFEassembly:

      SqlContext.Pipe.Send(args.Message);

    • Se você deseja gravar as mensagens em um arquivo, pode fazer algo como o seguinte, que pode ser feito em um EXTERNAL_ACCESSassembly:

      File.AppendAllText(FILE_PATH_CONSTANT, args.Message);

      Isso pode causar latência extra e não valer a pena. Mas, por outro lado, se o processo falhar no meio, tudo o que foi gravado no arquivo ainda estará lá, então definitivamente há algum benefício nessa abordagem.

    • Mas se você quiser armazenar as mensagens em uma variável para fazer algo mais tarde, precisará de uma variável de classe estática, pois o método para capturar a mensagem é static. Por exemplo, você pode declarar private static m_Messages StringBuilder = new StringBuiler("");. Então, no seu CaptureMessagemétodo, você pode fazer o seguinte:

      m_Messages.AppendLine(args.Message);

      Isso parece bastante simples. E para a maioria dos ambientes fora do SQLCLR é. No entanto, usar uma variável estática em SQLCLR requer que o assembly tenha um PERMISSION_SETde UNSAFE, e é melhor evitar isso, se possível. E o motivo pelo qual o assembly deve ser marcado como UNSAFEé que o AppDomain em que esse código está sendo executado é compartilhado entre todas as sessões/SPIDs. Portanto, uma variável de classe estática também é compartilhada entre sessões e isso pode levar a um comportamento muito estranho .

    Felizmente, nem toda a esperança está perdida se você quiser coletar mensagens para uma variável de instância. Para fazer isso, você precisa definir o manipulador embutido usando um delegado anônimo. O método anônimo está dentro do mesmo escopo que o resto do seu código, então ele tem acesso a variáveis ​​locais. Isso é bastante útil, pois permite que você armazene facilmente as mensagens em um assembly que tenha um PERMISSION_SETof SAFE(o que é altamente preferido).

    [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();    
        }
    }
}
    • 6

relate perguntas