CLR Stored Procedures as system objects

Question

Hi there,

Is is possible (and how) to invoke CLR Stored procedures living in master from other databases, such that the sproc is executed from with in that database's context?

This is simple in TSQL procs, marking the proc name as 'sp_' and executing the 'sys.sp_MS_marksystemobject'. 

This process seems to fail when I deploy my CLR proc. 

Any insight?

Answer 1

CREATE PROCEDURE [dbo].[sa_UpgradeSql2005]
	@serverName [nvarchar](4000),
	@databaseName [nvarchar](4000),
	@isFullUpgrade [bit]
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [UpgradeSql].[UpgradeSql.StoredProcedures].[UpgradeProcedures]
GO

This is a short example. After deploying your procedure you should run this script. This script mark your procedure as system object. You can check the result with objectpropertyex function.

exec sys.sp_MS_marksystemobject 'sa_UpgradeSql2005'

select * from sys.all_objects where name like 'sa_UpgradeSql2005'

select OBJECTPROPERTYEX(object_id('sa_UpgradeSql2005'),'IsMSShipped')

---

István Sáfár

Answer 2

Thank you for the reply Istvan.

I've tried it, but I still get the 'Msg 2812, Level 16, State 62, Line 1 Could not find stored procedure 'sp_Test'.'

So this is what I did:

In master, I deployed my assembly, hooked up a procedure name to it (using your code), marked the procedure as a system object and verified that it works from the context of master. (By the way, my test proc actually just returns the result of select db_name() so that I can verify where the program is run.)

I then executed the same proc from another database, which gives me the error stated above.

Perhaps I'm not understanding exactly what a system object is, and I'm barking up the wrong tree. 

My problem is this: I have 100's of databases, all with the exact same schema. Instead of rolling out updates to the database related code to each database, I want to have 1 central program that can be referenced via all my databases, making maintenance easier of course.

Regards,

Marcel

Answer 3

I think this is not the good way for maintain your storedprocedures. First of all sp_ms_marksystemobject is an undocumented,unsupported stored procedure, that is why I don't recommend to use it.

If you dont want to deploy your assembly to your all databasese, I think there is it a better way.
For example you can create a database that is hold your assembly and wrapper procedures. At the wrapper procedures you should add a parameter where you can specify your database name.


So wrapper procedures looke like as my sa_upgradesqk2005 storedprocedure and you can use it your CLR project (Anyway this is a database upgrader assembly. I wrote 2 years ago)

I hope this help you, if not I can send examples

---

István Sáfár

Answer 4

Ok, the centralized "procedures" database sounds like an idea, however, I am totally unfimiliar with this approach. Can you send some samples? I am specifically interested in 3 aspects:

1. How should I use the passed in database parameter? Do I use it in the connection string when newing up a SqlConnection?
2. How do I grant execute access for my "data" dbs to my "procedures" db?
3. How would I hookup CLR triggers in such a configuration, where the trigger is in a "data" db, but the CLR object is in a "procedures" db?

Thank you in advance.
Marcel