作为 SQL Server 代理作业中的步骤运行时出现语法错误 [英] Syntax error when run in as step in SQL Server Agent Job

问题描述

当您在 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 代理作业中的步骤运行时出现语法错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！