Solved

Maintenance plan to run a script for multiple databases

Posted on 2011-02-21
8
865 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
 
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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
OPENDATASOURCE 8 36
Insert statement is inserting duplicate records 15 58
format nvarchar field as mm/dd/yyyy 4 62
Getting max record but maybe not use Group BY 2 19
I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…

863 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now