question

sunil2711 avatar image
sunil2711 asked

Power Shell

Hi All, I am new to the powershell When I trying to file existance step in sqlserver agent by using following powershell command. test-path \\\\ ....\\....\\ConsolidatedCommCustLit.xlsx I given wrong file name but the job is executed successfully.
sql-server-2008powershell
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Tim avatar image
Tim answered
If I am not mistaken, when a SQL Agent job calls a PowerShell task, that task is executed outside of SQL Server, similar to executing xp_cmdshell. If you pass a wrong parameter to the Posh script, it will still call PowerShell successfully. You may want to test the scripts first in the PowerShell ISE.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

SirSQL avatar image
SirSQL answered
PowerShell test-path evaluates whether the file exists and returns a true or false condition, not an error. You have to manually force that error condition if the file does not exist. The following does that if (test-path \\server\drive\ filename.txt) { write-output "File exists" } else { write-error "File does not exist" }
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@SirSQL `Write-Error` will not cause the step to fail. You have to use `throw` to throw unhandled critical exception, which will cause the step to fail.
0 Likes 0 ·
Pavel Pawlowski avatar image
Pavel Pawlowski answered
If you want a PoverShell script in the agent to end with failure a critical error needs to occur. This is not a case of `Wrire-Error` as it simply writes an error message, but the pipeline continues. If you really want to end with failure, you need to throw exception in the script. If you put the script below in the agent PowerShell script, in case the `$fileToTest` doesn't exists, the agent step will end with failure. If `Write-Error` was used instead of `throw` then the step will end with success. $fileToTest = "\\server\path\filetotest.xlsx" if (Test-Path $fileToTest) { Write-Output "File [$fileToTest] exists" } else { throw "File [$fileToTest] does not exists" }
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.