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!
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.