Solved

Error message in Stored procedures

Posted on 2001-06-12
8
986 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
Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

 
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 50 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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Can I use dynamic SQL in a function for a COLUMN NAME 6 654
SQL Syntax 6 394
Problems writing binary files to Sybase from PHP 2 183
SQL Query Syntax 17 161
Employees depend heavily on their PCs, and new threats like ransomware make it even more critical to protect their important data.
Data breaches are on the rise, and companies are preparing by boosting their cybersecurity budgets. According to the Cybersecurity Market Report (http://www.cybersecurityventures.com/cybersecurity-market-report), worldwide spending on cybersecurity …
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …

776 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