Remoting and Invoke-SQLCmd

• Grant Fritchey

  SSC Guru

  Points: 399043

  More actions

  July 16, 2010 at 9:25 am

  #237701

  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

• Grant Fritchey

  SSC Guru

  Points: 399043

  More actions

  July 16, 2010 at 9:58 am

  #1194194

  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

• Grant Fritchey

  SSC Guru

  Points: 399043

  More actions

  July 16, 2010 at 10:51 am

  #1194225

  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