CLR Stored Procedure Cannot Be Marked as System Object?

Jeffrey Yao, 2017-02-17

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.

Rate

Share

Share

Rate

Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

Robert Davis

2009-02-23

1,567 reads

Networking – Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I’d like to talk about social networking. We’ll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let…

Andy Warren

2009-02-17

1,530 reads

Speaking at Community Events – More Thoughts

Last week I posted Speaking at Community Events – Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I’ve got a few more thoughts on the topic this week, and I look forward to your comments.

Andy Warren

2009-02-13

360 reads