Welcome to AspAdvice Sign in | Join | Help

Execute sqlcmd (oSql) from .NET and capture the output

I have recently seen quite a few questions about executing sqlcmd (oSql for those on 2000) from .NET code.  I have a bunch of utilities that i have written that do this and thought I would share the basic code to do it.

You can also easily capture the output as a stream and do whatever you want with it. In the example below I dump it to a file....I also use the -o to output the results of the sql that was excuted.
Just use the RedirectStandardOutput and RedirectStandardError
One caveat is to make sure you do the ReadToEnd before and after the waitforexit(). I had issues where it wouldn't complete and ended up just hanging if I didn't do that.

using (Process proc = new Process())

{

proc.StartInfo.FileName = @"""C:\Program Files\Microsoft SQL Server\90\Tools\Binn\SqlCmd.exe""";
proc.StartInfo.Arguments = @"-Uuser -Ppassword -S" + DestinationServer + " -d" + DestinationDatabase + @" -i""" + SqlToExecute+ @""" -o""" + OutSqlFileName + @"""";
proc.StartInfo.UseShellExecute = false;
proc.StartInfo.RedirectStandardOutput = true;
proc.StartInfo.RedirectStandardError = true;
proc.StartInfo.CreateNoWindow = true;
proc.Start();
System.IO.File.AppendAllText(OutFileName, proc.StandardOutput.ReadToEnd());
proc.WaitForExit();
System.IO.File.AppendAllText(OutFileName, proc.StandardOutput.ReadToEnd());
proc.Close();

}

Let me know if you have any questions or comments on this. 

Share this post: email it! | bookmark it! | digg it! | reddit! | kick it! | live it!
Published Thursday, September 14, 2006 1:28 AM by gstark
Filed under: , ,

Comments

Thursday, May 24, 2007 10:49 AM by MarkCline

# re: Execute sqlcmd (oSql) from .NET and capture the output

I just had to say thanks! For some reason, my process was just hanging when running certain SQL (but was generally working).  Thanks to your help, I finally figured out that it was fixed when I read the standardoutput before WaitForExit.  Lo & Behold - there was already text there.

Thanks!

P.S. I would recommend using WaitForExit(int milliseconds).  That way, if it ever is really hanging, it won't crash your whole app!

Friday, June 27, 2008 8:26 AM by brettemiller

# re: Execute sqlcmd (oSql) from .NET and capture the output

I specifically joined to also say thanks.  That was helpful.  As you suggest, the code can be altered slightly to catch errors (below).

Best,  Brett

using (Process proc = new Process())

{

 string sqlout = "";

 proc.StartInfo.FileName = @"""C:\Program Files\Microsoft SQL Server\90\Tools\Binn\SqlCmd.exe""";

 proc.StartInfo.Arguments = String.Format("-b -U {0} -P {1} -S {2} -d {3} -i \"{4}\"", user, pwd, server, db, file); // -b option forces failed exit status on error

 proc.StartInfo.UseShellExecute = false;

 proc.StartInfo.RedirectStandardOutput = true;

 proc.StartInfo.RedirectStandardError = true;

 proc.StartInfo.CreateNoWindow = true;

 proc.Start();

 sqlout += proc.StandardOutput.ReadToEnd();

 proc.WaitForExit();

 sqlout += proc.StandardOutput.ReadToEnd();

 if (proc.ExitCode > 0)

   throw new Exception(sqlout);

 else if (sqlout.Length > 0)

   Trace.WriteLine(sqlout);

 proc.Close();

}

Anonymous comments are disabled