April 29, 2015 at 2:46 pm
Hello - I have a Central Management Server that keeps enterprise instances. I want to select the instances from those msdb tables into an array and loop through and execute sql statements. Standard stuff. There are lots of ways to do this but I am wondering what I am doing wrong here with the [string[]]$WorkflowInstance. The array $ServerNameList is working fine but once it goes into the WorkFlow as a parameter - I get server not found errors. What am I doing wrong here?
Import-Module sqlps -DisableNameChecking;
WorkFlow Exec-SQLWorkflow
{
param(
[Parameter(Mandatory=$true)]
[string[]]$WorkflowInstance,
[Parameter(Mandatory=$false)]
[string]$Database,
[Parameter(Mandatory=$true)]
[string]$FilePath
)
foreach -parallel ($s in $WorkflowInstance)
{ invoke-sqlcmd -ServerInstance $s -Database $Database -InputFile $FilePath -querytimeout 60000 -MaxCharLength 65535 -ConnectionTimeout 1 ; }
}
$ServerNameList = @(Invoke-Sqlcmd -Database msdb -Query 'SELECT s.name ServerName
FROM msdb.dbo.sysmanagement_shared_registered_servers_internal s
JOIN msdb.dbo.sysmanagement_shared_server_groups_internal g ON s.server_group_id = g.server_group_id
WHERE g.name = ''Development''
ORDER BY s.name' -ServerInstance CMSSERVER2)
Exec-SQLWorkflow -WorkflowInstance $ServerNameList -Database master -FilePath 'C:\SQLFlatFiles\Servers_ServerProperties.sql' -Verbose | Export-Csv -LiteralPath 'C:\Temp\processes.csv' -Delimiter '|' -NoTypeInformation
April 29, 2015 at 11:43 pm
You could get a little more information and, at the same time, validation that variables are being handled correctly by adding calls to Write-Verbose/Write-Debug etc. This would help narrow down the scope of the problem.
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply