October 8, 2010 at 7:38 am
Hi Everyone - I have a 2008 Central Management Server that we're able to run queries from against all our SQL servers. I'm wondering if there's a way to do this via the command line or a job so the queries can be automated and rolled up into reporting services reports. Any suggestions?
October 11, 2010 at 7:25 am
Think you may have to do this using the master\target option of a job.
October 11, 2010 at 8:54 am
There's no way of doing it through management studio.
I accomplished this by using powershell and combining multiple resultset along with @@SERVERNAME into a $variable and pinging a HTML email report with the result.
November 24, 2010 at 2:24 pm
MysteryJimbo (10/11/2010)
There's no way of doing it through management studio.I accomplished this by using powershell and combining multiple resultset along with @@SERVERNAME into a $variable and pinging a HTML email report with the result.
This sounds like exactly what I need to do. Can you post some code?
December 20, 2010 at 1:57 pm
Hi shew and others.
Did you find a way to schedule multi-server queries using PowerShell? I would like to do this too and have been looking at a way to have Enterprise Policy Management Framework use queries in addition to policies.
Howard
shew (11/24/2010)
MysteryJimbo (10/11/2010)
There's no way of doing it through management studio.I accomplished this by using powershell and combining multiple resultset along with @@SERVERNAME into a $variable and pinging a HTML email report with the result.
This sounds like exactly what I need to do. Can you post some code?
December 20, 2010 at 4:24 pm
No, I haven't. Theoretically, you should be able to load the server names into an array and bump through each element in the array with a sqlcmd.exe statement, but I haven't tried it. This would *not* put all of the result sets into a single stream, but it would allow you to run the same statement on all servers from a single command line.
A while back, I did something similar by hardcoding all of the server names in a PowerShell script that accepts the name of a .sql script as a parameter. It's not perfect, but it allows me to execute desired code with a single call. I just have to remember to update the script to add new servers when the environment changes.
December 20, 2010 at 4:29 pm
This blog article shows how to script multi server queries through PowerShell that can be scheduled as a SQL Agent job. Unfortunately, outputing to the PowerShell window is not useful for something that is automated. I'd like to write the output to a table on my CMS. Enquired as to how that can be done in another thread.
December 20, 2010 at 6:00 pm
PHXHoward (12/20/2010)
This blog article shows how to script multi server queries through PowerShell that can be scheduled as a SQL Agent job. Unfortunately, outputing to the PowerShell window is not useful for something that is automated. I'd like to write the output to a table on my CMS. Enquired as to how that can be done in another thread.
Hmmm... Interesting idea. I need to check this out.
>>Unfortunately, outputing to the PowerShell window is not useful for something that is automated.
sqlcmd.exe and the -o switch can help you get around this limitation of invoke-sqlcmd.
Alternatively, this may help too:
invoke-sqlcmd -ServerInstance $DatabaseConnection_STR -Database dba -Query "select select serverproperty('InstanceName')" -Verbose
December 5, 2011 at 1:12 pm
Can someone Please post the code for scheduling a job on Central management server? I am desperately looking for the code.
December 5, 2011 at 1:22 pm
muhammadrazzaqpk-1032285 (12/5/2011)
Can someone Please post the code for scheduling a job on Central management server? I am desperately looking for the code.
You can create a SQL Agent Powershell job likethis:
$instanceNameList = invoke-Sqlcmd -query "
SELECT [server_name] as Name
FROM [msdb].[dbo].[sysmanagement_shared_registered_servers_internal] SSRSI
join [msdb].[dbo].[sysmanagement_shared_server_groups_internal] SSSGI
on SSRSI.server_group_id = SSSGI.server_group_id
where SSSGI.parent_id = '<use this if you want to limit the included servers to a CMS group>'
" -serverinstance "<your CMS here>"
$results = @()
foreach($instanceName in $instanceNameList)
{$results += Invoke-Sqlcmd -Query "
<your query here>
" -ServerInstance $instanceName.Name}
$results| Where-Object {$_} | Export-Csv <path to save csv file> -NoTypeInformation
It will dump the query results to a text file that can be loaded into your CMS using an SSIS step. Pretty powerful so I suggest that you run the Agent job with a proxy account that only has read access to the servers in the CMS.
The above example has four places that must be changed to replace the <...>
Hope that helps
Howard
December 7, 2011 at 1:53 pm
Thanks a lot Haword, I really appreciate your help. However I am getting syntax errors on it. Lets say my instance name is "myserver\dba" (where myserver is my machine name and dba is instance name for my CMS server . I have pasted the query exactly how I put it in the powershell script (with valide server and instance name) but getting error which is posted at the End of this script: Can you please assist me where I am doing things wrong? I don't need where clause for now.
Thanks again for your help.
$instanceNameList = invoke-Sqlcmd -query
"SELECT [server_name] as Name FROM [msdb].[dbo].[sysmanagement_shared_registered_servers_internal] SSRSI join
[msdb].[dbo].[sysmanagement_shared_server_groups_internal] SSSGI on SSRSI.server_group_id = SSSGI.server_group_id "
-serverinstance "myserver\dba"
$results = @() foreach($instanceName in $instanceNameList){$results += Invoke-Sqlcmd -Query
"SELECT a.name, b.type, MAX(b.backup_finish_date)
LastSuccessfulBackup,
CAST((GETDATE() - MAX(b.backup_finish_date)) AS NUMERIC(5, 2)) IntervalInDays
FROM master..sysdatabases a
LEFT OUTER JOIN msdb..backupset b ON a.name = b.database_name
where b.type='D'
GROUP BY a.name, b.type
ORDER BY a.name, b.type" -ServerInstance $instanceName.Name}$results| Where-Object {$_} | Export -Csv E:\Backup_log -NoTypeInformation
Message
Executed as user: myuser. A job step received an error at line 5 in a PowerShell script. The corresponding line is '-serverinstance "myserver\dba" '. Correct the script and reschedule the job. The error information returned by PowerShell is: 'Missing expression after unary operator '-'. '. Process Exit Code -1. The step failed.
December 8, 2011 at 12:06 am
1260221107 (12/8/2011)
...
For God's sake don't SPAM here.
December 8, 2011 at 1:59 am
Dev (12/8/2011)
1260221107 (12/8/2011)
...For God's sake don't SPAM here.
This user has done it on a number of topics now and is getting quite annoying, I have gone through the uses last posts and reported them all
There is just no need for stuff like that
December 8, 2011 at 6:04 am
anthony.green (12/8/2011)
Dev (12/8/2011)
1260221107 (12/8/2011)
...For God's sake don't SPAM here.
This user has done it on a number of topics now and is getting quite annoying, I have gone through the uses last posts and reported them all
There is just no need for stuff like that
In fact I reported many of them... I was just hoping this SPAMer should see it shouldn't SPAM any more. Else it would be really hard to report each individual post as SPAM.
December 8, 2011 at 8:43 am
are you guys talking about me being a spamer? I have subscribed for this topic and seeking for help.
This is first time, I have actually replied to any topic, usually I find by just reading.
Please clarify which user you talking about?
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply