Solved

Maintenance plan to run a script for multiple databases

Posted on 2011-02-21
8
866 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
  • 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
 
LVL 13

Expert Comment

by:agarwalrahul
ID: 34948449
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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

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 we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.

770 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