sql-server - 循环中调用-Sqlcmd错误，一次没有错误？

Question

如果我运行这个 .ps1 脚本-

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

我在控制台上得到以下信息，没有错误 -

MyDataBase
18

但是，如果我在更大的脚本中的 for 循环中尝试相同的查询 -

$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
    }
}

我得到正确的查询结果，但在控制台上出现错误 -

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                                                                                                                                                                                  

为什么在循环中运行它而不是运行一次时会出现错误？另外，即使我收到错误，为什么查询仍然有效？我想我错过了任何帮助将不胜感激！

标签： sql-serverpowershellinvoke-sqlcmd

Answer 1

我认为这是因为您Name在字符串上调用属性，实际上什么也不返回。试试这个（我刚刚删除了对属性的调用$dbserver.Name）$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
    }
}

该错误一定是因为它正在寻找一个数据库实例“”但它没有找到它，并且查询可能仍然进行，因为第一台服务器可能没有实例，或者因为这些值已经从前。但我只是推测，并没有真正使用过这个 cmdlet。

PS：如果你想避免调用空属性添加Set-StrictMode -Version 2到你的脚本，例如：

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

希望能帮助到你。