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;
Let me know if you have any questions or comments on this.