Hello people,
Good Morning!
In this post I would like to demonstrate to you as I am (and intend to continue) posting several cool things about CLR, such as sending warnings (PRINT) and error messages (RAISEERROR) to SQL Server when your CLR compiled Stored Procedures are executed.
Although the post is small, I decided to create a post just with that, because I use this class a lot in the SP's that I will publish here in the future, so I understand that it is easier to create this reference than to repost this class several times.
Server.cs file source code (prerequisite)
|
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 |
using System; using System.Collections.Generic; using System.Data.SqlClient; namespace Bibliotecas.Model { public class ServidorAtual { public string NomeServidor { get; set; } public ServidorAtual() { try { using (var conn = new SqlConnection(Servidor.Context)) { conn.Open(); using (var cmd = conn.CreateCommand()) { cmd.CommandText = "SELECT @@SERVERNAME AS InstanceName"; NomeServidor = (string) cmd.ExecuteScalar(); } var partes = NomeServidor.Split('\\'); if (partes.Length <= 1) return; if (string.Equals(partes[0], partes[1], StringComparison.CurrentCultureIgnoreCase)) NomeServidor = partes[0]; } } catch (Exception ex) { throw ex; } } } public static class Servidor { public static string Ds_Usuario => "Usuario"; public static string Ds_Senha => "Senha"; public static string PRODUCAO => "data source=PRODUCAO;initial catalog=CLR;persist security info=False;Enlist=False;packet size=4096;user id='" + Ds_Usuario + "';password='" + Ds_Senha + "'"; public static string Context => "context connection=true"; public static string Localhost => "data source=LOCALHOST;initial catalog=CLR;persist security info=False;Enlist=False;packet size=4096;user id='" + Ds_Usuario + "';password='" + Ds_Senha + "'"; public static string getLocalhost() { var servidorAtual = new ServidorAtual().NomeServidor; return "data source=" + servidorAtual + ";initial catalog=CLR;persist security info=False;Enlist=False;packet size=4096;user id='" + Ds_Usuario + "';password='" + Ds_Senha + "'"; } public static List<string> Servidores { get { var servidores = new List<string> { PRODUCAO, Localhost }; return servidores; } } } } |
Return class source code:
|
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 |
using System; using System.Data; using System.Data.SqlClient; using System.Diagnostics; using Microsoft.SqlServer.Server; namespace Bibliotecas.Model { public static class Retorno { public static void Erro(string erro) { /* IF (OBJECT_ID('CLR.dbo.Log_Erro') IS NOT NULL) DROP TABLE CLR.dbo.Log_Erro CREATE TABLE CLR.dbo.Log_Erro ( Id_Erro INT IDENTITY(1,1), Dt_Erro DATETIME DEFAULT GETDATE(), Nm_Objeto VARCHAR(100), Ds_Erro VARCHAR(MAX), CONSTRAINT [PK_Log_Erro] PRIMARY KEY CLUSTERED (Id_Erro) ) */ using (var conexao = new SqlConnection(Servidor.getLocalhost())) { var comando = new SqlCommand("INSERT INTO dbo.Log_Erro (Nm_Objeto, Ds_Erro) VALUES (@Nm_Objeto, @Ds_Erro)", conexao); var stackTrace = new StackTrace(); var objeto = stackTrace.GetFrame(1).GetMethod().Name; comando.Parameters.Add(new SqlParameter("@Nm_Objeto", SqlDbType.VarChar, 100)).Value = objeto; comando.Parameters.Add(new SqlParameter("@Ds_Erro", SqlDbType.VarChar, 8000)).Value = erro; conexao.Open(); comando.ExecuteNonQuery(); } throw new ApplicationException(erro); } public static void Mensagem(string mensagem) { using (var conexao = new SqlConnection(Servidor.Context)) { var Comando = new SqlCommand("IF ( (512 & @@OPTIONS) = 512 ) select 1 else select 0", conexao); conexao.Open(); if ((int) Comando.ExecuteScalar() != 0) return; var retorno = SqlContext.Pipe; retorno?.Send(mensagem.Length > 4000 ? mensagem.Substring(0, 4000) : mensagem); } } public static void RetornaReader(SqlDataReader dataReader) { var retorno = SqlContext.Pipe; retorno?.Send(dataReader); } } public class Ret : Exception { public Ret(string str) : base(str) { } } } |
Once this class is created in your CLR project, just import it into your Stored Procedure and start sending warnings and error messages, as I will demonstrate below:
Simulation of an error in a CLR method:
|
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 |
using Bibliotecas.Model; using System; using System.Data; using System.Data.SqlClient; using System.Data.SqlTypes; public partial class StoredProcedures { [Microsoft.SqlServer.Server.SqlProcedure] public static void stpTeste(SqlString Ds_String) { try { if (Ds_String.Value == "ERRO") { // Vou forçar um erro ao tentar inserir dados em uma tabela que não existe, causando uma Exception using (var Conexao = new SqlConnection(Servidor.Localhost)) { var Comando = new SqlCommand("INSERT INTO dbo.Erro (Nm_Objeto, Ds_Erro) VALUES (@Nm_Objeto, @Ds_Erro)", Conexao); Comando.Parameters.Add(new SqlParameter("@Nm_Objeto", SqlDbType.VarChar, 100)).Value = "Vai dar erro"; Comando.Parameters.Add(new SqlParameter("@Ds_Erro", SqlDbType.VarChar, 8000)).Value = "Descrição do Erro"; Conexao.Open(); Comando.ExecuteNonQuery(); } } Retorno.Mensagem("Alerta enviado para o banco (PRINT)"); } catch(Exception e) { Retorno.Erro("Mensagem de erro (RAISEERROR) gravada. Descrição do erro: " + e.Message); } } } |
Example of use:
Querying error history:
As you may have noticed, in the error method I put an SQL statement to record the history of when this method is called, making it have a CLR error log, making it easy to find possible problems in your CLR procedures.
That's it folks!
Any questions, leave it here in the comments.
Hug.
sql server clr c # csharp send warnings error messages warnings send text print error messages
sql server clr c # csharp send warnings error messages warnings send text print error messages
Good morning, does not recognize the [Microsoft.SqlServer.Server.SqlProcedure] SqlProcedure. Do you know what it could be?
Hi Denis,
Ok?
You have to add references to System and System.Data in your project.
Hug!
In the Server.cs file, we must enter the username and password, correct?
Even informing I get the error below:
.NET Framework error while executing user-defined routine or aggregation “stpTest”:
System.Data.SqlClient.SqlException: Login failure for user 'leonardo'.
System.Data.SqlClient.SqlException:
in System.Data.SqlClient.SqlInternalConnection.OnError (SqlException exception, Boolean breakConnection)
in System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning (TdsParserStateObject stateObj)
in System.Data.SqlClient.TdsParser.Run (RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
in System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin (Boolean enlistOK)
in System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin (ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject)
in System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover (String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart)
in System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist (SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
in System.Data.SqlClient.SqlInternalConnectionTds..ctor (DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
in System.Data.SqlClient.SqlConnectionFactory.CreateConnection (DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
in System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection (DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options)
in System.Data.ProviderBase.DbConnectionPool.CreateObject (DbConnection owningObject)
in System.Data.ProviderBase.DbConnectionPool.UserCreateRequest (DbConnection owningObject…
Call me in private to try to understand what's going on
good morning I'm trying to execute the code above but I'm having a hard time could you please help me
when attempting to execute this by reporting the following error in Line Code 29
Severity Code Description
Error CS0103 The name 'Server' does not exist in the current context
Gustavo, good morning.
I analyzed what you told me, and really forgot to put the code from the Server.cs file, which you should import into your project, since the Return class references it. I already edited the post and added the source code of this file there.
Thank you!