Advice needed from the SQL gurus - Run alter procedure script on multiple DBs

P

PsYTraNc3

Expert Member
Joined
Jan 4, 2012
Messages
3,718
Reaction score
790
Location
Scotland
So... I've got a script to alter a procedure which is stored on multiple databases and was wondering what the best way would be to run the script on databases where name like 'xxx%' ?

Script (Or part of it) below:
View attachment spGetEngineDataPerioden med PersonSats temptabell.txt

Is there any way for me to convert this script into a stored procedure? I can then easily run a script to loop through the databases i need.
 
There are many ways of doing it :

If it's only for certain DBs then use a CTE with some sort ID and your DB names as columns and then just create a query for each DB from the rows, if it's for all DBs use sp_MSForEachDB
 
PsYTraNc3 said:
So... I've got a script to alter a procedure which is stored on multiple databases and was wondering what the best way would be to run the script on databases where name like 'xxx%' ?

Script (Or part of it) below:
View attachment 301700

Is there any way for me to convert this script into a stored procedure? I can then easily run a script to loop through the databases i need.
Click to expand...

Cursur with all the DB's you need to change. Use Id or identifier from master.sysdatabases.

If the DB's are on different servers, same process albeit you will need linked servers.
 
prOd said:
There are many ways of doing it :

If it's only for certain DBs then use a CTE with some sort ID and your DB names as columns and then just create a query for each DB from the rows, if it's for all DBs use sp_MSForEachDB
Click to expand...

It's for certain DB's and the query that needs to be run on each one is about 1000 lines long :erm: so it won't be that simple.

I ended up getting our in house developer and CTO to look at it and they luckily developed an application a few years ago for a problem similar to this. :D

I can then add a csv with database names and the script which has to be run on them then the app will generate scripts which works like this:

USE "Column1, line 1 from csv"
GO

"Data from script used as input file:
End

Then runs the query and does the same for the next line in csv :)
 
Pho3nix said:
Cursur with all the DB's you need to change. Use Id or identifier from master.sysdatabases.

If the DB's are on different servers, same process albeit you will need linked servers.
Click to expand...

+1

that is the way I would have approached solving this as well
 
Pho3nix said:
Cursur with all the DB's you need to change. Use Id or identifier from master.sysdatabases.

If the DB's are on different servers, same process albeit you will need linked servers.
Click to expand...

Developers still use cursors? :wtf:
 
Pho3nix said:
:o

It does the job sometimes. While loop or others could be used. Depends on your comfort level
Click to expand...

At a previous job of mine if a developer wanted to use a cursor in his project he had to schedule a meeting with the dev manager and systems architect and explain why he needed one. It scared the bejezus out of all the devs ;)
 
prOd said:
At a previous job of mine if a developer wanted to use a cursor in his project he had to schedule a meeting with the dev manager and systems architect and explain why he needed one. I like that rule ;)
Click to expand...

Lol funny enough, it's a bit of a wild west here. As long as scripts align to specific speed (running time) as well as minimal impact to the servers, you are ready to go.

Longest running script here, is 27 minutes long. Just select statement, dev in question is avoiding fixing the c0ck up.
 
Cursor seems to be the obvious and easiest option but have any of you actually looked into what I would be trying to run with the cursor?

That makes it seem almost impossible unless there is a way to add the script as an input file to loop through DBs.
 
Pho3nix said:
Lol funny enough, it's a bit of a wild west here. As long as scripts align to specific speed (running time) as well as minimal impact to the servers, you are ready to go.

Longest running script here, is 27 minutes long. Just select statement, dev in question is avoiding fixing the c0ck up.
Click to expand...

I have a cursor i run to rename users in various tables of sometimes over 1000 DBs.
Not long ago over a weekend, it ran for almost 2 hours :erm:
 
PsYTraNc3 said:
Cursor seems to be the obvious and easiest option but have any of you actually looked into what I would be trying to run with the cursor?

That makes it seem almost impossible unless there is a way to add the script as an input file to loop through DBs.
Click to expand...

I've looked at the example script you put above and while your code would be long and mind-numbing, the cursor would still work fine (within reason).
I've run something similar a couple months ago out of necessity.

Am I missing something here?
 
Pho3nix said:
I've looked at the example script you put above and while your code would be long and mind-numbing, the cursor would still work fine (within reason).
I've run something similar a couple months ago out of necessity.

Am I missing something here?
Click to expand...

Not much besides that being only a few lines of the code I need to run and that I have already tried it in a cursor that I use for a lot of other things on my test server.
Because there are multiple Alter database, Selects, joins, updates etc, the amount of errors and incorrect syntax that was returned just made my non dev self pull the brakes and ask for help :D
 
PsYTraNc3 said:
Not much besides that being only a few lines of the code I need to run and that I have already tried it in a cursor that I use for a lot of other things on my test server.
Because there are multiple Alter database, Selects, joins, updates etc, the amount of errors and incorrect syntax that was returned just made my non dev self pull the brakes and ask for help :D
Click to expand...

:D Fair enough.

Demoing SSDT next week and have to fix a little over 200 syntax errors before the DB project will build. I can feel your pain.

At least you have a way to get your stuff sorted :)
 
Pho3nix said:
Yeah, you'd be fired here :D
Why would you be renaming users?
Click to expand...

I handle the Visma CRM package which is like Pastels big brother used mostly on Scandinavia.

When on-borading new customers (Mostly massive accounting firms) I have to import all their data to our format, rename users in the apps and DBs to the AD users we set up for them etc.

2 weeks ago we added a customer with around 150GB worth of SQL data, 100 users, 15 applications that are being run, email, file data, web sites, printers etc all to work in our cloud solution.
 
PsYTraNc3 said:
I handle the Visma CRM package which is like Pastels big brother used mostly on Scandinavia.

When on-borading new customers (Mostly massive accounting firms) I have to import all their data to our format, rename users in the apps and DBs to the AD users we set up for them etc.

2 weeks ago we added a customer with around 150GB worth of SQL data, 100 users, 15 applications that are being run, email, file data, web sites, printers etc all to work in our cloud solution.
Click to expand...

Woah :wtf: how big is your team.
Sorry for detailing your thread
 
Pho3nix said:
Woah :wtf: how big is your team.
Sorry for detailing your thread
Click to expand...

haha. no problemo.
For jobs like that, usually around 6 of us working.

Project leader - handles communication, meetings, overview of what is needed.
Network admin - sets up and distributes a Juniper firewall\VPN and assists with re-configuring network settings of all devices on site. (also does printer setup)
Sys admin 1 - File and email.
sys admin 2 - SQL, app server, finance app setup.
sys admin 3 - app setup.
sys admin 4 (Senior) - testing mostly.

After implementation and resolving teething problems, we hand over to customer center.
 
You must log in or register to reply here.
Back
Top
Sign up to the MyBroadband newsletter
X