script CLR stored procedure for a different database

Question

script CLR stored procedure for a different database

oyen_kai

SSC Enthusiast

Points: 167
More actions
November 10, 2008 at 9:23 pm

#215095

I have devDatabase and prodDatabase. prodDatabase is an out-dated version of devDatabase. I want to modify my stored procedures in prodDatabase based on the CLR stored procedures in devDatabase. Is this possible? I want to be able to do this through an update script.
Thanks in advance for the help! 😀

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

RBarryYoung SSC Guru Points: 143329 More actions · Answer 1

Why wouldn't you just copy the assemblies over and reload them?

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

oyen_kai SSC Enthusiast Points: 167 More actions · Answer 2

rbarryyoung (11/10/2008)
Why wouldn't you just copy the assemblies over and reload them?

Like DROP-CREATE the assembly? Doesn't that mean I have to drop the stored procedures as well? I tried that with a test Assembly and a test Stored Procedure: I dropped the depended stored procedures then ran the DROP-CREATE assembly against the other server but that didn't recreate my CLR stored procedures.

krayknot SSCertifiable Points: 7998 More actions · Answer 3

oyen_kai (11/10/2008)
I have devDatabase and prodDatabase. prodDatabase is an out-dated version of devDatabase. I want to modify my stored procedures in prodDatabase based on the CLR stored procedures in devDatabase. Is this possible? I want to be able to do this through an update script.
Thanks in advance for the help! 😀

can you do the following

1. take the backup of prod database and restore it as a test.

2. do whatever you want on the test dB.

3. if the things goes in right fashion, in the off hours again take the backup of the prod database and replase what you want

kshitij kumar
kshitij@krayknot.com
www.krayknot.com

RBarryYoung SSC Guru Points: 143329 More actions · Answer 4

oyen_kai (11/10/2008)
Like DROP-CREATE the assembly? Doesn't that mean I have to drop the stored procedures as well? I tried that with a test Assembly and a test Stored Procedure: I dropped the depended stored procedures then ran the DROP-CREATE assembly against the other server but that didn't recreate my CLR stored procedures.

This does not makes sense to me. How did you create the CLR procs on the other server the first time?

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

oyen_kai SSC Enthusiast Points: 167 More actions · Answer 5

can you do the following
1. take the backup of prod database and restore it as a test.
2. do whatever you want on the test dB.
3. if the things goes in right fashion, in the off hours again take the backup of the prod database and replase what you want

That's what I wanted. Actually the EASIEST way to do is to create a connection to the prodDatabase and compile my stored procedures there. However, I need some versioning thing so that I'll determine what version the prod is in, etc. And I'm kind of expecting that I might not be the one running the update scripts in the future...

Actually I even a "PURGE script" 😀 where I get the "clean" data back into the database.

I'd really like to know how to alter CLR stored procedures through scripts. Even though I don't use it in the current project, I think it'll be really handy in the future.

oyen_kai SSC Enthusiast Points: 167 More actions · Answer 6

rbarryyoung (11/10/2008)
oyen_kai (11/10/2008)
Like DROP-CREATE the assembly? Doesn't that mean I have to drop the stored procedures as well? I tried that with a test Assembly and a test Stored Procedure: I dropped the depended stored procedures then ran the DROP-CREATE assembly against the other server but that didn't recreate my CLR stored procedures.
This does not makes sense to me. How did you create the CLR procs on the other server the first time?

I have Database project in Visual Studio where I write and deploy the CLR Stored Procedures 🙂

Kishore.P SSCrazy Eights Points: 8147 More actions · Answer 7

make a connection to database in your visual studio database project and do what ever modifications you do and then deploy to database.

oyen_kai SSC Enthusiast Points: 167 More actions · Answer 8

That's exactly what I said I CAN do but am trying to figure out if it's possible to SCRIPT it so that I can just run an update script.

Kishore.P SSCrazy Eights Points: 8147 More actions · Answer 9

oyen_kai (11/11/2008)
That's exactly what I said I CAN do but am trying to figure out if it's possible to SCRIPT it so that I can just run an update script.

Script means, you can generate script for sp's.

Note: no direct software to create CLR Procedures from existing SP.

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 10

Kishore.P (11/11/2008)
oyen_kai (11/11/2008)
That's exactly what I said I CAN do but am trying to figure out if it's possible to SCRIPT it so that I can just run an update script.
Script means, you can generate script for sp's.
Note: no direct software to create CLR Procedures from existing SP.

Niels Berglund wrote a Addin the SSMS that allows you to script CLR Assemblies for altering between systems.

http://nielsb.wordpress.com/sqlclrproject/sqlclrdl/

You can do this entirely with SSMS by Scripting the Create Assembly to a new SSMS window and changing the word CREATE to ALTER. Then you script the ALTER Statements for each of your dependent objects and run them after you ALTER the Assembly on the server.

I wrote up how to do this with a UDT on the following link:

http://www.sqlclr.net/Articles/tabid/54/articleType/ArticleView/articleId/27/Default.aspx

RBarryYoung SSC Guru Points: 143329 More actions · Answer 11

oyen_kai (11/10/2008)
rbarryyoung (11/10/2008)
This does not makes sense to me. How did you create the CLR procs on the other server the first time?
I have Database project in Visual Studio where I write and deploy the CLR Stored Procedures 🙂

Ah, OK. I had forgotten about that, good question then.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

oyen_kai SSC Enthusiast Points: 167 More actions · Answer 12

Jonathan Kehayias (11/11/2008)
Kishore.P (11/11/2008)
oyen_kai (11/11/2008)
That's exactly what I said I CAN do but am trying to figure out if it's possible to SCRIPT it so that I can just run an update script.
Script means, you can generate script for sp's.
Note: no direct software to create CLR Procedures from existing SP.
Niels Berglund wrote a Addin the SSMS that allows you to script CLR Assemblies for altering between systems.
http://nielsb.wordpress.com/sqlclrproject/sqlclrdl/
You can do this entirely with SSMS by Scripting the Create Assembly to a new SSMS window and changing the word CREATE to ALTER. Then you script the ALTER Statements for each of your dependent objects and run them after you ALTER the Assembly on the server.
I wrote up how to do this with a UDT on the following link:
http://www.sqlclr.net/Articles/tabid/54/articleType/ArticleView/articleId/27/Default.aspx

sounds like what I'm looking for. Thank you! I'll try it out and let you know how it works.

oyen_kai SSC Enthusiast Points: 167 More actions · Answer 13

Jonathan Kehayias (11/11/2008)
Kishore.P (11/11/2008)
oyen_kai (11/11/2008)
That's exactly what I said I CAN do but am trying to figure out if it's possible to SCRIPT it so that I can just run an update script.
Script means, you can generate script for sp's.
Note: no direct software to create CLR Procedures from existing SP.
Niels Berglund wrote a Addin the SSMS that allows you to script CLR Assemblies for altering between systems.
http://nielsb.wordpress.com/sqlclrproject/sqlclrdl/
You can do this entirely with SSMS by Scripting the Create Assembly to a new SSMS window and changing the word CREATE to ALTER. Then you script the ALTER Statements for each of your dependent objects and run them after you ALTER the Assembly on the server.
I wrote up how to do this with a UDT on the following link:
http://www.sqlclr.net/Articles/tabid/54/articleType/ArticleView/articleId/27/Default.aspx

As I understand it, the article that you wrote handles altering an assembly. What if that assembly also deploys stored procedures? On the article you created a new function based on the assembly, but what I want to do is modify existing stored procedures that were spawned by the same assembly. (I'm not really sure if the assembly deploys CLR stored procedures...that's just how I understand it o.o)

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 14

If you alter the assembly, and the stored procedure's parameter and outputs have not changed, then the procedure is altered by altering the assembly. If you added to or removed from the procedures input/output parameter list, then all you have to do is script the alter procedure to a window, and then add the parameters to it and run the code. Or you can script the alter from your dev server and rerun it on production. It is really that simple.