Solved

Maintenance plan to run a script for multiple databases

Posted on 2011-02-21
8
869 Views
Last Modified: 2012-06-27
Hi All, I need to run a script on many servers (Exec USP_XYZ) which will be doing a bunch of delete and copy commands in several tables.

The wizard which has the option to select multiple databases DOES NOT have the option to run scripts. Only backup, shrink, etc...

I do not wish to create a job for every database, is it possible to create a plan that will be running for each database without using the wizard? or even using the wizard, does anyone know how to run a "EXEC usp_proc" on mulitple databases?

TIA
0
Comment
Question by:sir-rob
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 34948162
Kindly give inputs on the below questions:

1. Can you kindly confirm whether it is on Multiple databases in a single server or databases in multiple servers.
2. what is your SQL Server version and edition.

Without the above inputs, anyhow you can perform it via SQLCMD if it is in databases across multiple servers as given below:

http://www.sqlservercentral.com/articles/SQLCMD/66183/
0
 

Author Comment

by:sir-rob
ID: 34953070
rrjegan17:

1. Can you kindly confirm whether it is on Multiple databases in a single server or databases in multiple servers.
One server, but lots of databases in the server... I don't want to create one job per database.

2. what is your SQL Server version and edition.
2005 - And all I want is how to create this plan so I can run a "EXEC usp_abc" proc...

Thanks!

0
Why You Need a DevOps Toolchain

IT needs to deliver services with more agility and velocity. IT must roll out application features and innovations faster to keep up with customer demands, which is where a DevOps toolchain steps in. View the infographic to see why you need a DevOps toolchain.

 
LVL 57

Accepted Solution

by:
Raja Jegan R earned 500 total points
ID: 34957369
>> One server, but lots of databases in the server.

Ok, in that case use Execute T-SQL Task and provide your Procedure in the way it is:

USE ur_db1
GO
EXEC usp_abc

USE ur_db2
GO
EXEC usp_abc

and so on..
0
 

Author Comment

by:sir-rob
ID: 34974615
I did try USE... and it works but again, there are way too many databases....

What do you think about a Cursor?

Thanks!
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 34976098
>> it works but again, there are way too many databases....

Do you want to run it on all databases in your server, then you can use

EXEC sp_MSforeachdb 'USE ? ; exec usp_abc'

If you want to exclude few databases like system databases, then you can use the code below:

EXEC sp_MSforeachdb 'USE ? ; if ( DB_NAME() NOT IN(''master'', ''model'', ''msdb'', ''tempdb'')) begin exec usp_abc end'
0
 

Author Comment

by:sir-rob
ID: 35059810
rrjegan17: what if there is a "-" in the name (dash), is there a way around it?

Btw, I am giving you the points, just wanted to see if you know of a solution (since it fails and will not finish)...

Thanks
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 35063599
Having - in a database is not a recommended coding practise..
Anyhow, the below code would work, you can see there is a db named test_db-1 in the list for which I tested

EXEC sp_MSforeachdb 'USE [?] ; if ( DB_NAME() NOT IN(''master'', ''model'', ''msdb'', ''tempdb'', ''test_db-1'')) begin exec sp_helpdb end'
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

729 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question