CODEWITHSUNDEEP
sql-serverpowershellsql-server-2016
user3657339
user3657339

Reputation: 627

Syntax error when run in as step in SQL Server Agent Job

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)')"
}

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

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

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.

Upvotes: 2

Views: 775

Answers (1)

abbgrade
abbgrade

Reputation: 578

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 + "')"
}

Upvotes: 2

Related Questions