CLR Stored Procedure Cannot Be Marked as System Object?

It is an open secret that in SQL Server, we can “create a system object” by using an undocumented stored procedure sp_MS_marksystemobject as explained here.

The benefit is that when you put a SP in the [master] database and mark it as system object, this SP can then be accessed in any other databases without using three-part naming convention.

I recently tried to create a CLR stored procedure and put it into [master] and mark it as system object, but I find this is NOT doable, while on the other hand, a pure T-SQL stored procedure has no problem being marked as a system object.

Here are the two simple examples just for proof purpose , 1st is a pure T-SQL.

use master
drop proc dbo.sp_Test;
go
create proc dbo.sp_Test --(@db sysname) 
as
begin
	print 'Hello World'
end
go
exec sp_ms_marksystemobject 'dbo.sp_Test'
go

and the 2nd is C# code for CLR stored procedure

using System;
using Microsoft.SqlServer.Server;

public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure(Name = "sp_test2")]
    public static void sp_test2()
    {
        SqlContext.Pipe.Send("Hello World" + Environment.NewLine);
    }
}

After deploy to [master] database as [dbo].[sp_test2], and run

use master
exec sp_MS_marksystemobject 'dbo.sp_test2';
go

Now if I run the following code in [TempDB], everything is fine though sp_test is actually in the [master] database not [TempDB]

image

However, if I run sp_test2, there will be an error

image

But if I use three-part name convention to run sp_test2 as shown below, everything will be fine just as expected

image

If I check the property of the two SPs

use master
select name, type, type_desc, is_ms_shipped 
from sys.procedures
where name in ('sp_test', 'sp_test2')

select [sp_test]=OBJECTPROPERTYEX(object_id('sp_test'), 'ismsshipped'), 
[sp_test2]=OBJECTPROPERTYEX(object_id('sp_test2'), 'ismsshipped')

I will get the following result:

image

This is an interesting finding, and I think I’d better blog it here for my own reference.

This has been tested in SQL Server 2016 Developer Edition.

Advertisements
This entry was posted in SQL Development and tagged . Bookmark the permalink.

One Response to CLR Stored Procedure Cannot Be Marked as System Object?

  1. @DataSic says:

    I think you can use synonym. If logic depends on db then pass db name as required parameter. Delayed validation/loose coupling can be ticking bomb. Have fun!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s