Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 Old Forums
 CLOSED - General SQL Server
 How trap Error Description inside procedure

Author  Topic 

adhik
Yak Posting Veteran

66 Posts

Posted - 2003-07-30 : 21:01:10
Hi,

I am using Cursors in my procedure. I have to do multiple operations on cursor Data and finally store it in different place.

But in between, if Error Occurs how can i trap that message and store same message for error record. I got Error Number. But I dont want system error messages, I need Error Message with objects involed into error.

Thanks in Advance
Adhik

shsmonteiro
Constraint Violating Yak Guru

290 Posts

Posted - 2003-07-31 : 00:02:33
Not sure if this is what you want:

You can use the sp_addmessage to include a custom messsage in the sysmessages table, then use the formatmessage function to pass the object name whenever you find an @@ERROR<>0 condition.


Sérgio Monteiro
Trust in no Oracle
Go to Top of Page

adhik
Yak Posting Veteran

66 Posts

Posted - 2003-07-31 : 00:47:27
Thanks

I want to trap error messages, just you get in VB with err.description.
Here in SQL I get @@Error (Error Number), but from that i wont get any idea which object are involved in the error.

Thanks
Adhik

quote:
Originally posted by shsmonteiro

Not sure if this is what you want:

You can use the sp_addmessage to include a custom messsage in the sysmessages table, then use the formatmessage function to pass the object name whenever you find an @@ERROR<>0 condition.


Sérgio Monteiro
Trust in no Oracle

Go to Top of Page

adhik
Yak Posting Veteran

66 Posts

Posted - 2003-07-31 : 02:46:51
Does SQL Sever (T-SQL used for writing procedures) has any Function/Utility like
On Error Resume Next in VB.

Actally I wann to trap error occured in between porcssing of CURSOR and kept that error in table for futher use like why error occured in peticuler record?

Thanks
Adhik
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-31 : 11:47:53
You need to check for errors after the statement executes. And you need to store the results of the system variables before you do anything else, or they are lost...

I think you're looking for something like:


SELECT @BatchId = Max(BatchId) FROM BatchControl

SELECT @Result_Count = @@ROWCOUNT, @error_out = @@error
If @Error_Out <> 0
BEGIN
Select @Error_Loc = 7
Select @Error_Type = 50001
GOTO isp_FixedWidthFileLoader_Error
END

If @Result_Count = 0
BEGIN
SELECT @Error_Loc = 8
SELECT @Error_Message = 'Max BatchId Not Obtained'
SELECT @Error_Type = 50002
GOTO isp_FixedWidthFileLoader_Error
END




Brett

8-)
Go to Top of Page

adhik
Yak Posting Veteran

66 Posts

Posted - 2003-08-02 : 11:34:17
Thanks a lot,

It works fine, but i get only error number. How can i get exact error description in which complete error is decribed with object involed in the error.

As well after this error i want to continue further excution, so that i can get total failed and total successed records.

Regards
Adhik

quote:
Originally posted by X002548

You need to check for errors after the statement executes. And you need to store the results of the system variables before you do anything else, or they are lost...

I think you're looking for something like:


SELECT @BatchId = Max(BatchId) FROM BatchControl

SELECT @Result_Count = @@ROWCOUNT, @error_out = @@error
If @Error_Out <> 0
BEGIN
Select @Error_Loc = 7
Select @Error_Type = 50001
GOTO isp_FixedWidthFileLoader_Error
END

If @Result_Count = 0
BEGIN
SELECT @Error_Loc = 8
SELECT @Error_Message = 'Max BatchId Not Obtained'
SELECT @Error_Type = 50002
GOTO isp_FixedWidthFileLoader_Error
END




Brett

8-)

Go to Top of Page

shsmonteiro
Constraint Violating Yak Guru

290 Posts

Posted - 2003-08-02 : 16:11:20
There is no way to get customizable errors qithout using Brett function or/and sysmessages table. As I understand your question, you trying to get error messages in the same way SQL Server presents violation of constraints error messages. Well these messages are in the sysmessages and are raised by raiseerror, using formatmessage function.

If you want custom errors descriptions you will have to include it in sysmessages (or use the goto logic by Brett) and, checking by errors (@@ERROR <> 0) after each command, format and raise an error using the message and object name that you has resgistered.

For instance, lets say that you registered the message:

"Table '%s' has no rows", using sp_addmessage and assigned a messagenuber of 50003

then after issue

select * from TabSample

you would check for

@@ROWCOUNT = 0

and issue


the raiserror(50003, 16, 1, 'TabSample')

Would return "Table 'tabSample' has no rows" .

If you do not want use the sysmessages way you can issue the raiserror with a string:


raiserror('Table %s has no rows', 16, 1, 'TabSample')

will return the same error description.

But raiserror will return control for client.



Sérgio Monteiro
Trust in no Oracle
Go to Top of Page

adhik
Yak Posting Veteran

66 Posts

Posted - 2003-08-03 : 02:41:28
Hi shsmonteiro,

But I have one question here. If I trap same errors in my application insted of DB. I get complete error message with DB object involed in it.

e.g. I get this message when i run procedure in Query Analyzer.
INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'FK_tbContacts_tbSites'. The conflict occurred in database 'DEMO', table 'tbSites', column 'SiteId'.

If I trap this message in Front-end, I get full message, but how can I trap same error in procedure itself. I want to do this becasue I want to save this message with record which gives errors, and proceed for further excution, so that later i can get detiled record of error with record which created this error.

If you are still unclear, please let me know.
As well after excuting error record, I dont want to stop further processing (like VB ON ERROR RESUME NEXT works).

Thanks and regards
Adhik
P.S. : Is there any good help available error handling in T-SQL, I didn't get much from what Books Online.


quote:
Originally posted by shsmonteiro

There is no way to get customizable errors qithout using Brett function or/and sysmessages table. As I understand your question, you trying to get error messages in the same way SQL Server presents violation of constraints error messages. Well these messages are in the sysmessages and are raised by raiseerror, using formatmessage function.

If you want custom errors descriptions you will have to include it in sysmessages (or use the goto logic by Brett) and, checking by errors (@@ERROR <> 0) after each command, format and raise an error using the message and object name that you has resgistered.

For instance, lets say that you registered the message:

"Table '%s' has no rows", using sp_addmessage and assigned a messagenuber of 50003

then after issue

select * from TabSample

you would check for

@@ROWCOUNT = 0

and issue


the raiserror(50003, 16, 1, 'TabSample')

Would return "Table 'tabSample' has no rows" .

If you do not want use the sysmessages way you can issue the raiserror with a string:


raiserror('Table %s has no rows', 16, 1, 'TabSample')

will return the same error description.

But raiserror will return control for client.



Sérgio Monteiro
Trust in no Oracle

Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-08-03 : 09:59:41
does this help:

select * from master..sysmessages where error = @SomeErrorNumber

??



- Jeff
Go to Top of Page

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-08-03 : 12:16:43
No, Jeff.
By
select description from master..sysmessages where error=2627
you'll get just this:
Violation of %ls constraint '%.*ls'. Cannot insert duplicate key in object '%.*ls'.

But he wants the real meaningful description text with replaced placeholders:

Violation of PRIMARY KEY constraint 'PK_d'. Cannot insert duplicate key in object 'd'.

- Vit
Go to Top of Page

shsmonteiro
Constraint Violating Yak Guru

290 Posts

Posted - 2003-08-03 : 18:14:25
Hi adhik.

Front end apps are able to get the full description, because SQL engine uses the process I described in my last post to format messages stored in sysmessages table. from BOL:

quote:
All the database APIs, such as ADO, OLE DB, ODBC, DB-Library, and Embedded SQL, report the basic error attributes: the error number and message string. However, there are variations in how many of the other error attributes each database can report.




You can get the same using the process, BUT not issuing the raiserror. Instead format messages and store it to further processing, or use PRINT statement to present the formated message and keep processing.

regards,


Sérgio Monteiro
Trust in no Oracle
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2003-08-04 : 10:29:53
Why is my Dunamic SQL Alarm going off again....

Handle all of your DML via stored procedures and you won't have this problem...




Brett

8-)

SELECT POST=NewId()
Go to Top of Page
   

- Advertisement -