July 16, 2010 at 9:25 am
I almost have this working but I keep getting a security error. Here's the basic script:
$instances = @{"FRITCHEYG1W7"="FRITCHEYG1W7\R208"; "FRITCHEYGW7"="FRITCHEYGW7\R208"}
$job = Invoke-Command -ComputerName (Get-Content "c:\scripts\serverlist.txt") -ScriptBlock {Invoke-Sqlcmd -ServerInstance $instances.$env:computername -Query "exec sp_who2"} -JobName tsql -ConfigurationName MyShell
Wait-Job tsql
$results = Receive-Job -Name tsql #-Keep
$results
Stop-Job -Name tsql
Remove-Job -Name tsql
Basically I'm creating a list of servers & instances using the associative array and then calling invoke-cmd and loading in a list of servers. The invoke-cmd calls invoke-sqlcmd and does it while creating a job so all the calls are asynchronous. I wait for the job to complete and gather the results.
The basic process is all working. I just keep getting this error: "The server was not found or was not accessible."
I'm not sure why. A little help would be appreciated.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 16, 2010 at 9:58 am
Half way there.
Changed:
$job = Invoke-Command -ComputerName (Get-Content "c:\scripts\serverlist.txt") -ScriptBlock {Invoke-Sqlcmd -ServerInstance $instances.$env:computername -Query "exec sp_who2"} -JobName tsql -ConfigurationName MyShell
To:
$job = Invoke-Command -ComputerName (Get-Content "c:\scripts\serverlist.txt") -ScriptBlock {param($instances) Invoke-Sqlcmd -ServerInstance $instances.$env:computername -Query "exec sp_who2"} -JobName tsql -ConfigurationName MyShell -ArgumentList $instances
So now it executes. Connects to the server & runs. Yee ha!
But now I'm getting an error:
A column named 'SPID' already belongs to this DataTable
Anyone with experience or knowledge... I'm all ears.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 16, 2010 at 10:51 am
Actually, it is working. It was pointed out to me that sp_who2 returns two SPID columns. That's the only isue.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply