Why doesn’t my SQL Server Job that uses CmdExec not report a failure correctly

Here’s the scenario. I am running SQL Server and I have a created a Job that has among other steps a CmdExec step. This CmdExec step calls a C# console application that I wrote. My C# console application writes its errors to the Windows Event Log instead of just throwing an exception. True, I could just throw the exception and the exception would get logged to the Windows Event Log, but there would be 2 or 3 entries and they would be ugly and in general just confusing. I want to have better control over what gets written to the Windows Event Log so I have a try-catch at the highest level of my console application so that nearly no exception will be just thrown.

The problem

The problem is that when I catch the exception my program no longer returns a non-zero return code (error code) like it would if an uncaught exception was thrown. I want my program to have a return of 0 only if it was successful, and non-zero (let’s choose 1) if there is an error I caught and wrote to the Windows Event Log. The major reason I need to make sure the return code is correct is because SQL Server’s CmdExec step looks at the return code to determine if the step succeeded or not which it then affects the overall flow of the later steps and also what the status of the Job is.

Thank goodness there is an easy way to solve this problem if you are writing a C# console application.

Here is a very simple but illustrative example of how to change the return code. No need to set it for success since it will do that for us. We just have to handle the case where we catch the exception and want to have the return code to 1 (indicating an error).

static void Main(string[] args)
{
try
{
// some code here
throw new Exception(“Oh no an error”);
}
catch (Exception ex)
{
// write error to Windows Event log here using your favorite code
…

//Change the return code to 1 since there was an error
System.Environment.ExitCode = 1;
}
}