I don't know a solution (and I don't know if the CLR scaffolding was ever designed to mimic the functionality you're talking about), but one workaround could be to create a T-SQL stored procedure in master that serves as a wrapper to relay the call to the CLR version. Marking it as a system object shouldn't be necessary, as long as a stored procedure with the same name doesn't exist in the user database.
EDIT - just proved that this works fine, as much for myself as anything. First I created your assembly and added the procedure necessary to repro (I'm not going to include all the bits here for brevity):
USE master;
GO
CREATE ASSEMBLY [Justin]
AUTHORIZATION [dbo]
FROM 0x4D5A900003000000040... lots of data here ...;
GO
ALTER ASSEMBLY [Justin]
DROP FILE ALL
ADD FILE FROM 0x4D6963726F... even more data here ...;
AS N'Justin.pdb';
GO
CREATE PROCEDURE [dbo].[sp_RAISERROR_CaughtDemo]
AS EXTERNAL NAME [Justin].[StoredProcedures].[RaiserrorCaught]
GO
Then I created a wrapper stored procedure, making sure to fully qualify the CLR procedure:
CREATE PROCEDURE dbo.sp_RAISERROR_CaughtDemo_Wrapper
AS
BEGIN
SET NOCOUNT ON;
EXEC master.dbo.sp_RAISERROR_CaughtDemo;
END
GO
Then I adjusted your repro code to call the wrapper from tempdb instead:
USE tempdb;
PRINT 'USING tempDb';
EXEC sp_RAISERROR_CaughtDemo_wrapper;
GO
Results:
USING tempDb
RAISERROR() Caught Severity 0
RAISERROR() Caught Severity 1
Msg 50000, Level 1, State 1
RAISERROR() Caught Severity 2
Msg 50000, Level 2, State 1
RAISERROR() Caught Severity 3
Msg 50000, Level 3, State 1
RAISERROR() Caught Severity 4
Msg 50000, Level 4, State 1
RAISERROR() Caught Severity 5
Msg 50000, Level 5, State 1
RAISERROR() Caught Severity 6
Msg 50000, Level 6, State 1
RAISERROR() Caught Severity 7
Msg 50000, Level 7, State 1
RAISERROR() Caught Severity 8
Msg 50000, Level 8, State 1
RAISERROR() Caught Severity 9
Msg 50000, Level 9, State 1
RAISERROR() Caught Severity 10
Msg 50000, Level 11, State 1, Line 1
RAISERROR() Caught Severity 11
So this shows that you should be able to use a wrapper like this to be able to call CLR procedures, unreferenced, from other databases.
(However, I will suggest that in general this shouldn't be a goal - you should be properly defining references with three-part names where applicable.)
I'll confess I did make one change to your stored procedure though, to prevent Visual Studio from whining at me. I changed:
while (true)
To:
while (i <= 11)
But of course this change had nothing to do with the scoping issue.
Best Answer
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.
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 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 aDATETIMEor maybe could use aROWVERSIONvalue and compare to@@DBTSeach time). If your table already has aDATETIMEorDATETIME2column being set uponINSERT, then you can leverage that existing column, else you will need to add aDATETIME/DATETIME2/ROWVERSIONcolumn. 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 usedID. 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
INSERTEDtable. 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.