moving a sproc from test environment 1

[NuJoizey]

is there a way to export a sproc from one server (my test environment) to another (the production environment) other than cutting/pasting the tsql into a new sproc on the remote server?

 

[nice95gle]

You should script it out as CREATE and save as a sql file. Then run that script on the new DB or server.

Remember you need to have an entry in your syscomments table for you SP to function. The only way I know of to get a entry into the table is by using the CREATE command in a SP.

But this is way I do it, there might be another way.

Well Done is better than well said
- Ben Franklin

 

[NuJoizey]

ok - that's good to know - thank you.

 

[nomadicalloy]

Do you use Visual Studio 2005? If you do then you can use following tool

http://www.microsoft.com/downloads/...ad-bd54-4eda-b08c-4df84df0d641&displaylang=en

My Programming Blog

http://www.xpherion.com/wp/index.php/category/programming/

 

[NuJoizey]

hmmm - interesting - yes i am using vs2005, but professional editoin, not team edition. I'm very afraid to start messing with my configuration, at least for now...

 

[bboffin]

There are many tools for comparing SQL databases and synchronizing them. All work by producing scripts to do the changes so they are really just automated versions of the process advised above BUT they will generally allow tables/views etc to be synchronized as well and figure out dependencies so that any updates are carried out in the correct order. For example if you have added a column to a table and then used that column in an SP it will update the table first before updating the SP.

I use SQL Examiner from TulaSoft (

http://www.sqlaccessories.com)

which allows you to save the generated scripts or to direct them to Query Analyzer or SQL Server Management Studio or to run them immediately. All scripts have the option to include taking a backup first.

TulaSoft also have a tool called SQL Data Examiner which will selectively synchronize the data between two databases. This is very useful where you have added new static data such as status values.

Note that these are commercial products.

I have no connection with TulaSoft other than as a satisfied customer.


Bob Boffin

 

[NuJoizey]

For example if you have added a column to a table and then used that column in an SP it will update the table first before updating the SP.

yeah, this is exactly what i am talking about. This exact thing just happened yesterday. Right now I do all these updates to my test environment, they get extensive with little minor detail changes, and I start to forget exactly what I did. Inevitably, when I think I uploaded all my changes to the production server, one or two things are forgotten (i.e. added a column that is called by a new sproc, but forgot to add the column to the production server) and the app blows up, and then either i'm making fixes like crazy trying to remember what I did, or I just have to remember to do everything twice!

- thanks for educating me on what it is you do to handle this.

Are you working solely, or in a team environment?

 

[SQLSister]

SQLCompare from Redgate is another tool that checks both environmments and then creates a script to make all the changes from one to the other. And the price is very reasonable.

It is best to use a tool like this when promoting changes because you might forget something. You may need to adjust any script to make sure things are done in the proper order. And I usually do table changes, then view changes, then function changes and finally sp changes. And sometimes you may need to exclude some changes that you know are not ready to go to production. It is also a good idea if possible to have a staging or QA environment which is just like prod to run the scripts on first. You can then test and fix any problems encountered (such as the order of when changes are made) before promoting the script to production.

"NOTHING is more important in a database than integrity." ESquared

 

[NuJoizey]

staging or QA environment which is just like prod to run the scripts on first

if the production app is located at

http://App1

and points to datastore ds1 on instance1 on hardware a, then would it be sufficient enough for the qa environment to be something like setting up

http://App2

in IIS, and pointing it towards datastore ds2 on instance1 on hardware a? - and then use one of the tools you mentioned? - or should it be something more? Let's assume a small company user environment of one developer and 50 employees.