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

score 0 · Answer 1 · answered Apr 01 '16 at 18:47

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 01 '16 at 18:47

briantist

45,546
6
82
127

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. – Jeff A Apr 01 '16 at 18:55
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. – briantist Apr 01 '16 at 18:57
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? – Jeff A Apr 01 '16 at 19:01
@JeffA you're defining `$Inst = $SQLInst.INSTANCE` ; does `$SQLInst` actually contain a property called `INSTANCE`? – briantist Apr 01 '16 at 19:02
I think I have it, you pointed me in the right direction thanks! – Jeff A Apr 01 '16 at 19:06

Valeriy Glushenkov · Answer 2 · 2016-04-12T17:50:24.160

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 '16 at 17:50

answered Apr 05 '16 at 00:56

Valeriy Glushenkov

114
4

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 – Jeff A Apr 07 '16 at 15:16
Check if $Serverlist array is populated. You can do output the values into shell: Write-Host $Serverlist – Valeriy Glushenkov Apr 08 '16 at 19:04
I added that and get the output "System.Data.DataRow" currently I have the query only returning one value for testing. – Jeff A Apr 11 '16 at 19:40
What version of powershell are you using? You can find out by typing $PSVersionTable into PowerShell concole. – Valeriy Glushenkov Apr 11 '16 at 20:37
Appears to be PSVersion 4.0 – Jeff A Apr 11 '16 at 21:55
I have edited the last statement. It might help you. It appears to be working against powershell 2.0. – Valeriy Glushenkov Apr 12 '16 at 19:25
I tried the script you posted and getting this error: Invoke-Sqlcmd : Value cannot be null. Parameter name: ServerInstance At line:6 char:1 + Invoke-Sqlcmd -ServerInstance $SQLInst -Database Master -Query "select @@servern ... + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : InvalidArgument: (:) [Invoke-Sqlcmd], ArgumentNullException + FullyQualifiedErrorId : CannotGetServerInstance,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand – Jeff A Apr 13 '16 at 21:36

score 0 · Answer 3 · answered Aug 23 '22 at 16:07

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
}

Error passing variable SQL instance name into Invoke-SQLcmd

3 Answers3