Powershell sending wrong exit code to SQL Agent

I went to the edge of the Internet, and could not find the answer to my Powershell question.  Say I returned exit code from Powershell. SQL Agent, running the command from CmdExec, would always receive an exit code of 0.   It didn’t matter what I tried from all of the suggestions.  The Powershell would fail, but Agent always thought that it succeeded.

This solution ended up working.  Use “[Environment]::Exit(1);” rather than “Exit 1;”  In SQL Agent, you can look for a box in the job step area for CmdShell, and tell it what exit code represents success.  You’d want to change this to 1, if that represents success for your script.    The StackOverflow question can be found here:  http://stackoverflow.com/questions/8902004/powershell-fails-to-return-proper-exit-code

Although the question shows a solution, it doesn’t state any reasons.   I found that it was because of [Parameter(Mandatory=$True)] in the parameter portion of the Powershell script.  My script had this, and so does the one in the StackOverflow question.

Once I ripped out [Parameter(Mandatory=$True)] from the script, it processed the exit codes normally.  Now, this is a little inconvenient, since I do want those parameters to be mandatory.   With this knowledge, I was able to find the StackOverflow question that pointed to the answer.  Use [Environment]::Exit(1);

This entry was posted in Powershell and tagged . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s