Author Posts

January 5, 2017 at 9:22 pm

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)')"}}

January 9, 2017 at 3:02 pm

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?

January 9, 2017 at 3:14 pm

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

January 9, 2017 at 6:00 pm

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.

January 26, 2017 at 4:32 pm

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!