March 13, 2014 at 1:04 pm
I can easily query multiple servers using the multi-server query function in Central Management Server and write some of the results to logging tables. I would like to be able to do this via a scheduled job. So far I am finding that even setting up Master/Target Servers this may not work and the only workaround is either using SSIS, SQLCMD (by basically hard coding the servername) and possibly Powershell.
Can anyone tell me if they have been successful just using standard jobs and querying against multiple servers?
If I can't save the results to a 'central' database/table (I can do this when in SSMS), but can still query against multiple servers I was thinking I could write the results to a CSV file that a SSIS job picks up. Anyone see any real flaws?
I have attempted using SSIS to iterate through servers and have been plagued with intermittent connection issues when using a For...Loop container.
March 19, 2014 at 3:31 am
As far as I am aware CMS only allows you to query/register servers with windows authentication. however, have you tried linked server.
alternatively, I have used SSIS to create a global temp table (##table) that way you can use it throughout the scope of SSIS and dump final results into a single database or CSV
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply