Reading the custom error messages from SQL

[Mihaly]

I have a stored procedure in SQL Server 2000, and I want to read the error
messages from this stored procedure.
Please help me for this question:

This is the stored procedure. Please supose than Column2 has no 0 values:

CREATE PROCEDURE dbo.ErrorTest
AS
SELECT Column1 FROM Table WHERE Column2 = 0
IF (@@ROWCOUNT <= 0) GOTO RollB
RETURN 1

RollB:
RAISERROR('Error in the procedure...', 16, 1)
IF (@@TRANCOUNT > 0)
ROLLBACK TRANSACTION
RETURN 0
GO

This is the procedure in the client application:

SqlConnection MyConnection = new SqlConnection();
SqlCommand cmd = new SqlCommand("EXEC ErrorTest", MyConnection);
SqlDataReader dr = null;
try
{
MyConnection.Open();
cmd.Transaction =
cmd.Connection.BeginTransaction(IsolationLevel.Rea dCommitted);
dr = cmd.ExecuteReader();
if (dr.HasRows)
if (dr.read())
MyValue = dr["Camp1"];
dr.Close();
cmd.Transaction.Commit();
MyConnection.Close();
}
catch (Exception ex)
{
try
{
cmd.Transaction.Rollback();
}
catch
{
}
string errMessage = "";
for( Exception tempException = ex; tempException != null ; tempException
= tempException.InnerException )
errMessage += tempException.Message + Environment.NewLine +
Environment.NewLine;
MessageBox.Show(errMessage, "Error", MessageBoxButtons.OK,
MessageBoxIcon.Error);
}

In this example the stored procedure returns the error message, but the
client applicatin isn't show it. The client applicatin showes an other error
message: The COMMIT TRANSACTION request has no corresponding BEGIN
TRANSACTION.
Please tell me:
1. How can I read in C# than the transaction was closed in the stored
procedure?
2. How can I show the error message from the stored procedure in the client
application?

Thank you!

[Yunus Emre ALPÖZEN [MCAD.NET]]

you should use transaction either in a stored procedure or in ADO. After
fixing this u should see that u receive error message

--

Thanks,
Yunus Emre ALPÖZEN
BSc, MCAD.NET

"Mihaly" <Mi****@discussions.microsoft.com> wrote in message
news:8F**********************************@microsof t.com...

I have a stored procedure in SQL Server 2000, and I want to read the error
messages from this stored procedure.
Please help me for this question:

This is the stored procedure. Please supose than Column2 has no 0 values:

CREATE PROCEDURE dbo.ErrorTest
AS
SELECT Column1 FROM Table WHERE Column2 = 0
IF (@@ROWCOUNT <= 0) GOTO RollB
RETURN 1

RollB:
RAISERROR('Error in the procedure...', 16, 1)
IF (@@TRANCOUNT > 0)
ROLLBACK TRANSACTION
RETURN 0
GO

This is the procedure in the client application:

SqlConnection MyConnection = new SqlConnection();
SqlCommand cmd = new SqlCommand("EXEC ErrorTest", MyConnection);
SqlDataReader dr = null;
try
{
MyConnection.Open();
cmd.Transaction =
cmd.Connection.BeginTransaction(IsolationLevel.Rea dCommitted);
dr = cmd.ExecuteReader();
if (dr.HasRows)
if (dr.read())
MyValue = dr["Camp1"];
dr.Close();
cmd.Transaction.Commit();
MyConnection.Close();
}
catch (Exception ex)
{
try
{
cmd.Transaction.Rollback();
}
catch
{
}
string errMessage = "";
for( Exception tempException = ex; tempException != null ; tempException
= tempException.InnerException )
errMessage += tempException.Message + Environment.NewLine +
Environment.NewLine;
MessageBox.Show(errMessage, "Error", MessageBoxButtons.OK,
MessageBoxIcon.Error);
}

In this example the stored procedure returns the error message, but the
client applicatin isn't show it. The client applicatin showes an other
error
message: The COMMIT TRANSACTION request has no corresponding BEGIN
TRANSACTION.
Please tell me:
1. How can I read in C# than the transaction was closed in the stored
procedure?
2. How can I show the error message from the stored procedure in the
client
application?

Thank you!