Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

2008 Central Management Server - Schedule Multi Server Query? Expand / Collapse
Author
Message
Posted Wednesday, December 7, 2011 1:53 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, March 14, 2014 12:01 PM
Points: 7, 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.
Post #1218224
Posted Thursday, December 8, 2011 12:06 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 6:53 PM
Points: 1,932, Visits: 1,589
1260221107 (12/8/2011)
...


For God's sake don't SPAM here.


Dev

Devendra Shirbad | BIG Data Architect / DBA | Ex-Microsoft CSS (SQL 3T)
*** Open Network for Database Professionals ***

LinkedIn: http://www.linkedin.com/in/devendrashirbad
Post #1218404
Posted Thursday, December 8, 2011 1:59 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:19 AM
Points: 5,216, Visits: 5,109
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
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

Post #1218440
Posted Thursday, December 8, 2011 6:04 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 6:53 PM
Points: 1,932, Visits: 1,589
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.


Dev

Devendra Shirbad | BIG Data Architect / DBA | Ex-Microsoft CSS (SQL 3T)
*** Open Network for Database Professionals ***

LinkedIn: http://www.linkedin.com/in/devendrashirbad
Post #1218566
Posted Thursday, December 8, 2011 8:43 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, March 14, 2014 12:01 PM
Points: 7, 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?
Post #1218673
Posted Thursday, December 8, 2011 8:48 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:19 AM
Points: 5,216, Visits: 5,109
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
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

Post #1218680
Posted Thursday, December 8, 2011 8:56 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, March 14, 2014 12:01 PM
Points: 7, Visits: 131
Thanks for the clarification. Appreciate it -
Post #1218691
Posted Thursday, December 8, 2011 9:08 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 5:49 PM
Points: 301, Visits: 927
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.

Post #1218700
Posted Thursday, December 8, 2011 11:36 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, March 14, 2014 12:01 PM
Points: 7, 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.
Post #1218841
Posted Thursday, December 8, 2011 1:10 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 5:49 PM
Points: 301, Visits: 927
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



Post #1218919
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse