Error codes for sql server?

[Roy]

Hey all,
I wish to implement a "Try..." block into my web app (which is
basically an editable datagrid) to display appropriate error pages to
my users. Basically, the two main error's are that the request times
out while talking to sql server or that the database admin kills the
process. Does anyone know what error codes those are so that I might
insert them into catch exceptions block or direct me to a
link/reference where they might be located?

Thanks,
Roy

 

[Guest]

If you want to dink with this, try this C# program:

class Class1
{
[STAThread]
static void Main(string[] args)
{
//Blank sa for example only (not a good idea on a production db)
string connString = @"server=(local);database=pubs;UID=sa;PWD=;";
string cmdText = @"UPDATE Authors SET au_lname = au_lname";

SqlConnection conn = new SqlConnection(connString);
SqlCommand cmd = new SqlCommand(cmdText, conn);

//For admin killing process
cmd.CommandTimeout = 0;

//For timeout
cmd.CommandTimeout = 1;

try
{
conn.Open();
cmd.ExecuteNonQuery();
}
catch(Exception ex)
{
//Breakpoint location
string typ = ex.GetType().ToString();
}
finally
{
conn.Dispose();
}
}
}

Before running this program, run the following against the pubs database:

BEGIN TRAN

UPDATE AUTHORs SET au_lname = au_lname
WAITFOR DELAY '00:02:00'
UPDATE AUTHORs SET au_lname = au_lname
COMMIT

What you will find is the same exception is thrown each time:

GetType() = System.Data.SqlClient.SqlException

The difference is the message:

Timeout:
"Timeout expired. The timeout period elapsed prior to completion of the
operation or the server is not responding."

Admin killing process:
"A severe error occurred on the current command. The results, if any,
should be discarded.\r\nThe transaction has been terminated.\r\nThe statement
has been terminated."

You can use the messages, but realize that other fatal errors will cause the
same exception message as an admin killing a process. This is good enough,
however, to pass a message on to the user.

I have not played with inner exceptions to see if there are any in either
case (my guess is no, but it is worth an experiment).

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

***************************
Think Outside the Box!
***************************

 

[Roy]

Thanks for the great assistance Greg, it's appreciated!

As far as an inner exception for the admin killing, nope, there isn't.