Improve company productivity with a Business Account.Sign Up

x
?
Solved

Error message in Stored procedures

Posted on 2001-06-12
8
Medium Priority
?
1,043 Views
Last Modified: 2008-02-01
How can I get the description of the sybase error in the stored procedure. For example, if I am trying to insert a record in a table and duplicate key condition occurs then how can I get the description of the error that 'Duplicate key condition occured in <table_name>' in thestore procedure itself?
0
Comment
Question by:AjayG
8 Comments
 
LVL 5

Expert Comment

by:amitpagarwal
ID: 6180998
hope this helps.

create proc ..

begin

declare @msg char(80)

..some sql ....

INSERT INTO ....

IF(@@ERROR != 0 )
BEGIN
SELECT @msg = "Duplicate key ..."
GOTO Error_exit
END

... some SQL ...

RETURN 0

Error_exit:

RAISERROR @msg
RETURN -1

END
0
 
LVL 5

Expert Comment

by:amitpagarwal
ID: 6181012
You can even derive some info from this comment.

sp_addmessage 25001,"There is already a remote user named '%1!' for remote server '%2!'."

raiserror 25001, jane, myserver

This example adds a message to sysusermessages and then tests the message with raiserror, providing the substitution arguments.

Thanks,
Amit Agarwal.
0
 

Author Comment

by:AjayG
ID: 6181601
Thank you amit for your reply. But what I am looking for is how can i get the sybase's own error message corresponding to the @@error value. I do not  want to generate my own message but just print out the sybase error message corresponding to @@error value.

By your method my program will have to know what value of @@error corresponds to which error (like in the example you have given I have to know that certain value of @@error means duplicate key).
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
LVL 5

Expert Comment

by:amitpagarwal
ID: 6181627
You may use master..sysmessages to find the description of the sybase error.

Thanks.
0
 

Author Comment

by:AjayG
ID: 6181666
master..sysmessages contains message templates (like you had mentioned in your earlier comment (,"duplicate key error occured while inserting record into '%1!'"). Where will I get the complete sybase message with all the substitutions done.
0
 
LVL 5

Expert Comment

by:amitpagarwal
ID: 6181753
just thinking .. maybe sp_getmessage should solve your purpose, you can get the errror in a variable.
0
 
LVL 3

Expert Comment

by:ahoor
ID: 6187209
I don't think it's possible, since the substitution is done in a raiserror and you can not trap it anymore then... but it's interesting.

sp_getmessage does nothing more then a select from sysusermessages, that won't work I guess...
0
 
LVL 10

Accepted Solution

by:
bret earned 150 total points
ID: 6187257
From within TSQL, there is no way to get at the error message itself (i.e you cannot tell what the
substituted table names were).  An Open Client client can parse the error message string.

You may be able to figure out what the substituted information was from your context, i.e. if you
do an insert into mytable and get this error, well, you know which table you were just inserting into.

There are a couple  long-open feature requesst at Sybase, CRs 58371 and 31148, for this ability.
You can improve the chances they might be acted upon by promoting them through the feature request
ranking system at the International Sybase User's Group website (http://www.isug.com)

CR 31148: Wants the ability to access error message text (master..sysmessages) from a previous raise error command.  Perferably as a global variable.

CR 58371: Would like a way to get error message paramters from within TSQL.  Example: 1105 occurs, can get 1105 from @@error, but would like to get the dbname parameter.
 
-bret
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Lotus Notes is the most prominent choice of all users due to its advance email management. It provides email features along with contact management, appointments, task, calendar etc. Many users rely on its service to carry out electronic communicati…
How to Import Outlook PST file to Exchange Server Mailbox without Powershell and Exchange Admin Center. Use SysTools Exchange Import Tool to Move PST file in Exchange 2016 / 13 / 10/ 07 Server Mailbox including Contacts, Calendar, Task and journal d…
Watch the video to know how one can repair corrupt Exchange OST file effortlessly and convert OST emails to MS Outlook PST file format by using Kernel for OST to PST converter tool. It can convert OST to MSG, MBOX, EML to access them. It can migrate…
Watch the software video of Kernel Import PST to Office 365 tools which can easily import PST and OST files to Office 365 for bulk mailboxes. The process of migration is simple and user can map source and destination mailboxes and easily import data…

579 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question