Invoke-Sqlcmd Nested For-Each Loops

This topic contains 4 replies, has 3 voices, and was last updated by Profile photo of Eric Riddle Eric Riddle 6 months ago.

  • Author
    Posts
  • #61336
    Profile photo of Eric Riddle
    Eric Riddle
    Participant

    I'm stumped. I only return the first value from sys.databases then the script moves on to the next server. I have, for example, 10 servers listed and therefore I get only 10 results when there are actually dozens of databases in total. I've looked everyone and can't find an answer specific to a nested invoke-sqlcmd. Code below:

    Import-Excel C:\servers.xlsx -WorkSheetname servers |
     %{Invoke-SqlCmd -ServerInstance $_.srvr_name -Query "select @@servername as server_name, name, state_desc, getdate() as dateStamp from master.sys.databases" |
      %{Invoke-SqlCmd -ServerInstance servername -Query "insert into databasename.dbo.databaseState (server_name, name, state_desc, dateStamp) values ('$($_.server_name)', '$($_.name)', '$($_.state_desc)', '$($_.dateStamp)')"}}
    
    ##I get only one result with the below code
    Invoke-Sqlcmd -ServerInstance servername-Query "select srvr_name from databasename.dbo.serverList" |
     %{Invoke-SqlCmd -ServerInstance $_.srvr_name -Query "select @@servername as server_name, name, state_desc, getdate() as dateStamp from master.sys.databases" |
      %{Invoke-SqlCmd -ServerInstance servername -Query "insert into databasename.dbo.databaseState (server_name, name, state_desc, dateStamp) values ('$($_.server_name)', '$($_.name)', '$($_.state_desc)', '$($_.dateStamp)')"}}
    
    
  • #61552
    Profile photo of Don Jones
    Don Jones
    Keymaster

    You'll have to be patient because I don't work with SQL that much anymore.

    You're saying that this:

    Invoke-Sqlcmd -ServerInstance servername -Query "select srvr_name from databasename.dbo.serverList"
    

    If run entirely by itself, returns only one object?

  • #61560
    Profile photo of Ron
    Ron
    Participant

    You should be able to identify where the code is going wrong by breaking that down into multiple steps, and storing the results so you can check each one. If you still can't figure it out, the results might give us more to go on. Be sure to obfuscate any proprietary info.

    $a = Invoke-Sqlcmd -ServerInstance servername-Query "select srvr_name from databasename.dbo.serverList"
    $a
    
    $b = $a | %{Invoke-SqlCmd -ServerInstance $_.srvr_name -Query "select @@servername as server_name, name, state_desc, getdate() as dateStamp from master.sys.databases"
    $b
    
    $c = $b | %{Invoke-SqlCmd -ServerInstance servername -Query "insert into databasename.dbo.databaseState (server_name, name, state_desc, dateStamp) values ('$($_.server_name)', '$($_.name)', '$($_.state_desc)', '$($_.dateStamp)')"}}
    $c
    
  • #61570
    Profile photo of Eric Riddle
    Eric Riddle
    Participant

    The initial invoke-sqlcmd returns exactly 6 servers from my serverlist table. However, the point at which it cycles through the sys.databases only one database shows up in my dbo.databaseState table for the result set. What I should see is a list of every database for all 6 servers from my list. I get only one and then it quits without error.

  • #62713
    Profile photo of Eric Riddle
    Eric Riddle
    Participant

    I was fortunate enough to find a solution to this little problem. Evidently according to a PowerShell MVP piping one Invoke-Sqlcmd to another is just not possible. Here was his solution:

    $ServerList = Invoke-SqlCmd -ServerInstance $_.srvr_name -Query "select @@servername as server_name, name, state_desc, getdate() as dateStamp from master.sys.databases";
    $ServerList | %{ Invoke-SqlCmd -ServerInstance servername -Query "insert into databasename.dbo.databaseState (server_name, name, state_desc, dateStamp) values ('$($_.server_name)', '$($_.name)', '$($_.state_desc)', '$($_.dateStamp)')"
    

    Thanks for the help!

You must be logged in to reply to this topic.