470,594 Members | 1,512 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,594 developers. It's quick & easy.

Reading the custom error messages from SQL

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!

Nov 17 '05 #1
1 2463
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!

Nov 17 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by Lee | last post: by
21 posts views Thread by JoKur | last post: by
16 posts views Thread by Bret Pehrson | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.