I am using Stored Procedures with an SQL Server database. Periodically I have to modify these stored procedures and this requires a formal release of software.

At the moment these stored procedures are being updated manually (by systems bods) via the Enterprise Manager for these releases on the production system.

Is there a better way of updating these SP's? I would rather take out the human error element of updating them - a typo would be far too easy. Also, fallback procedures would be performed by the same numpty who'd made a typo in the first place. There must be a better way, but these SP's aren't stored as a file on the hard drive and I can't think of a better way of updating them.

Anyone got any better ideas?

Hi jerome

Have you used Scripting. ( select SP's , drop and create - DO NOT select dependant objects as this will drop and recreate tables, views , etc )

You generate a script ( stored wherever ) and then modify the script.

Then open script in query analyser and run it.

Drop me an E if you require any further help.

Scripting is the way the dba's here do it. Saving the scripts also makes re-creating the server easier if you have a disaster or need to set up a test server.

Certainly the way us 'Between Contracts' dba types do it.

yup - scripting is the way to do it. Updating them manually is a bit risky what with human errrror and all...

Dave