Specifying Schema for Stored Procs in CLR Project

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

.NET Inside SQL Server (2005)

Specifying Schema for Stored Procs in CLR Project

Author

Topic

kchoi
Starting Member

5 Posts

Posted - 2007-11-05 : 13:24:50

Hello all

I've been doing searches over the web and in these forums for a way to specify the schema information when deploying a SQL CLR assembly.

When I deploy my SQL CLR assembly, is there any way I can specify a specific schema in which the stored procedures will be created?

Any help appreciated, thanks!

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-11-05 : 13:31:32

you specify the schema in t-sql when creating sql objects from clr

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

kchoi
Starting Member

5 Posts

Posted - 2007-11-05 : 13:37:24

Hi spirit1, I'm not sure what you mean.
I have a SQL CLR project in my VS2005 IDE, with one stored procedure in it named "wp_Clients_Import".

When I build the project and "deploy" it using the IDE, it automatically creates a stored procedure for me that is:
"[XXX\kchoi].wp_Clients_Import"

I want the stored procedure to be created under "dbo"
Is there any way to specify "dbo" schema in VS2005?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-11-05 : 13:38:39

http://weblogs.sqlteam.com/mladenp/archive/2006/12/16/52754.aspx

you can se here what i mean.
look at the T-SQL code at the bottom of the post.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

kchoi
Starting Member

5 Posts

Posted - 2007-11-05 : 13:42:32

I see. This is deploying CLR manually and not through the VS2005 IDE then.

I'm having problems manually creating the assembly in SQL Management Studio. Can't find the dll for some reason, that is why I'm doing it through the IDE.

I will try this. Do you know of any way to specify the schema when deploying through VS2005?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-11-05 : 13:48:35

no.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

kchoi
Starting Member

5 Posts

Posted - 2007-11-05 : 13:52:09

thanks

kchoi
Starting Member

5 Posts

Posted - 2007-11-05 : 15:35:08

spirit1, thanks for the help, but now I'm getting a runtime error if I create the stored procedure through SQL Server Management Studio.

The error is:

Error 1 The assembly module 'wp_Clients_Import' cannot be re-deployed because it was created outside of Visual Studio. Drop the module from the database before deploying the assembly. SQLCLR

Which I'm guessing means 1 of 2 things:
1) that since I created it in SSMS, it wants to recreate the procedure when it recompiles my SQLCLR project
or
2) that since I created it in SSMS and something is different with the way I created it, it can't recreate the same named stored procedure and therefor is a problem

If I drop the procedure from the database that I created manually, VS2005 then recreates the stored procedure, but it is under the schema [XXX\kchoi], which I do not want. Do you have any advice for this problem?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources