Sisupoika
asked on
CLR Stored Procedures: how to set the schema/owner?
Hi there,
I am working on a Linq based CLR Stored Procedure for some complex filtering and manipulation, which would otherwise require a lot of messy and poorly performant T-SQL code, if implemented in a more "traditional" Stored Procedure.
This is working great, but I can't find how to set the schema of this Stored Procedure in phase of deployment, for a better organization and separation of the database objects in modules.
Any ideas?
Many thanks in advance.
I am working on a Linq based CLR Stored Procedure for some complex filtering and manipulation, which would otherwise require a lot of messy and poorly performant T-SQL code, if implemented in a more "traditional" Stored Procedure.
This is working great, but I can't find how to set the schema of this Stored Procedure in phase of deployment, for a better organization and separation of the database objects in modules.
Any ideas?
Many thanks in advance.
BrandonGalderisi
I've never attempted, but have you tried the "create procedure SCHEMANAME.PROCEDURENAME" syntax?
ASKER
Thanks for replying, but I am afraid you have misunderstood the question :)
It's about CLR stored procedures, not normal ones.
I usually use schemas with normal SPs with no problems, but I don't know how to do the same with CLR SPs.
It's about CLR stored procedures, not normal ones.
I usually use schemas with normal SPs with no problems, but I don't know how to do the same with CLR SPs.
I'm not confused.
create procedure SCHEMANAME.PROCEDURENAME
as
external name blah.blah.blah
GO
ASKER
I am sorry, perhaps I am the confused one and have misunderstood your suggestion :)
Using your method gives me the possibility to call the stored procedure with the schema+procedure_name that I want, but wouldn't it still show two items instead of one?
Many thanks for your help.
Using your method gives me the possibility to call the stored procedure with the schema+procedure_name that I want, but wouldn't it still show two items instead of one?
Many thanks for your help.
If you have already created your CLR proc in a different schema, you would have to drop it and update all references to the one created in the desired schema.
Using your method gives me the possibility to call the stored procedure with the schema+procedure_name that I want,
No, that is a CREATE procedure statement.
No, that is a CREATE procedure statement.
ASKER
Just to make sure I am following :)
So, I first deploy the CLR stored procedures from within Visual Studio, then I can drop the ones it creates with the dbo schema, and create new references with whichever schema name I want, by using the command you showed me. Is this what you are saying? :)
If yes, what shall I specify as external name?
Say the CLR stored procedure VS creates with the deployment has name 'GetSurveyQuestions', with schema 'dbo'. That is, dbo.GetSurveyQuestions.
Say I organize the database objects in 'modules' by using different schemas, and I want the 'GetSurveyQuestions' sp to appear as 'SurveyModule.GetSurveyQue
How shall I actually proceed?
What do I put as external name in
create procedure SCHEMANAME.PROCEDURENAME
as
external name blah.blah.blah
GO
?
Many thanks for all your help, the points are already yours but I haven't got it 100% :D
So, I first deploy the CLR stored procedures from within Visual Studio, then I can drop the ones it creates with the dbo schema, and create new references with whichever schema name I want, by using the command you showed me. Is this what you are saying? :)
If yes, what shall I specify as external name?
Say the CLR stored procedure VS creates with the deployment has name 'GetSurveyQuestions', with schema 'dbo'. That is, dbo.GetSurveyQuestions.
Say I organize the database objects in 'modules' by using different schemas, and I want the 'GetSurveyQuestions' sp to appear as 'SurveyModule.GetSurveyQue
How shall I actually proceed?
What do I put as external name in
create procedure SCHEMANAME.PROCEDURENAME
as
external name blah.blah.blah
GO
?
Many thanks for all your help, the points are already yours but I haven't got it 100% :D
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Sorry for the delay. I am not deploying anymore from within Visual Studio in favour of scripts where I can use the method you suggested, with better control.
Many thanks for your help.
Many thanks for your help.