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
The Scary DBA Author of: SQL Server 2022 Query Performance Tuning, 6th Edition and SQL Server Execution Plans, 3rd Edition
Product Evangelist for Red Gate Software
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
The Scary DBA Author of: SQL Server 2022 Query Performance Tuning, 6th Edition and SQL Server Execution Plans, 3rd Edition
Product Evangelist for Red Gate Software
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
The Scary DBA Author of: SQL Server 2022 Query Performance Tuning, 6th Edition and SQL Server Execution Plans, 3rd Edition
Product Evangelist for Red Gate Software
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply