SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Job to query multiple instances at once


Job to query multiple instances at once

Author
Message
scogeb
scogeb
SSC-Enthusiastic
SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)

Group: General Forum Members
Points: 198 Visits: 420
Like most environments, we have a bunch of SQL servers. I would like to set up some jobs to query all of the instances for certain things and then email me the results. I have a Central Management Server setup and it works great and my queries get me what I need, but I can't schedule a job through the CMS, so it's still a manual process. Does anyone have something set up to do this? I thought about one instance with a ton of linked servers but I'm not sure if that's the best direction to go. Just wondering if anyone has a better solution.
lptech
lptech
SSC-Addicted
SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)SSC-Addicted (483 reputation)

Group: General Forum Members
Points: 483 Visits: 3365
You could use an SSIS package, as described here:

http://www.sqlservercentral.com/blogs/sqlstudies/2012/10/31/looping-through-multiple-servers-in-ssis/
Chris Harshman
Chris Harshman
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5100 Visits: 4010
scogeb (12/7/2012)
...I would like to set up some jobs to query all of the instances for certain things and then email me the results...

I don't know how many instances you have, but I've setup something for myself that collects some daily information about each instance and stores the data in one central database that only I use. The SQL Agent job I created reaches the other servers using SELECT ... FROM OPENROWSET () methodology so I don't need linked servers. You can create the commands from a table with a list of instances, and execute it as dynamic SQL with sp_executesql.
Hemant.R
Hemant.R
SSC-Enthusiastic
SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)

Group: General Forum Members
Points: 136 Visits: 240
hi,

you can use sql server administration wizard this is new feature

right click on sql server agent you will get multi server administration wizard,

select master server (where you can create job)

select target server (where job automatically replicate)

please find below link

http://msdn.microsoft.com/en-us/library/ms180992(v=sql.100).aspx

http://msdn.microsoft.com/en-us/library/ms191305.aspx
scogeb
scogeb
SSC-Enthusiastic
SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)

Group: General Forum Members
Points: 198 Visits: 420
Thanks guys! I've done some more research and Redgate's SQL Monitor might be what I'm looking for. I'm surprised no one recommended it though, so I'm a bit leary. I'll start a new thread on it.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search