Error passing variable SQL instance name into Invoke-SQLcmd

Question

I'm working on a project that pulls a list of SQL instances from server A and then loops through the returned list and runs a query (eventually to audit users and insert results into table) but getting an error instance not found. It seems I'm not defining the variable correctly in the loop because if I hardcode the instance name it works.

I appreciate any input on how to fix this.

$Serverlist = invoke-sqlcmd -ServerInstance TESTSERVER1 -Database TESTDB -Query "SELECT instancename from testtable"

foreach ($SQLInst in $Serverlist) 
{
$Inst = $SQLInst.INSTANCE
Invoke-Sqlcmd -ServerInstance ${$Inst} -Database Master -Query "select @@servername as servername" | select -ExpandProperty servername
} #end foreach loop

Invoke-Sqlcmd : A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) At line:12 char:1 + Invoke-Sqlcmd -ServerInstance ${$SQLInst} -Database Master -Query "select @@serv ... + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : InvalidOperation: (:) [Invoke-Sqlcmd], SqlException + FullyQualifiedErrorId : SqlExectionError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

briantist · Accepted Answer · 2016-04-01 18:47:18Z

0

There's no reason to use curly braces like that ${$Inst} in this instance.

Simply using $Inst will work fine. If you do use curly braces, then you don't use the $ inside: ${Inst}.

Invoke-Sqlcmd -ServerInstance $Inst

# or

Invoke-Sqlcmd -ServerInstance ${Inst}

answered Apr 1, 2016 at 18:47

briantist

48.3k6 gold badges94 silver badges139 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Jeff A Over a year ago

I've tried using both variations and still get the error: Invoke-Sqlcmd : A network-related or instance-specific error occurred while establishing a connection to SQL Server. If I hardcode the instance name that the top query returns it works.

briantist Over a year ago

Are you certain that the variable $Inst contains the value you think it does? Add a Write-Verbose -Message "~$Inst~" -Verbose and see what it says, or better yet debug the code and add a breakpoint so you can inspect the variables.

Jeff A Over a year ago

I don't get any output with the statement but if I add Write-Output $SQLInst I get a value. Am I qualifying $Inst as an instance name correctly?

briantist Over a year ago

@JeffA you're defining $Inst = $SQLInst.INSTANCE ; does $SQLInst actually contain a property called INSTANCE?

Jeff A Over a year ago

I think I have it, you pointed me in the right direction thanks!

Valeriy Glushenkov · Accepted Answer · 2016-04-12 17:50:24Z

0

I would check to make sure that each instance is the correct one:

$Serverlist = invoke-sqlcmd -ServerInstance TESTSERVER1 -Database TESTDB -Query "SELECT instancename from testtable"

    foreach ($SQLInst in $Serverlist) 
    {
      $Inst = $SQLInst.INSTANCE
      Write-Host $Inst
    } #end foreach loop

I noticed some problems with my previous statement. Can you try this?

$Serverlist = invoke-sqlcmd -ServerInstance TESTSERVER1 -Database TESTDB -Query "SELECT instancename from testtable"

foreach ($SQLInst in $Serverlist) 
{  
  $Inst = $SQLInst.instancename
Invoke-Sqlcmd -ServerInstance "$Inst" -Database Master -Query "select @@servername as servername" | select -ExpandProperty servername
} #end foreach loop

edited Apr 12, 2016 at 17:50

answered Apr 5, 2016 at 0:56

Valeriy Glushenkov

1244 bronze badges

7 Comments

Jeff A Over a year ago

I'm getting an error when I make that change: Invoke-Sqlcmd : Value cannot be null. Parameter name: ServerInstance At line:16 char:1 + Invoke-Sqlcmd -ServerInstance $SQLInst -Database Master -Query "select @@servern ... + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : InvalidArgument: (:) [Invoke-Sqlcmd], ArgumentNullException + FullyQualifiedErrorId : CannotGetServerInstance,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

Valeriy Glushenkov Over a year ago

Check if $Serverlist array is populated. You can do output the values into shell: Write-Host $Serverlist

Jeff A Over a year ago

I added that and get the output "System.Data.DataRow" currently I have the query only returning one value for testing.

Valeriy Glushenkov Over a year ago

What version of powershell are you using? You can find out by typing $PSVersionTable into PowerShell concole.

Jeff A Over a year ago

Appears to be PSVersion 4.0

|

Jim · Accepted Answer · 2022-08-23 16:07:41Z

0

When you are referencing a result from a query, you must specify the column name even if there is only one column in the query. Enclosing the query in parentheses and using dot notation with the column name will convert the rows to a list of values. Your original problem was you had the column name incorrect.

Try this:

$Serverlist = ( invoke-sqlcmd -ServerInstance TESTSERVER1 -Database TESTDB -Query "SELECT instancename from testtable").instancename

$Serverlist | ForEach-Object {
    ( Invoke-Sqlcmd -ServerInstance $SQLInst -Database Master -Query 'select @@servername as servername' ).servername
}

answered Aug 23, 2022 at 16:07

Jim

81211 silver badges16 bronze badges

Collectives™ on Stack Overflow

Error passing variable SQL instance name into Invoke-SQLcmd

3 Answers 3

5 Comments

7 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

7 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related