July 14, 2008 at 10:54 am
I have 3 enviroments (dev, test, prod) we creates stored procedures to generate reports using Crystal. Most of the times the same SP has to be created in all enviroments and for all Db's (100)
how can I create the same SP to all db's with just one script?
July 14, 2008 at 11:07 am
First of all, you shouldn't be creating the stored procedure in all three environments at the same time. It violates the SDLC (Software Development Life Cycle) process and could potentially cause you problems. Plus, if your company has to abide by SOX regulations, it's technically illegal to throw your Proc in Prod without extensive testing first. (Of course, the auditors have to catch up with you first... Or the lawsuits...)
Secondly, you can't do it without Linked Servers (at least), or without changing your connection properties every time you run the script.
July 14, 2008 at 11:14 am
But if I misunderstood your question and you're just trying to run it on all DBs in Dev (for instance) and then later move it to test...
You have a couple of options. My usual is a WHILE...DO loop. Populate a temp table with the names of all the datatabases from sys.databases where databaseID > 4 (the first 4 are system DBs) and include a bit column called DoneNotDone. Set your counter to the max # of databases and inside the WHILE loop, set dynamic SQL which does your USE statement.
Something like this...
Declare @MyDB varchar(30), @MyDBCnt int, @MySQL varchar(30)
Set @MyDBCnt = (Select Count(*) from #MyTemp)
While @MyDBCnt > 0
Begin
Set @MyDB = (Select Top 1 from #MyTemp where DoneNotDone = 0);
Set @MySQL = 'Use ' + @MyDB + ' GO';
Execute(@MySQL)
Create Procedure ....
< body of proc >
Update #MyTemp
Set DoneNotDone = 1
Where DBName = @MyDB;
Set @MyDBCnt = @MyDBCnt - 1;
End
July 14, 2008 at 11:15 am
But you still have to change the context to run the ALTER/CREATE/whatever script on each of the different environments.
That said, listen to Brandie. Fight the very idea of running it on all environments. You have three different one's for a reason. If you're just going to run it automagically on all three, why not skip the process and just develop straight on Prod. It's the same thing.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 14, 2008 at 12:57 pm
You are correct, this is only for Dev to test... then after intensive test we deploy to prod..
Let me try that script and I'll let you know..
Thanks
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply