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


2008 Central Management Server - Schedule Multi Server Query?


2008 Central Management Server - Schedule Multi Server Query?

Author
Message
muhammadrazzaqpk-1032285
muhammadrazzaqpk-1032285
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 Visits: 131
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.
Dev
Dev
SSCrazy Eights
SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)

Group: General Forum Members
Points: 8586 Visits: 1602
1260221107 (12/8/2011)
...


For God's sake don't SPAM here.
anthony.green
anthony.green
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23386 Visits: 6519
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



Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
When a question, really isn't a question - Jeff Smith
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger


Dev
Dev
SSCrazy Eights
SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)

Group: General Forum Members
Points: 8586 Visits: 1602
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.
muhammadrazzaqpk-1032285
muhammadrazzaqpk-1032285
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 Visits: 131
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?
anthony.green
anthony.green
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23386 Visits: 6519
muhammadrazzaqpk-1032285 (12/8/2011)
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?


no we are talking about a user called "1260221107", there where a number of topics which this user had put an advertising message for Gucci and other products.

these where reported to the admins who removes them from the topics if they are found to be spam.

as this has happened you where the last offical none spamer to post which may make it seem like we are talking about you.

you can see from the white quote boxes in mine and Dev's posts the chain working from the inside out.



Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
When a question, really isn't a question - Jeff Smith
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger


muhammadrazzaqpk-1032285
muhammadrazzaqpk-1032285
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 Visits: 131
Thanks for the clarification. Appreciate it -
PHXHoward
PHXHoward
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1735 Visits: 1264
Try moving the open quote onto the same line as the invoke-sqlcmd -query. I think it has to do with white space and how it is reading the script. I'll take a look at it when I get into the office.
muhammadrazzaqpk-1032285
muhammadrazzaqpk-1032285
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 Visits: 131
Thanks - I have given it every try - As you know I am not good at all with powershell scripting but it will be helpful for me if a working script where I have to just change name of my server and query.

Perhaps if you take my script and I am sure you will be able to reproduce the error.

I really do appreciate your time.
PHXHoward
PHXHoward
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1735 Visits: 1264
muhammadrazzaqpk-1032285 (12/8/2011)
Thanks - I have given it every try - As you know I am not good at all with powershell scripting but it will be helpful for me if a working script where I have to just change name of my server and query.

Perhaps if you take my script and I am sure you will be able to reproduce the error.

I really do appreciate your time.


Hi

Run this script. It should create a SQL Agent job that when run will run your query across all SQL Servers that are registered in your CMS. The results will be saved to a text file on your E drive which you can load into a database using SSIS or some other method.


USE [msdb]
GO

/****** Object: Job [Last Successful Backup] Script Date: 12/8/2011 1:02:48 PM ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [[Uncategorized (Local)]]] Script Date: 12/8/2011 1:02:48 PM ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Last Successful Backup',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'No description available.',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [PowerShell] Script Date: 12/8/2011 1:02:48 PM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'PowerShell',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'PowerShell',
@command=N'$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',
@database_name=N'master',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO




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