Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

CLR Stored Procedures: how to set the schema/owner?

Posted on 2009-05-13
9
Medium Priority
?
568 Views
Last Modified: 2012-05-06
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.
0
Comment
Question by:Sisupoika
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 24380857
I've never attempted, but have you tried the "create procedure SCHEMANAME.PROCEDURENAME" syntax?
0
 

Author Comment

by:Sisupoika
ID: 24380970
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.
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 24388454
I'm not confused.


create procedure SCHEMANAME.PROCEDURENAME
as
external name blah.blah.blah
GO

Open in new window

0
Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

 

Author Comment

by:Sisupoika
ID: 24389692
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.
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 24391568
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.
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 24391572
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.
0
 

Author Comment

by:Sisupoika
ID: 24393565
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.GetSurveyQuestions' rather than 'dbo.GetSurveyQuestions'.
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
0
 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 2000 total points
ID: 24396576
"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? :"

Yes.  Or just don't deploy from VS first.

The external name is the namespace path to the procedure in your code.  I'm sure there is an option somewhere in VS to specify the schema you want to deploy to.
0
 

Author Closing Comment

by:Sisupoika
ID: 31580898
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.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question