For T-SQL stored procedures with the prefix sp_, SQL Server will search for and user a procedure in the master database before the current one. This does not appear to be the case with a SQLCLR stored procedure I created with SQL Server 2010 using Visual Studios built in deploy. The database server is SQL Server 2008 R2 (SP1).
I execute this script:
SELECT name
from master.sys.procedures
WHERE type_desc='CLR_STORED_PROCEDURE' and schema_id = 1
USE tempdb
PRINT 'USING tempDb'
EXEC sp_RAISERROR_CaughtDemo;
GO
USE master
PRINT 'USING master'
EXEC sp_RAISERROR_CaughtDemo
GO
--SELECT * from sys.assembly_modules
And the stored procedure will only execute with an unqualified name from the master database:
name
----------------------------------
sp_RAISERROR_CaughtDemo
sp_RAISERROR_UncaughtDemo
(2 row(s) affected)
USING tempDb
Msg 2812, Level 16, State 62, Line 5
Could not find stored procedure 'sp_RAISERROR_CaughtDemo'.
USING master
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
The code for those procedures is as follows:
[SqlProcedure(Name = "sp_RAISERROR_UncaughtDemo")]
public static void RaiserrorUncaught()
{
short i = 0;
using (var cn = new SqlConnection("context connection=true"))
using (var cmd = cn.CreateCommand())
{
cn.Open();
cmd.CommandText = "RAISERROR('RAISERROR() Uncaught Severity %d', @i, 1, @i)";
cmd.Parameters.Add("@i", SqlDbType.SmallInt);
while (true)
{
cmd.Parameters["@i"].Value = i++;
SqlContext.Pipe.ExecuteAndSend(cmd);
}
cn.Close();
}
}
[SqlProcedure(Name = "sp_RAISERROR_CaughtDemo")]
public static void RaiserrorCaught()
{
try
{
short i = 0;
using (var cn = new SqlConnection("context connection=true"))
using (var cmd = cn.CreateCommand())
{
cn.Open();
cmd.CommandText = "RAISERROR('RAISERROR() Caught Severity %d', @i, 1, @i)";
cmd.Parameters.Add("@i", SqlDbType.SmallInt);
while (true)
{
cmd.Parameters["@i"].Value = i++;
SqlContext.Pipe.ExecuteAndSend(cmd);
}
cn.Close();
}
}
catch(SqlException) {}
}
Is there anything I can do to make SQL Server search in the master database to find this stored procedure? I tried sys.sp_MS_marksystemobject
to no avail.
Best Answer
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):
Then I created a wrapper stored procedure, making sure to fully qualify the CLR procedure:
Then I adjusted your repro code to call the wrapper from tempdb instead:
Results:
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:
To:
But of course this change had nothing to do with the scoping issue.