作为 SQL Server 代理作业中的步骤运行时出现语法错误

问题描述：

当您在 PowerShell 窗口中运行以下脚本时，它可以正常工作.

Below scripts works perfectly fine, when you run it in PowerShell window.

Import-Csv $DestinationFile | ForEach-Object {
    Invoke-Sqlcmd -Database $database -ServerInstance $server -Query "INSERT INTO $table VALUES ('$($SourceFile)','$($_.LineNumber)','$($_.LineDescription)','$($_.Criteria)','$($_.LineValue)')"
}

但是当您通过 SQL Server 代理作业运行此脚本时...它会出现语法错误.

But when you run this script through SQL Server Agent job ... it gives syntax error.

当作为作业安排时，下面提到的行也不起作用.

Also below mentioned line doesn't work when scheduled as a job.

$objWord = New-Object -Com Word.Application
$objWord.Visible = $false # $false

我们使用 PowerShell 搜索文档，并根据模式搜索、找到的结果……将它们导入数据库.

Using PowerShell, we search the docs and based on pattern search, results we found... we import them into the database.

当您在 PowerShell 控制台中运行时一切正常，但是当您尝试通过 SQL Server 运行 PS1 文件时，它没有任何作用.

All working good when you run in PowerShell console, but when you try to run PS1 file through SQL Server it doesn't do anything.

答

请试试这个，因为在 SQL Agent 中 $( ... ) 是保留的.

Please try this instead, since in SQL Agent $( ... )is reserved.

Import-Csv $DestinationFile | ForEach-Object {
    Invoke-Sqlcmd -Database $database -ServerInstance $server -Query "INSERT INTO $table VALUES ('$SourceFile','" + $_.LineNumber + "','" + $_.LineDescription + "','" + $_.Criteria + "','" + $_.LineValue + "')"
}

作为 SQL Server 代理作业中的步骤运行时出现语法错误

相关推荐