Invoke-Sqlcmd error in Loop, No error once?

Question

If I run this .ps1 script-

$secID = Invoke-Sqlcmd -ServerInstance "MyDBServer" -Database "MyDataBase"-Query "SELECT SysID FROM dbo.SecurityLevels WHERE LEVELNAME LIKE '%User%';" 
Write-Host "MyDataBase"
Write-Host $secID.SysID

I get the following on the console with no error -

MyDataBase
18

However if I try this same query in a for loop in a larger script -

$dbservers = @('DataBaseServer1', 'DataBaseServer2')

foreach ($dbserver in $dbservers)
{
    $databases = Get-SqlDatabase -ServerInstance $dbserver | Where-Object { $_.Name -Match '\d{3,4}' -and $_.Name -notlike '*test*'}
    foreach ($database in $databases)
    {
            $secID = Invoke-Sqlcmd -ServerInstance $dbserver.Name -Database $database.Name -Query "SELECT SysID FROM dbo.SecurityLevels WHERE LEVELNAME LIKE '%User%';" 
            Write-Host $database.Name
            Write-Host $secID.SysID
    }
}

I get the correct query result but errors preceding it on the console -

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 \SQL.ps1:28 char:13                                                                                                      
+ ...    $secID = Invoke-Sqlcmd -ConnectionString $ConnectionString -Query  ...                                                                                                     
+                 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~                                                                                                         
    + CategoryInfo          : InvalidOperation: (:) [Invoke-Sqlcmd], SqlException                                                                                                   
    + FullyQualifiedErrorId : SqlExceptionError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand                                                                          

Invoke-Sqlcmd :                                                                                                                                                                     
At \SQL.ps1:28 char:13                                                                                                      
+ ...    $secID = Invoke-Sqlcmd -ConnectionString $ConnectionString -Query  ...                                                                                                     
+                 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~                                                                                                         
    + CategoryInfo          : ParserError: (:) [Invoke-Sqlcmd], ParserException                                                                                                     
    + FullyQualifiedErrorId : ExecutionFailureException,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand*                                                                  
MyDataBase                                                                                                                                                                                                                                                                                                                                                            
18

Why do I get errors when running it in the loop versus running it one time? Also why does the query work even though I get an error? I think I am missing something any help would be greatly appreciated!

Roque Sosa · Accepted Answer · 2020-04-08T22:40:38.597

I think its because you are calling property Name on a String, effectively returning nothing. try this (I just deleted the calling of the property $dbserver.Nameto $dbserver):

$dbservers = @('DataBaseServer1', 'DataBaseServer2')

foreach ($dbserver in $dbservers)
{
    $databases = Get-SqlDatabase -ServerInstance $dbserver | Where-Object { $_.Name -Match '\d{3,4}' -and $_.Name -notlike '*test*'}
    foreach ($database in $databases)
    {
            $secID = Invoke-Sqlcmd -ServerInstance $dbserver -Database $database.Name -Query "SELECT SysID FROM dbo.SecurityLevels WHERE LEVELNAME LIKE '%User%';" 
            Write-Host $database.Name
            Write-Host $secID.SysID
    }
}

The error must be because its looking for a DB instance "" and it doesn't find it, and the query might still go though because the the first server might not have an instance, or because those values were already loaded into the properties from before. But I'm just speculating, haven't really used this cmdlets.

PS: if you want to avoid calling empty properties add Set-StrictMode -Version 2 to your script, example:

PS > $text = "This is just a string"
PS > $text.AnyProperty
PS > Set-StrictMode -Version 2
PS > $text.AnyProperty
The property 'AnyProperty' cannot be found on this object. Verify that the property exists.
At line:1 char:1
+ $text.AnyProperty
+ ~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], PropertyNotFoundException
    + FullyQualifiedErrorId : PropertyNotFoundStrict

Hope it helps.

Invoke-Sqlcmd error in Loop, No error once?

1 Answers1