?
Solved

Error message in Stored procedures

Posted on 2001-06-12
8
Medium Priority
?
1,004 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Get MongoDB database support online, now!

At Percona’s web store you can order your MongoDB database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card. Handle your MongoDB database support now!

 
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

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

New style of hardware planning for Microsoft Exchange server.
This month, Experts Exchange’s free Course of the Month is focused on CompTIA IT Fundamentals.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

770 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