Click here to Skip to main content
15,562,222 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I am using ThreadPool to make multiple calls to a method called RunScripts. Number of threads are maintained by a count of number of sql scriptfile to run. If there are 4 sqlscripts to run then I make call to ThreadPool.QueueUserWorkItem 4 times and son on. In the RunScripts method, I am using System.Diagnostic.Process to run the sql scripts as shown is the code. The Process has two event handlers namely OutPutDataReceived and ErrorDataRecieved. In both of the events, I am simply printing the message in console. I have set the ThreadPool.SetMinThreads=4 and ThreadPool.SetMaxThreads=10.

In the code, I have called executorProcess.CancelOutputRead() and executorProcess.CancelErrorRead() as soon as my process finish the work. If I am not wrong the program execution reach these lines only after the two events are called if there are any standard output/error . My scripts finish execution within 3 or 4 seconds. My problem here is OutputDataReceived and ErrorDataReceived events are not called for every threads. Asynchronous reads are being cancelled before the two events are invoked.However, if I increase the MinThreads and MasThreads then the two events are called before the debug point reach CancelOutputRead() and CancelErrorRead().

when I removed the executorProcess.CancelOutputRead() and executorProcess.CancelErrorRead() from the code then every standard ouput and error are being printed. I am not sure why it is not working when there are less number of threads.

Private void RunScripts()

      // Start SQLCMD process.
                ProcessStartInfo psi = new ProcessStartInfo();
                psi.FileName = "SQLCMD.EXE";
                string args = "-S " + this.Server;
                if (string.IsNullOrEmpty(this.UserName)) args += " -E";
                else args += " -U " + this.UserName;
                if (!string.IsNullOrEmpty(scriptFile.Database))
                    args += " -d " + scriptFile.Database;
                if (string.IsNullOrEmpty(this.Password))
                    args += " -i " + scriptFilePath;
                    args += " -P " + this.Password + " -i " + scriptFilePath;
                psi.Arguments = args;
                psi.RedirectStandardOutput = true;
                psi.RedirectStandardError = true;
                psi.UseShellExecute = false;

                using (Process executorProcess = Process.Start(psi))
                    executorProcess.EnableRaisingEvents = true;
                    executorProcess.OutputDataReceived +=
                        new DataReceivedEventHandler(
                    executorProcess.ErrorDataReceived +=
                        new DataReceivedEventHandler(executorProcess_ErrorDataReceived);
                    bool exited = false;
                    while (!exited)
                        exited = executorProcess.WaitForExit(60000);
                        if (!exited && this.StopSignal)
                            try { executorProcess.Kill(); }
                            catch { }
                    catch { }

        void executorProcess_ErrorDataReceived(object sender, DataReceivedEventArgs e)

       private void executorProcess_DataReceived(object sender,
            DataReceivedEventArgs e)
Updated 12-Aug-12 7:19am
Sergey Alexandrovich Kryukov 12-Aug-12 12:25pm    
First of all, what makes you using a separate process executorProcess instead of doing all processing in your process but in different threads. Processes are designed to be isolated first, all communication between then is IPC.
Your general design does not look reasonable to me.
3529255 13-Aug-12 0:27am    
Yes I do know there are different methods provided by Microsoft to execute sql queries like ExecuteNonQuery, ExecuteScalar, ExecuteReader and so on. But I use this only when I have to run specific type of sql queries i.e. when I know whether I am simply running "SELECT" query or "Update" query and so on. And moreover, above methods allow to run single query at a time but I can't do that because I have hundreds of sql queries to run.

Right now I am looking for a solution where I need not know type of queries I am running, I have .sql files with huge number of queries in it and need to run those sql scripts (here scripts could be creating database, storedprocedues,complex T-SQL with logic and whatever) at once. For that there is only one solution to execute sql command in separate process.

Hope you get it what I am saying.
Sergey Alexandrovich Kryukov 13-Aug-12 13:25pm    
As far as I can understand it, it may make sense. But I don't see how it justifies using a separate process. You can have some code directly called from different thread and do exactly the same. With a separate process, you don't win anything (because this is still the same machine with same processor(s)), but you add extra hassles and the overhead of an extra process.

If you are using c# then it would be much better like Sergey said to do all your work in your own application instead of out in another process especially if you want to get the error messages etc.

Try the following :[^]
Share this answer
3529255 13-Aug-12 0:32am    
Mehdi thanx for your reply. The link you have provided is about executing specific sql queries using SMO or SqlClient but that does not meet my requirement. I need to asynchronously call separate process to call SQLCMD.
Mehdi Gholam 13-Aug-12 2:17am    
You can do all that in c# with Task, Threads, etc. so it is essentially the same and you get to handle and catch the errors.
Sergey Alexandrovich Kryukov 13-Aug-12 13:26pm    
Agree. The logic is the same, but overhead of the separate process is not justified. My 5 for the answer.
3529255 14-Aug-12 0:32am    
Sergey I suppose you have never used SQLCMD utility that is why you are asking "Why separate process". SQLCMD is a utility that allows to run T-SQL, system procedures and sql scripts. Now since it is a separate utility like any other Windows application, we have to have another process within our program to execute.
If you are still confused then try to google "Run SQLCMD from c#". You will see every answer will have separate process being used.

Thanx anyways.
Sergey Alexandrovich Kryukov 14-Aug-12 1:59am    
Right, I did not use it. It simply means that SQLCMD is not provided as a library, unfortunately, so, you might have no choice. This is just the utility. When you explained that, I can see the problem. Then, your solution needs to be analyzed...
Please see my last comment to the answer by Mehdi. After we discussed why you need a separate process and I can see why you really might need it, let's see how you do it.

The biggest problem I can see is that you totally block the propagation of exceptions, in two places. You should never do it; instead, you should do it only once, on the very top of stack of every thread. And you should never leave the exception caught on top without any action. You should get exception information and collect it. If this is the UI, you can notify the UI thread on the exceptions. I usually do it using an event declared in a thread wrapper class and handle this even in the UI thread (using UI thread invocation mechanism). In other cases (now you just use the console, but you need to make provisions for other uses of this code), you need to write out exception information and/or log it, for example using System.Information.EventLog.

You need to change this code and see what happens with exceptions.

The termination of the thread is also very questionable. Why would you ever kill the process? This is unsafe. Did you experiment with your code? — does this kill case ever happen? I would never do it; and I would wait for the process until it legitimately terminate itself forever. It should not take infinite time.

And why do you cancel error/output read unconditionally? If the process exit by itself, it is never needed.

Please analyze it and provide some feedback.

Share this answer
3529255 14-Aug-12 8:37am    
Sergey I am glad you finally understood why separate process is required.

Now coming back to your reply, the two unhandled catch blocks are not my concern here because I don't want to handle exceptions in those places.

Regarding the collection of exceptions, Yes I di have handlers which get called whenever there is error. Event handlers "executorProcess_ErrorDataReceived" and "executorProcess_OutputDataReceived" are meant for that. As you can see these two displays errors in console. I have everything you have mentioned.
Sergey Alexandrovich Kryukov 14-Aug-12 11:40am    
If you don't want handle exceptions, don't handle it. What you do is different: you handle it in a quite a specific way, by blocking exception propagation. If this is not your concern, it does not mean you are doing it in a right way. You need to stop blocking this exceptions to find out the root of your problem, first of all. No, by handling these events, you do what I mentioned, but not all of it. You still can suppress some exceptions with blocking their propagation. Remove it to see what happens.

I don't say this will solve your problem itself, but this is one necessary step.
3529255 15-Aug-12 5:18am    
Sergey Yes I understand I should not leave my catch block just like that. Guess what, I have handled those catch blocks but there is no such exception. I know my problem is just either with the Threads or the System.Diagnostic.Process.

I would appreciate if replies are focused on these aspects rather than pointing out the simple code issues which are not related in any ways to the issue I am facing currently.
Sergey Alexandrovich Kryukov 15-Aug-12 13:11pm    
One step at a time, ever heard of it? The issues are related.
pasztorpisti 14-Aug-12 14:16pm    
+5 blocking exception propagation is one of the biggest guilts. the god of coders will punish for it. makes it impossible to find bugs in an otherwise excellent environment.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900