SQL Run EXE works. Hangs when Exe starts another Exe

Question

I am running an executable from SQL, using SQL Server Agent. Also have used xp_cmdshell for exact same result.

The executable is written in C#. Last step is to log the success so I can verify.

When the executable runs and only does internal processes (updates SQL, gets data from internet), it succeeds and updates SQL and SQL Server Agent (the job) reports success. The problem is when the executable program runs ANOTHER executable.

In debugging mode in Visual Studio it works and calls the 2nd program, it completes its work, then 1st program continues. In deployment, it hangs when it calls the 2nd program and never completes. If I remove the 2nd program call from program #1, it succeeds. As soon as I add in the call to the 2nd program, it hangs indefinitely.

This is the method for calling the 2nd program.

static void RunProgramImageUploadAwsS3(int itemId, string fileName = "", string folderName = "")
        {
            try
            {
                string programPath = @"\\SVR\VS Projects\AWS_S3_Upload_Image\AWS_S3_Upload_Image.exe";
                string programArgs = itemId.ToString() + ((fileName == "") ? "" : " " + fileName) + ((folderName == "") ? "" : " " + folderName);
                using (Process exeProcess = Process.Start(programPath, programArgs))
                {
                    exeProcess.WaitForExit();
                }
            }
            catch (Exception e)
            {
                LogError("Method: RunProgramImageUploadAwsS3", e.Message.ToString());
            }
        }

My reason for calling a 2nd program is it is used for other parts of the work flow. I could copy all it's contents into program #1, but now I have duplicate program parts, if any changes both have to be changed, etc.

I do not know the reason for the 2nd program failing when called from SQL, nor can I find any details online. It flows best this way as I can feed program #2 the item I am working with as program #1 works on it.

Here is the SQL xp_cmdshell code:

declare @cmd varchar(500) = '"\\SVR\VS Projects\ItemData\ItemData.exe"'
EXECUTE master..xp_cmdshell @cmd

Might be linked to this: https://stackoverflow.com/questions/439617/hanging-process-when-run-with-net-process-start-whats-wrong — sr28, May 12 '20 at 16:29
@sr28 That lead me to the correct answer. I do not know why, but adding EnableRaisingEvents = false; / .StartInfo.UseShellExecute = false; / StartInfo.RedirectStandardOutput = true; to Process has it functioning. If anyone can shed light on why that would be great. Your link had someone with an answer that included using those. If you want to post it as an answer I'll accept it, it fixed it for me and now SQL can run the exe, which in turn runs the 2nd exe, and SQL knows when completed. — MikeCxt, May 12 '20 at 17:27

score 1 · Accepted Answer · answered May 13 '20 at 07:00

As per your comment it sounds like you were able to fix it by setting the following as per Justin Tanner's answer in this question:

Process command = new Process();
command.EnableRaisingEvents = false;
command.StartInfo.UseShellExecute = false;
command.StartInfo.RedirectStandardOutput = true;

This may well have worked for you as your stopping it from now raising the 'Exited' event as per msdn. RedirectStandardOutput being set to true means your no longer using the standard output stream (normally writing to the console). As such, you've suppressed it. The UseShellExecute has to be false for this to work. For more info see here.

Thank you for explaining the different options, that helps my understanding a lot. And of course for the initial find of the other thread where Justin had solved a related but slightly different issue. I did not have to use his main fix, but after looking at his option setting and trying them, I found the resolution that you have listed here. — MikeCxt, May 13 '20 at 14:58

SQL Run EXE works. Hangs when Exe starts another Exe

1 Answers1