Remoting and Invoke-SQLCmd

  • 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

  • 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

  • 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