I need to use a third party DLL in a trigger c# code in SQL Server CLR
But when I try to add reference it just shows some DLLs from SQL Server.
How can I add my third party dll to SQL Server?
csql serversql-clr
I need to use a third party DLL in a trigger c# code in SQL Server CLR
But when I try to add reference it just shows some DLLs from SQL Server.
How can I add my third party dll to SQL Server?
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();
}
}
}
Can I host the DLL on a different server so it will get run there?
How would this work? If you want to run code on a remote server, you would still need code on the local server to be able to make that remote call.
Although, you could set up an instance of Express Edition to host the SQLCLR code (it would need to be a Stored Procedure(s) and/or Function(s) instead of a Trigger), then create a Linked Server to that Express instance, and then have a T-SQL Trigger on the local table execute the SQLCLR Stored Procedure(s) and/or Functions over the Linked Server. In this manner, the local code calling the remote code is pure T-SQL. You wouldn't even need to enable CLR Integration on the local instance.
The reason I ask is that we don't want this code slowing down the database server, which it likely would by performing these other tasks within the code.
Whatever work you inside of a Trigger, be it a T-SQL or SQLCLR Trigger, will hold up the completion of that DML statement. Using SQLCLR does not really change anything there.
What are the exact requirements of the Trigger with regards to:
do things other than database work, such as write a JSON string to a txt file and update a system log of a different type of database.
Do those steps / actions need to be bound by the transaction, such that if the INSERT operation fails, the txt file does not exist and the log entry is not there?
Finding a way to do this asynchronously would be ideal (as also mentioned by Jonathan). However, making a Web Service call removes the transactional aspect so you might get files and log entries for rows that do not actually exist.
Using an NT service would be the least impacting on the INSERT operation, and unless you are using WITH (NOLOCK)
(which you shouldn't do), it will only see the results of successful INSERT operations. But, the service is disconnected and will have more latency than the Trigger model, and you will need to find a means of tracking which entries are "new" (the NT service will need to keep track of either a DATETIME
or maybe could use a ROWVERSION
value and compare to @@DBTS
each time). If your table already has a DATETIME
or DATETIME2
column being set upon INSERT
, then you can leverage that existing column, else you will need to add a DATETIME
/ DATETIME2
/ ROWVERSION
column. OR, if you have a PK based upon either an IDENTITY or Sequence, then you can use that value instead of adding a column, and the NT service would just need to keep track of the most recently used ID
. Where would you store that value? Text file? Registry? The "Cloud"? (just kidding about that last one ;-)
On the other hand, you could probably use Service Broker to match the two pieces together. When the Trigger fires, it can add a message to the Service Broker queue, including any necessary info from the INSERTED
table. This allows it to be asynchronous. Then, on the other end, the queue reader can use a SQLCLR stored procedure as the "activation procedure" to take that info and take the appropriate actions (creating the JSON file, logging the entry on the other system, etc).
This model might not require Service Broker. You can set up your own queue table, have a T-SQL Trigger dump entries into it, and then create a SQL Server Agent job that reads from the queue table and calls the SQLCLR Stored Procedure(s) and/or Function(s) asynchronously (whether they are located on the local server or across a Linked Server), and then removes the records when it has processed them successfully.
Best Answer
You can only add references to those assemblies which have been registered with Sql Server. If they are not registered, they will no show up in the Add References dialog.
There are a number of steps you'll need to do register a DLL, firstly you'll need to reconfigure your database:
Once this is done, Sql Server is CLR enabled. Next, you'll need to register your assembly:
If this last script runs correctly, the assembly is now registered, and will appear in the Add References dialog.
What you will need to consider though, is the application security of your Sql Server CLR configuration:
SAFE
, only in exceptional circumstances should you useEXTERNAL_ACCESS
orUNSAFE
.Assembly.Load()
is purposefully restricted.UNSAFE
.context connection = true;
) runs in the context of the connected user, so you need to make sure you are aware what access that library has to your data.