Wednesday, March 28, 2012

How to make Stored Proc Changes

Hi,
Just moved to SQL2005 from SQL 2000. Thought I would drop some bad habits along the move. With SQL 2000, when I had stored proc changes to take to the client, I would open EnterpriseManager on my laptop while plugged into their network. I would open their database on their network, and the database on my laptop that had their changes, and cut and post the changes. Really want to stop that.

Now, with SQL2005, I see it wants me to modify the SP, then run it as a script.

What is the best way for me to take stored proc changes from my SQL2005 server in the office, and get them to the clients SQL2005 server?

Thanks

option 1. Script your procedures in the office with create and drop options

option 2. Edit the procedure and change the "Create Procedure" to "Alter Procedure" (Consistent since SQL 7, 6.5 ....)

Hope this helps

GDR

|||So for option 1., I would make the change in the office, then right click on the sp, choose
Script as > Alter to > File, and make a file of it. Or choose Create To if it is a new one.

Then what do I do when I get to the customers with my .sql files?

Thanks

|||..and, can I do the same thing with table changes?
|||You can either script the object one by one, or create a script right-clicking on the database and choosing script. After selecting the appropiate options which apply to you, you will get a Database script which can be run at the customer site to create a new database. if you just want to deploy changes to the customersite, you will have to only apply the procedures (which can be applied no matter if data is in the database or not) and the update scripts of the tables. YOu should not use the "Drop first" then "create" the table from scratch option, because data would be wiped out during the drop command. E.g. if you add another column in your database you would create an "update" script which only would apply the additional changes to the database like adding another column ALTER TABLE SomeTable Add SomeColumn VARCHAR(50).

Jens K. Suessmeyer.

http://www.sqlserver2005.de
sql

No comments:

Post a Comment