Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

How to access the actual error message text from sp?

3 posts in General Discussion Last posting was on 2009-11-19 19:35:05.0Z
Leonid Gvirtz Posted on 2009-11-19 08:54:31.0Z
Sender: 6c89.4b04f6d9.1804289383@sybase.com
From: Leonid Gvirtz
Newsgroups: sybase.public.ase.general
Subject: How to access the actual error message text from sp?
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4b0507c6.6f42.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 19 Nov 2009 00:54:31 -0800
X-Trace: forums-1-dub 1258620871 10.22.241.41 (19 Nov 2009 00:54:31 -0800)
X-Original-Trace: 19 Nov 2009 00:54:31 -0800, 10.22.241.41
Lines: 39
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28650
Article PK: 77892

Hi

I have a question that looks very simple, but I have not
succeeded to find an answer on it so far. I need to access
the actual error message text inside a stored procedure in
order to process it and log into a table. While there is no
problem to access the error code via @@error variable and
then get the error message template from sysmessages table,
I don't know how to assign the actual error message to a
variable.

For example:

create table test_errm (fid int, fname varchar(30))
go
alter table test_errm add constraint pk_test_errm primary
key (fid)
go
insert into test_errm values (1, 'test_1')
go
insert into test_errm values (1, 'test_1')
go

2> Msg 2601, Level 14, State 1:
Server 'DS_01_4K', Line 1:
Attempt to insert duplicate key row in object 'test_errm'
with unique index 'pk_test_errm'
Command has been aborted.

Here, the value of @@error will be 2601, and the message
from sysmessages will be "Attempt to insert duplicate key
row in object '%.*s' with unique index '%.*s'%S_EED". The
question is: how to retrieve the actual error message, with
the real table name and index name, into a variable?

I'm looking for solution for ASE 12.5.3.

Thanks in advance
Leonid Gvirtz


J Posted on 2009-11-19 16:28:35.0Z
From: jtotally_bogus@sbcglobal.net (J)
Newsgroups: sybase.public.ase.general
Subject: Re: How to access the actual error message text from sp?
Reply-To: J@bogusemailAddress.com
Message-ID: <4b0570fb.1858422@forums.sybase.com>
References: <4b0507c6.6f42.1681692777@sybase.com>
X-Newsreader: Forte Free Agent 1.21/32.243
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 19 Nov 2009 08:28:35 -0800
X-Trace: forums-1-dub 1258648115 10.22.241.152 (19 Nov 2009 08:28:35 -0800)
X-Original-Trace: 19 Nov 2009 08:28:35 -0800, vip152.sybase.com
Lines: 59
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28653
Article PK: 77893

On 19 Nov 2009 00:54:31 -0800, Leonid Gvirtz wrote:

Difficult one.

The error message with the template completed via the server flows
back to the actual client and not the stored procedure. If you need
to see the actual message contents then I think you would have to have
logic in the stored procedure to know the object that you deal with
and complete the template from sysmessages accordingly. In your
example here it is not that hard but if it were dynamic sql or
triggers eg. then it could be very hard.

Other approaches would include reading the ASE errrorlog for messages
which flow to the client and are logged to the errorlog.

Perhaps someone else has a better technique. Sorry.

Jay

>Hi
>
>I have a question that looks very simple, but I have not
>succeeded to find an answer on it so far. I need to access
>the actual error message text inside a stored procedure in
>order to process it and log into a table. While there is no
>problem to access the error code via @@error variable and
>then get the error message template from sysmessages table,
>I don't know how to assign the actual error message to a
>variable.
>
>For example:
>
>create table test_errm (fid int, fname varchar(30))
>go
>alter table test_errm add constraint pk_test_errm primary
>key (fid)
>go
>insert into test_errm values (1, 'test_1')
>go
>insert into test_errm values (1, 'test_1')
>go
>
>2> Msg 2601, Level 14, State 1:
>Server 'DS_01_4K', Line 1:
>Attempt to insert duplicate key row in object 'test_errm'
>with unique index 'pk_test_errm'
>Command has been aborted.
>
>Here, the value of @@error will be 2601, and the message
>from sysmessages will be "Attempt to insert duplicate key
>row in object '%.*s' with unique index '%.*s'%S_EED". The
>question is: how to retrieve the actual error message, with
>the real table name and index name, into a variable?
>
>I'm looking for solution for ASE 12.5.3.
>
>Thanks in advance
>Leonid Gvirtz


Bret Halford Posted on 2009-11-19 19:35:05.0Z
From: Bret Halford <bret@sybase.com>
User-Agent: Thunderbird 2.0.0.23 (Windows/20090812)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: How to access the actual error message text from sp?
References: <4b0507c6.6f42.1681692777@sybase.com> <4b0570fb.1858422@forums.sybase.com>
In-Reply-To: <4b0570fb.1858422@forums.sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4b059de9@forums-1-dub>
Date: 19 Nov 2009 11:35:05 -0800
X-Trace: forums-1-dub 1258659305 10.22.241.152 (19 Nov 2009 11:35:05 -0800)
X-Original-Trace: 19 Nov 2009 11:35:05 -0800, vip152.sybase.com
Lines: 68
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28655
Article PK: 77899

I concur with J.
There is an open feature request (CR 296395) for something like an
@@lasterrmessage session variable, but the functionality
certainly doesn't currently exist.

-bret

J wrote:
> On 19 Nov 2009 00:54:31 -0800, Leonid Gvirtz wrote:
>
> Difficult one.
>
> The error message with the template completed via the server flows
> back to the actual client and not the stored procedure. If you need
> to see the actual message contents then I think you would have to have
> logic in the stored procedure to know the object that you deal with
> and complete the template from sysmessages accordingly. In your
> example here it is not that hard but if it were dynamic sql or
> triggers eg. then it could be very hard.
>
> Other approaches would include reading the ASE errrorlog for messages
> which flow to the client and are logged to the errorlog.
>
> Perhaps someone else has a better technique. Sorry.
>
> Jay
>
>> Hi
>>
>> I have a question that looks very simple, but I have not
>> succeeded to find an answer on it so far. I need to access
>> the actual error message text inside a stored procedure in
>> order to process it and log into a table. While there is no
>> problem to access the error code via @@error variable and
>> then get the error message template from sysmessages table,
>> I don't know how to assign the actual error message to a
>> variable.
>>
>> For example:
>>
>> create table test_errm (fid int, fname varchar(30))
>> go
>> alter table test_errm add constraint pk_test_errm primary
>> key (fid)
>> go
>> insert into test_errm values (1, 'test_1')
>> go
>> insert into test_errm values (1, 'test_1')
>> go
>>
>> 2> Msg 2601, Level 14, State 1:
>> Server 'DS_01_4K', Line 1:
>> Attempt to insert duplicate key row in object 'test_errm'
>> with unique index 'pk_test_errm'
>> Command has been aborted.
>>
>> Here, the value of @@error will be 2601, and the message
>>from sysmessages will be "Attempt to insert duplicate key
>> row in object '%.*s' with unique index '%.*s'%S_EED". The
>> question is: how to retrieve the actual error message, with
>> the real table name and index name, into a variable?
>>
>> I'm looking for solution for ASE 12.5.3.
>>
>> Thanks in advance
>> Leonid Gvirtz
>