?
Solved

Maintenance plan to run a script for multiple databases

Posted on 2011-02-21
8
Medium Priority
?
873 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 57

Accepted Solution

by:
Raja Jegan R earned 2000 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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…

762 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