SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

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;
create proc dbo.sp_Test --(@db sysname) 
	print 'Hello World'
exec sp_ms_marksystemobject 'dbo.sp_Test'

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';

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


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


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


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:


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.


Leave a comment on the original post [dbaphilosophy.wordpress.com, opens in a new window]

Loading comments...