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.

Capturing the error message text in a proc

6 posts in General Discussion Last posting was on 2011-12-30 21:14:54.0Z
Mark Posted on 2011-12-30 13:44:16.0Z
From: Mark <mlibner@yahoo.com>
Reply-To: mlibner@yahoo.com
Organization: Security Finance
User-Agent: Mozilla/5.0 (Windows NT 5.1; rv:8.0) Gecko/20111105 Thunderbird/8.0
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Capturing the error message text in a proc
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: <4efdc030$1@forums-1-dub>
Date: 30 Dec 2011 05:44:16 -0800
X-Trace: forums-1-dub 1325252656 10.22.241.152 (30 Dec 2011 05:44:16 -0800)
X-Original-Trace: 30 Dec 2011 05:44:16 -0800, vip152.sybase.com
Lines: 12
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30793
Article PK: 73683

Hi All,

This may be a dumb question but I didn't see an obvious answer.

Is there an elegant way of capturing the error message associated with
the error code in @@error in a stored proc? I would like to capture this
and write to a table in the db in the error handling section of my procs.

thanks,
Mark

ASE 15.5


"Mark A. Parsons" <iron_horse Posted on 2011-12-30 16:04:14.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.13) Gecko/20101207 Lightning/1.0b2 Thunderbird/3.1.7
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Capturing the error message text in a proc
References: <4efdc030$1@forums-1-dub>
In-Reply-To: <4efdc030$1@forums-1-dub>
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: <4efde0fe@forums-1-dub>
Date: 30 Dec 2011 08:04:14 -0800
X-Trace: forums-1-dub 1325261054 10.22.241.152 (30 Dec 2011 08:04:14 -0800)
X-Original-Trace: 30 Dec 2011 08:04:14 -0800, vip152.sybase.com
Lines: 19
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30796
Article PK: 73686

Not that I'm aware of.

You could try putting a proxy table on the front of the stored proc to see if the error message can be picked up as a
textual result set (from the proxy). (NOTE: I don't recall if this will work or not.) (NOTE: Obviously (?) this would
incur a good bit of overhead for the CIS call as well as the processing of the output from the stored proc.) (NOTE:
Obviously (?) if this works you would be working from outside the proc as opposed to within the proc as you envision.)

On 12/30/2011 08:44, Mark wrote:
> Hi All,
>
> This may be a dumb question but I didn't see an obvious answer.
>
> Is there an elegant way of capturing the error message associated with the error code in @@error in a stored proc? I
> would like to capture this and write to a table in the db in the error handling section of my procs.
>
> thanks,
> Mark
>
> ASE 15.5


Mark Posted on 2011-12-30 17:49:33.0Z
From: Mark <mlibner@yahoo.com>
Reply-To: mlibner@yahoo.com
Organization: Security Finance
User-Agent: Mozilla/5.0 (Windows NT 5.1; rv:8.0) Gecko/20111105 Thunderbird/8.0
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Capturing the error message text in a proc
References: <4efdc030$1@forums-1-dub> <4efde0fe@forums-1-dub>
In-Reply-To: <4efde0fe@forums-1-dub>
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: <4efdf9ad@forums-1-dub>
Date: 30 Dec 2011 09:49:33 -0800
X-Trace: forums-1-dub 1325267373 10.22.241.152 (30 Dec 2011 09:49:33 -0800)
X-Original-Trace: 30 Dec 2011 09:49:33 -0800, vip152.sybase.com
Lines: 53
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30797
Article PK: 73687

Hi Mark,

Would sp_getmessage get me what I'm after?

Here's the idea only I would call sp_getmessage from the proc that
actually inserts the record into my error table by using the passed in
error code.

/*-------------------------------------------------------------------
This jump is triggered whenever an update error is encountered
-------------------------------------------------------------------*/
update_error:
DECLARE @li_error int,
@ls_error_msg varchar(255)

select @li_error = @@error

execute sp_getmessage @ai_error_code,@ls_error_msg output

... continue error handling with error code and message


Thanks,
Mark

On 12/30/2011 11:04 AM, Mark A. Parsons wrote:
> Not that I'm aware of.
>
> You could try putting a proxy table on the front of the stored proc to
> see if the error message can be picked up as a textual result set
> (from the proxy). (NOTE: I don't recall if this will work or not.)
> (NOTE: Obviously (?) this would incur a good bit of overhead for the
> CIS call as well as the processing of the output from the stored
> proc.) (NOTE: Obviously (?) if this works you would be working from
> outside the proc as opposed to within the proc as you envision.)
>
> On 12/30/2011 08:44, Mark wrote:
>> Hi All,
>>
>> This may be a dumb question but I didn't see an obvious answer.
>>
>> Is there an elegant way of capturing the error message associated
>> with the error code in @@error in a stored proc? I
>> would like to capture this and write to a table in the db in the
>> error handling section of my procs.
>>
>> thanks,
>> Mark
>>
>> ASE 15.5


"Mark A. Parsons" <iron_horse Posted on 2011-12-30 17:58:51.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.13) Gecko/20101207 Lightning/1.0b2 Thunderbird/3.1.7
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Capturing the error message text in a proc
References: <4efdc030$1@forums-1-dub> <4efde0fe@forums-1-dub> <4efdf9ad@forums-1-dub>
In-Reply-To: <4efdf9ad@forums-1-dub>
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: <4efdfbdb@forums-1-dub>
Date: 30 Dec 2011 09:58:51 -0800
X-Trace: forums-1-dub 1325267931 10.22.241.152 (30 Dec 2011 09:58:51 -0800)
X-Original-Trace: 30 Dec 2011 09:58:51 -0800, vip152.sybase.com
Lines: 59
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30798
Article PK: 73688

Have you tried it? When in doubt, try it out!

If you have pre-canned messages, without wildcard/parameters (eg, %1!), stored in sysmessages then sp_getmessage will
probably suffice.

In all other cases sp_getmessage will probably be of little/no benefit (eg, it won't capture the values passed into
wildcard/parameter positions (eg, %1!); on-the-fly/dynamic error messages aren't stored in sysmessages so nothing for
sp_getmessage to grab hold of).

On 12/30/2011 12:49, Mark wrote:
> Hi Mark,
>
> Would sp_getmessage get me what I'm after?
>
> Here's the idea only I would call sp_getmessage from the proc that actually inserts the record into my error table by
> using the passed in error code.
>
> /*-------------------------------------------------------------------
> This jump is triggered whenever an update error is encountered
> -------------------------------------------------------------------*/
> update_error:
> DECLARE @li_error int,
> @ls_error_msg varchar(255)
>
> select @li_error = @@error
>
> execute sp_getmessage @ai_error_code,@ls_error_msg output
>
> ... continue error handling with error code and message
>
>
> Thanks,
> Mark
>
>
>
>
> On 12/30/2011 11:04 AM, Mark A. Parsons wrote:
>> Not that I'm aware of.
>>
>> You could try putting a proxy table on the front of the stored proc to see if the error message can be picked up as a
>> textual result set (from the proxy). (NOTE: I don't recall if this will work or not.) (NOTE: Obviously (?) this would
>> incur a good bit of overhead for the CIS call as well as the processing of the output from the stored proc.) (NOTE:
>> Obviously (?) if this works you would be working from outside the proc as opposed to within the proc as you envision.)
>>
>> On 12/30/2011 08:44, Mark wrote:
>>> Hi All,
>>>
>>> This may be a dumb question but I didn't see an obvious answer.
>>>
>>> Is there an elegant way of capturing the error message associated with the error code in @@error in a stored proc? I
>>> would like to capture this and write to a table in the db in the error handling section of my procs.
>>>
>>> thanks,
>>> Mark
>>>
>>> ASE 15.5


Mark Posted on 2011-12-30 18:31:35.0Z
From: Mark <mlibner@yahoo.com>
Reply-To: mlibner@yahoo.com
Organization: Security Finance
User-Agent: Mozilla/5.0 (Windows NT 5.1; rv:8.0) Gecko/20111105 Thunderbird/8.0
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Capturing the error message text in a proc
References: <4efdc030$1@forums-1-dub> <4efde0fe@forums-1-dub> <4efdf9ad@forums-1-dub> <4efdfbdb@forums-1-dub>
In-Reply-To: <4efdfbdb@forums-1-dub>
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: <4efe0387@forums-1-dub>
Date: 30 Dec 2011 10:31:35 -0800
X-Trace: forums-1-dub 1325269895 10.22.241.152 (30 Dec 2011 10:31:35 -0800)
X-Original-Trace: 30 Dec 2011 10:31:35 -0800, vip152.sybase.com
Lines: 84
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30799
Article PK: 73689

I've wired it in I just haven't exercised it. I tried misspelling a
column name to exercise it but my proc wouldn't compile. I'll do
something else like violate a pk with an insert or something like that.
If it doesn't get me what I'm looking for at least I'll have everything
else.

I'm not worried about wildcard/parameters. I'm stuffing the pertinent
variable values in another column that gets written to the db. The
dynamic messages are the ones I probably care the most about which I
can't get.

This would be a nice feature in a future version.

Thanks,
Mark

On 12/30/2011 12:58 PM, Mark A. Parsons wrote:
> Have you tried it? When in doubt, try it out!
>
> If you have pre-canned messages, without wildcard/parameters (eg,
> %1!), stored in sysmessages then sp_getmessage will probably suffice.
>
> In all other cases sp_getmessage will probably be of little/no benefit
> (eg, it won't capture the values passed into wildcard/parameter
> positions (eg, %1!); on-the-fly/dynamic error messages aren't stored
> in sysmessages so nothing for sp_getmessage to grab hold of).
>
>
>
> On 12/30/2011 12:49, Mark wrote:
>> Hi Mark,
>>
>> Would sp_getmessage get me what I'm after?
>>
>> Here's the idea only I would call sp_getmessage from the proc that
>> actually inserts the record into my error table by
>> using the passed in error code.
>>
>> /*-------------------------------------------------------------------
>> This jump is triggered whenever an update error is encountered
>> -------------------------------------------------------------------*/
>> update_error:
>> DECLARE @li_error int,
>> @ls_error_msg varchar(255)
>>
>> select @li_error = @@error
>>
>> execute sp_getmessage @ai_error_code,@ls_error_msg output
>>
>> ... continue error handling with error code and message
>>
>>
>> Thanks,
>> Mark
>>
>>
>>
>>
>> On 12/30/2011 11:04 AM, Mark A. Parsons wrote:
>>> Not that I'm aware of.
>>>
>>> You could try putting a proxy table on the front of the stored proc
>>> to see if the error message can be picked up as a
>>> textual result set (from the proxy). (NOTE: I don't recall if this
>>> will work or not.) (NOTE: Obviously (?) this would
>>> incur a good bit of overhead for the CIS call as well as the
>>> processing of the output from the stored proc.) (NOTE:
>>> Obviously (?) if this works you would be working from outside the
>>> proc as opposed to within the proc as you envision.)
>>>
>>> On 12/30/2011 08:44, Mark wrote:
>>>> Hi All,
>>>>
>>>> This may be a dumb question but I didn't see an obvious answer.
>>>>
>>>> Is there an elegant way of capturing the error message associated
>>>> with the error code in @@error in a stored proc? I
>>>> would like to capture this and write to a table in the db in the
>>>> error handling section of my procs.
>>>>
>>>> thanks,
>>>> Mark
>>>>
>>>> ASE 15.5


Mark Posted on 2011-12-30 21:14:54.0Z
From: Mark <mlibner@yahoo.com>
Reply-To: mlibner@yahoo.com
Organization: Security Finance
User-Agent: Mozilla/5.0 (Windows NT 5.1; rv:8.0) Gecko/20111105 Thunderbird/8.0
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Capturing the error message text in a proc
References: <4efdc030$1@forums-1-dub> <4efde0fe@forums-1-dub> <4efdf9ad@forums-1-dub> <4efdfbdb@forums-1-dub>
In-Reply-To: <4efdfbdb@forums-1-dub>
Content-Type: multipart/alternative; boundary="------------070702040302080205090305"
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4efe29ce$1@forums-1-dub>
Date: 30 Dec 2011 13:14:54 -0800
X-Trace: forums-1-dub 1325279694 10.22.241.152 (30 Dec 2011 13:14:54 -0800)
X-Original-Trace: 30 Dec 2011 13:14:54 -0800, vip152.sybase.com
Lines: 490
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30800
Article PK: 73691

The error message in the screen shot below is what I would love to capture unfortunately I can't capture @@error value. Even if I set a variable right after the insert that causes the error before I check @@error.

Just for giggles I called sp_getmessage passing it error code 546 but got back a null. If I execute a simple select from sysmessages  where error = 546 I get back the text in the message below with the place holders. Time to punt I guess.



On 12/30/2011 12:58 PM, Mark A. Parsons wrote:

Have you tried it?  When in doubt, try it out!

If you have pre-canned messages, without wildcard/parameters (eg, %1!), stored in sysmessages then sp_getmessage will probably suffice.

In all other cases sp_getmessage will probably be of little/no benefit (eg, it won't capture the values passed into wildcard/parameter positions (eg, %1!); on-the-fly/dynamic error messages aren't stored in sysmessages so nothing for sp_getmessage to grab hold of).



On 12/30/2011 12:49, Mark wrote:
Hi Mark,

Would sp_getmessage get me what I'm after?

Here's the idea only I would call sp_getmessage from the proc that actually inserts the record into my error table by
using the passed in error code.

/*-------------------------------------------------------------------
This jump is triggered whenever an update error is encountered
-------------------------------------------------------------------*/
update_error:
DECLARE @li_error int,
@ls_error_msg varchar(255)

select @li_error = @@error

execute sp_getmessage @ai_error_code,@ls_error_msg output

... continue error handling with error code and message


Thanks,
Mark




On 12/30/2011 11:04 AM, Mark A. Parsons wrote:
Not that I'm aware of.

You could try putting a proxy table on the front of the stored proc to see if the error message can be picked up as a
textual result set (from the proxy). (NOTE: I don't recall if this will work or not.) (NOTE: Obviously (?) this would
incur a good bit of overhead for the CIS call as well as the processing of the output from the stored proc.) (NOTE:
Obviously (?) if this works you would be working from outside the proc as opposed to within the proc as you envision.)

On 12/30/2011 08:44, Mark wrote:
Hi All,

This may be a dumb question but I didn't see an obvious answer.

Is there an elegant way of capturing the error message associated with the error code in @@error in a stored proc? I
would like to capture this and write to a table in the db in the error handling section of my procs.

thanks,
Mark

ASE 15.5

efcdfaci.png