Invoke-Sqlcmd Nested For-Each Loops

Welcome Forums General PowerShell Q&A Invoke-Sqlcmd Nested For-Each Loops

This topic contains 4 replies, has 3 voices, and was last updated by

 
Participant
1 year, 10 months ago.

  • Author
    Posts
  • #61336

    Participant
    Points: 0
    Rank: Member

    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

    Keymaster
    Points: 1,673
    Helping HandTeam Member
    Rank: Community Hero

    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
    Ron

    Participant
    Points: 0
    Rank: Member

    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

    Participant
    Points: 0
    Rank: Member

    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

    Participant
    Points: 0
    Rank: Member

    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!

The topic ‘Invoke-Sqlcmd Nested For-Each Loops’ is closed to new replies.