SQL 代理 - PowerShell 步驟“語法錯誤”

我在未執行的 SQL 代理作業步驟中有以下程式碼設置。收到的消息很簡單：

無法開始執行第 1 步（原因：第 (46) 行：語法錯誤）。步驟失敗。

工作的持續時間是：00:00:00

該腳本的第 46 行是[here-string](http://technet.microsoft.com/en-us/library/ee692792.aspx)：

   ,@DriveLetter = '$($c.DriveLetter)'

此腳本在 SQL Server 代理之外完美執行。

伺服器名稱表：

CREATE TABLE ServerTable (
ServerName varchar(50)
)

磁碟空間表類似於：

CREATE TABLE DiskSpace (
ID int IDENTITY(1,1),
ServerName varchar(50),
DriveLetter varchar(2),
DiskSpaceCapacityGB decimal(12,5),
DiskSpaceFreeGB decimal(12,5)
)

PowerShell 腳本：

*This command is to allow SQL Agent job to show failure in event PowerShell script errors
Default behavior in SQL Agent for PowerShell steps it 'Continue'*
$erroractionpreference = "Stop"

$sqlInstance = 'server1'
$sqlDatabase = 'database1'



$qServerList = @"
Select ServerName From ServerTable
"@



$srvList = Invoke-Sqlcmd -ServerInstance $sqlInstance -Database $sqlDatabase -Query $qServerList | 
Where-Object {$_ -ne '' -or $_ -ne $null} | Select-Object -ExpandProperty ServerName



foreach ($s in $srvList)
{
if (Test-Connection -ComputerName $s -Count 1 -ErrorAction 'SilentlyContinue')
{
try 
{
$cServer = Get-WmiObject Win32_Volume -ComputerName $s -ErrorAction 'Stop' |
where {$*.DriveType -eq 3 -and $*.DriveLetter } |
Select-Object @{Label="ServerName";Expression={$s}},
@{Label="DriveLetter";Expression={$*.DriveLetter}},
@{Label="DiskSpaceCapacityGB";Expression={"{0:N0}" -f($*.Capacity/1GB)}},
@{Label="DiskFreeSpaceGB";Expression={"{0:N2}" -f($_.FreeSpace/1GB)}}

   foreach ($c in $cServer) 
   {
       $qAddServerDiskSpace = @"

EXEC [dbo].[StoredProcInsert]
@ServerName = '$($c.ServerName)'
,@DriveLetter = '$($c.DriveLetter)'
,@DiskSpaceCapacityGB = $($c.DiskSpaceCapacityGB)
,@DiskFreeSpaceGB = $($c.DiskFreeSpaceGB)
"@

       try
       {
           Invoke-Sqlcmd -ServerInstance $sqlInstance -Database $sqlDatabase -Query $qAddServerDiskSpace -ErrorAction 'Stop'
       }
       catch
       {
           $ErrorMsg = $_.Exception.Message
           $fullMsg = "Error writing executing dbo.StoredProcInsert for $s - $ErrorMsg"
           Return $fullMsg
       }           
   }

} catch { $ErrorMsg = $_.Exception.Message $qAddPSErrorLog = @"

EXEC [dbo].[StoredProcInsert_Error] 
@ServerName = '$($cServer.ServerName)'
, @ErrorText = '$($ErrorMsg)'
"@
try 
{
Invoke-Sqlcmd -ServerInstance $sqlInstance -Database $sqlDatabase -Query $qAddPSErrorLog -ErrorAction 'Stop'
}
catch 
{
$ErrorMsg = $_.Exception.Message
$fullMsg = "Error writing executing dbo.StoredProcInsert_Error for $s - $ErrorMsg"
Return $fullMsg
}
}
}
else
{
$qAddPSErrorLog = @"
EXEC [dbo].[StoredProcInsert_Error] 
@ServerName = '$($cServer.ServerName)'
, @ErrorText = 'Unable to ping server'
"@

try { Invoke-Sqlcmd -ServerInstance $sqlInstance -Database $sqlDatabase -Query $qAddPSErrorLog -ErrorAction ‘Stop’ } catch { $ErrorMsg = $_.Exception.Message $fullMsg = “Error writing executing dbo.StoredProcInsert_Error for $s - $ErrorMsg” Return $fullMsg } }

}

編輯

設置其他步驟以嘗試將 CmdExec 類型用作PowerShell -Command "& { }". 通過此執行，腳本執行並為適當的 catch 塊填充錯誤日誌，但沒有磁碟空間數據寫入表。

此執行的代理歷史記錄顯示一條警告消息：

以使用者身份執行的消息：NT Service\SQLAgent$myInstance。警告：某些導入的命令名稱包含未經批准的動詞，這可能會使它們不易被發現。使用 Verbose 參數了解更多詳細資訊，或鍵入 Get-Verb 以查看批准的動詞列表。處理退出程式碼 0。該步驟成功。

如果我使用相同類型的步驟但呼叫文件：PowerShell -File MyScript.ps1 我會收到與上面的 PowerShell 步驟類型相同的語法錯誤消息。

這不是很直覺，我從來沒有找到任何具體的解釋

$$ e.g. no exact BOL or white paper was found $$. SQL 代理作業中的語法錯誤是基於User Tokens的 T-SQL 語法錯誤。所以這基本上意味著 PowerShell 子表達式運算符被視為 SQL Server 代理的令牌。因此，在 PowerShell 中，這$( )似乎被視為 SQL Server 代理的保留字元序列。所以 SQL 代理會從 BOL 文章中尋找類似 T-SQL 範例的內容： PRINT N'Current database name is $(A-DBN)' ; .

所以在我的腳本中，當它到達時,@DriveLetter = '$($c.DriveLetter)'，“$c.DriveLetter”不是允許的令牌之一。

解決方法就是不要在 PowerShell 中使用子表達式語句。我會在我的腳本中進行調整，這樣：

$qAddServerDiskSpace = @"
EXEC [dbo].[StoredProcInsert]
   @ServerName = '$($c.ServerName)'
   ,@DriveLetter = '$($c.DriveLetter)'
   ,@DiskSpaceCapacityGB = $($c.DiskSpaceCapacityGB)
   ,@DiskFreeSpaceGB = $($c.DiskFreeSpaceGB)
"@

必須修改為這樣的：

$severName = $c.ServerName
$driveLetter = $c.DriveLetter
$capacityGB = $c.DiskSpaceCapacityGB
$freeGB = $c.DiskFreeSpaceGB

$qAddServerDiskSpace = @"
EXEC [dbo].[StoredProcInsert]
  @ServerName = '$serverName'
  ,@DriveLetter = '$driveLetter'
  ,@DiskSpaceCapacityGB = $CapacityGB
  ,@DiskFreeSpaceGB = $freeGB
"@

對我的腳本進行了上述調整，它現在可以完美執行。

引用自：https://dba.stackexchange.com/questions/58669