I have a weird problem with PowerShell and the Invoke-Sqlcmd when used in a ForEach block. In an nutshell, I've done this before many times and not really seen this.

I have a list of SQL Server instances in a database table, let's call it inst_list, and lets say it contains 3 instances: Inst1, Inst2 and Inst3. I'm populating an array called $instances, like this:

$instances = @(Invoke-Sqlcmd -serverinstance $myserver `
-database $mydatabase -query "select name from inst_list") | 
select-object -expand name

Then in a ForEach block, I'm enumerating over the list and running Invoke-Sqlcmd, like this:

 foreach ($instance in $instances)
    Invoke-Sqlcmd -serverinstance $instance -database master -query "SELECT 1"

Simple enough right? However, I'm getting a Failed to Enumerate Collection error output, and alongside that another error which is Can't Connect to Server Inst1 Inst2 Inst3.

It's as if it's seeing my variable $instances as one large, long string than as a collection of instances to loop over.

  • When you say your table (inst_list) has 3 instances, you mean there are 3 rows - one for inst1, one for inst2 and one for inst3 - correct? – Scott Hodgin Apr 20 '17 at 12:42
  • I ran your command as is (of course change servername, table name. It runs perfect for me. I am pulling from SQL server 10.0.2430.0 to pull servernames. Powershell 4.0. – SqlWorldWide Apr 20 '17 at 12:43
  • Scott, that's correct – Molenpad Apr 20 '17 at 12:46
  • @Molenpad - I also ran your command (with simple changes) and it ran fine for me as well - what's in $instances (before the foreach) - is it a string or an array? – Scott Hodgin Apr 20 '17 at 13:05
  • Ok - it seems to be a perms thing related to SMO on 3/70 servers. I should have mentioned earlier that there SMO is used as well as an invoke-sqlcmd. If I remove the SMO bit it works ok. Does use of SMO require specific permissions. – Molenpad Apr 20 '17 at 13:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.