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 AdvanceAdhik |
|
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 MonteiroTrust in no Oracle |
|
|
adhik
Yak Posting Veteran
66 Posts |
Posted - 2003-07-31 : 00:47:27
|
ThanksI 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 Adhikquote: 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 MonteiroTrust in no Oracle
|
|
|
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 likeOn 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 |
|
|
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 Brett8-) |
|
|
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.RegardsAdhikquote: 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 Brett8-)
|
|
|
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 50003then after issue select * from TabSampleyou 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 MonteiroTrust in no Oracle |
|
|
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 regardsAdhikP.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 50003then after issue select * from TabSampleyou 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 MonteiroTrust in no Oracle
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-08-03 : 09:59:41
|
does this help:select * from master..sysmessages where error = @SomeErrorNumber??- Jeff |
|
|
Stoad
Freaky Yak Linguist
1983 Posts |
Posted - 2003-08-03 : 12:16:43
|
No, Jeff.Byselect description from master..sysmessages where error=2627you'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 |
|
|
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 MonteiroTrust in no Oracle |
|
|
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...Brett8-)SELECT POST=NewId() |
|
|
|