SQL Server Performance Forum – Threads Archive

To handle errors. Help!

Hello! I need to know which is the indicator indeed that allows me to know if the action in my stored procedures has led to end correctly or has been occurred some problem. Then pass this value with a variable to my application to decide what to do about it. Now I use the function @@ERROR to know the value of error, but how can I decide on my application if the action has been led to end correctly? Many people say that if the error number is different to 0 an error has occurred, but I can not say to my application to show a message if this value is different to 0, because there are many errors are only informative, and the action has been done perfectly. I don#%92t understand anything!! In the other hand those numbers by itself not indicate anything, I think that the true indicator is the severity associated with those numbers. In the other hand I cannot understand why all of error numbers are duplicated two times in the table sysmessages. What can I do? Another question is: The errors that occur in SQL remain registered in some place? I would like be able to register it to review it later, if it is not like that, how can I know if the errors has occurred?
Thank you,
Cesar

Firstly, the most reliable method to catch a database error is function @@ERROR. Also if you are using VB / VC / ASP etc, the database exception is thrown to the application in case of error. You can trapm the exception in the application and thus you’ll know if your action was a success or not. The numbers have a definate mapping to the sysmessages table in the master database unless the error no.’s are user generated numbers. In case they are, there has to be a single entry for each user gebnerated error in sysmessages althogh this is not mendatory. If the error no. does not have an entry, the error itself means nothing. I don’t know how there are multiple messages in sysmessages table for same error no. The application errors are not logged anywhere implicitly. However for reviewing the same, you can log them into SQL Server error log and Windows Error Log using XP_LOGEVENT or if the error is raised using RAISERROR function, you can use the WITH LOG option. Althogh I won’t recommend this as this will lead to filling up of error log pretty quickly. HTH. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

Ok gaurav, but, what numbers exactly means if the action of stored procedure has been done correctly or not?. I do not want that my users see errors that they don’t have to solve, user only need to know if their action has been done correctly, and if it isn’ t, try again. @@ERROR <> 0 not indicate if the action has been done correctly, only indicate that some error has occurred, and if this error has not affected the action I don#%92t want user see this error because is my problem.
Here is an example of one of my Stored Procedures: CREATE PROCEDURE check_mail_password_inscription
@E-mail varchar(50), @Password char(10),
@Offer_num int, @Date datetime, @Motives_interest varchar(250)
As
Declare @Buyer_num int If Exists(select E-mail from Buyers where E-mail = @E-mail
and Password = @Password)
begin select @Buyer_num = Buyer_num
from Buyers
where E-mail = @E-mail
and Password = @Password select @Buyer_num AS Buyer_num, 1 AS Value
INSERT INTO Inscriptions_in_offers (Offer_num, Buyer_num, Date, Motives_interest)
VALUES (@Offer_num, @Buyer_num, @Date, @Motives_interest)
end
else
begin
select 0 AS Value
end

Foor this u’ll have to do all the changes in the database using transactions. As soon as you encounter an error, you should catch that using @@error and database exceptions as I mentioned before. After this you should rollback the transaction. An error generated at frontend need not be displayed to the user. The application can be made intelligent enogh to submit the query / transaction again. I would write the given stored procedure as
CREATE PROCEDURE check_mail_password_inscription
@E-mail varchar(50), @Password char(10),
@Offer_num int, @Date datetime, @Motives_interest varchar(250)
As
Declare @Buyer_num int,
@Error int If Exists(select E-mail from Buyers where E-mail = @E-mail
and Password = @Password)
begin select @Buyer_num = Buyer_num
from Buyers
where E-mail = @E-mail
and Password = @Password select @Buyer_num AS Buyer_num, 1 AS Value BEGIN TRANSACTION INSERT INTO Inscriptions_in_offers (Offer_num, Buyer_num, Date, Motives_interest)
VALUES (@Offer_num, @Buyer_num, @Date, @Motives_interest) SELECT @Error = @@ERROR IF @Error <> 0
BEGIN
ROLLBACK TRANSACTION
RETURN @Error
END ELSE
COMMIT TRANSACTION
RETURN 0
END
end
else
begin
select 0 AS Value
end
This way you can check for the return value of the stored procedure to see if the transaction was successful or not. If the fron end does not support return values from stored procedures, you can select the @Error variable in the resultset. HTH. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

Ok. Let me review the steps to check if I understand all the process. 1.My web application (ASP VBScript) calls my SP (stored procedure) on SQL Server. 2.If the procedure send an error number @Error <> 0, the SP Rollback the transaction (finish the transaction?), then my application execute/call again the SP. And this way indefinitely? Up to when? 3.On the second intention of the application, if @Error = 0 is returned, then my application show a message defined by me, for example ‘the transaction was successful. Congratulations’. And the SP commits the transaction.
Is the process like that? Correct me please if it is necessary.
That would happen if it I were not making serve all that? Now my application works perfect Thank you!
Cesar

quote:Originally posted by Cesar That would happen if it I were not making serve all that? Now my application works perfect

I don’t understand this statement of yours. You have got pretty close to what I was looking at…. Some comments Step 2: Rollback transaction means the application will behave as if all the stps within the BEGIN and ROLLBACK statments never executed. Generally the application is developed to show the error to the user. Now in this case since we don’t want this, I would like you to log the same for troubleshooting later. Also a counter should be kept for no. of attempts so that after a ceratin no. of attemps error is displayed and the user can call a support personel to correct the problem. This will be helpful in the cases where there is a global problem and u won’t be flooded with application requests and consequent errors. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

Don’t worry about my last statment <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />, my english isn’t very good. Ok gaurav. It#%92 s a good idea for a global problem. When you can, would you mind give me an example of a counter in my case? And the corresponding conclusion/ending ? This counter has to be written in the SP or in the web page? Now I#%92 m going to gym, I#%92 ll check the forum later. Thank you vm, Cesar

Just came across an article on Error handling in Nested Stored Procedures- http://www.devx.com/codemag/Article/16120 Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

Ok gaurav, thank you very much [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />]. Cesar

]]>