Error passing variable SQL instance name into Invoke-SQLcmd

1
votes

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

2
sql-serverpowershellsqlps

2 Answers

0
votes

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}
0
votes

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