Is there anyway to make sql server search the master database for SQLCLR stored procedures before the current one?

Question

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.

Answer 1

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.