How to retrieve error when launching sqlcmd from C# ?

Question

Hi all,

I need to run a stored procedure from a C# application.

I use the following code to do so:

Process sqlcmdCall = new Process();
sqlcmdCall.StartInfo.FileName = "sqlcmd.exe";
sqlcmdCall.StartInfo.Arguments = "-S localhost\\SQLEXPRESS -d some_db -Q \":EXIT(sp_test)\""
sqlcmdCall.Start();
sqlcmdCall.WaitForExit();

From the sqlcmdCall object after the call completes, I currently get an ExitCode of -100 for success and of 1 for failure (i.e. missing parameter, stored proc does not exist, etc...).

How can I customize these return codes?

H.

Answer 1

If you are trying to call a stored procedure from c# you would want to use ADO.Net instead of the calling sqlcmd via the command line. Look at SqlConnection and SqlCommand in the System.Data.SqlClient namespace.

Once you are calling the stored procedure via SqlCommand you will be able to catch an exception raised by the stored procedure as well we reading the return value of the procedure if you need to.

Answer 2

Hi, in this case, I do not want to do create a SqlCommand. This is a simple application that runs several scripts and / or maintenance store procedures at installation time. I have no connection string and connect using windows authentication.

Note that the code I posted works fine and really applies to what I am trying to achieve, I just want to add a little error handling in the process.

thanks for your reply,

H.

Answer 3

I have a small VB.Net app that executes system commands like that. To capture error or success conditions I define regular expressions to match the error text output from the command and I capture the output like this:

        myprocess.Start()
        procReader = myprocess.StandardOutput()

        While (Not procReader.EndOfStream)
           procLine = procReader.ReadLine()

           If (MatchesRegEx(errRegEx, procLine)) Then
              writeDebug("Error reg ex: [" + errorRegEx + "] has matched: [" + procLine + "] setting hasError to true.")

              Me.hasError = True
           End If

           writeLog(procLine)
        End While

        procReader.Close()

        myprocess.WaitForExit(CInt(waitTime))

That way I can capture specific errors and also log all the output from the command in case I run across an unexpected error.

Answer 4

@huck Even with windows authentication you can still use SqlCommand and SqlConnection to execute, and you don't have to re-invent the wheel for exception handling.

A simple connection configuration and a single SqlCommand can execute it without issue.

Answer 5

@Matt and @Mitchel Sellers, you guys are right. The hell with sqlcmd, I can indeed call the stored proc in code with proper error handling.

Thanks for your help!

H.