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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 How trap Error Description inside procedure
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

adhik
Yak Posting Veteran

India
66 Posts

Posted - 07/30/2003 :  21:01:10  Show Profile  Send adhik an AOL message  Send adhik a Yahoo! Message
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

Brazil
290 Posts

Posted - 07/31/2003 :  00:02:33  Show Profile  Send shsmonteiro an ICQ Message
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

India
66 Posts

Posted - 07/31/2003 :  00:47:27  Show Profile  Send adhik an AOL message  Send adhik a Yahoo! Message
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

India
66 Posts

Posted - 07/31/2003 :  02:46:51  Show Profile  Send adhik an AOL message  Send adhik a Yahoo! Message
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 - 07/31/2003 :  11:47:53  Show Profile
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

India
66 Posts

Posted - 08/02/2003 :  11:34:17  Show Profile  Send adhik an AOL message  Send adhik a Yahoo! Message
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

Brazil
290 Posts

Posted - 08/02/2003 :  16:11:20  Show Profile  Send shsmonteiro an ICQ Message
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

India
66 Posts

Posted - 08/03/2003 :  02:41:28  Show Profile  Send adhik an AOL message  Send adhik a Yahoo! Message
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


Edited by - adhik on 08/03/2003 02:44:16
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 08/03/2003 :  09:59:41  Show Profile  Visit jsmith8858's Homepage
does this help:

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

??



- Jeff
Go to Top of Page

Stoad
Freaky Yak Linguist

*
1983 Posts

Posted - 08/03/2003 :  12:16:43  Show Profile  Visit Stoad's Homepage
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

Brazil
290 Posts

Posted - 08/03/2003 :  18:14:25  Show Profile  Send shsmonteiro an ICQ Message
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 - 08/04/2003 :  10:29:53  Show Profile
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
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000