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.

Issue with using "for xml"

3 posts in General Discussion Last posting was on 2011-02-17 04:17:23.0Z
Prandip Posted on 2011-02-16 05:14:10.0Z
Sender: 56d2.4d5b5c33.1804289383@sybase.com
From: Prandip
Newsgroups: sybase.public.ase.general
Subject: Issue with using "for xml"
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4d5b5d22.56f9.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 15 Feb 2011 21:14:10 -0800
X-Trace: forums-1-dub 1297833250 10.22.241.41 (15 Feb 2011 21:14:10 -0800)
X-Original-Trace: 15 Feb 2011 21:14:10 -0800, 10.22.241.41
Lines: 34
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29971
Article PK: 79199

Hi,
I am using ASE version 15.0.3/EBF 17769 ESD#4/P/Sun_svr4/OS
5.8/ase1503/2768/64-bit.
Recently a functionality was deployed to audit data changes
to come key application tables using for xml. The trigger
code is as follows:

CREATE TRIGGER tr_XXX ON t_XXX FOR INSERT, UPDATE, DELETE
AS
IF @@rowcount = 0 /* no rows deleted */ return
declare @tablename varchar(32)
select @tablename = 't_XXX' INSERT INTO t_Audit_XXX
SELECT @tablename, (select * from deleted for xml), (select
* from inserted for xml), getdate(), suser_name(),
host_name()
IF @@error != 0
BEGIN
PRINT 'An error occurred backing up deleted row'
ROLLBACK TRAN
END
RETURN

The issue is that in some sporadic cases, deletes/inserts in
the table t_XXX might fail the trigger execution stating:
error 208 : t_XXX not found.
The same statements would then work fine if fired from a
different session. In order to make the statment work on the
same session, either the trigger has to be dropped or
disabled. Its very difficult to replicate the issue, but in
rare cases we have been able to get the same issue across
multiple session/macs. Sometimes recompiling the trigger
works, in other cases it doesn't.
So not exactly sure what is going wrong here. Has any one
else faced this issue? Please advise.


Rob V [ Sybase ] Posted on 2011-02-16 11:25:24.0Z
From: "Rob V [ Sybase ]" <robv@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY>
Reply-To: robv@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY
Organization: Sypron BV / TeamSybase / Sybase
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: Issue with using "for xml"
References: <4d5b5d22.56f9.1681692777@sybase.com>
In-Reply-To: <4d5b5d22.56f9.1681692777@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: <4d5bb424@forums-1-dub>
Date: 16 Feb 2011 03:25:24 -0800
X-Trace: forums-1-dub 1297855524 10.22.241.152 (16 Feb 2011 03:25:24 -0800)
X-Original-Trace: 16 Feb 2011 03:25:24 -0800, vip152.sybase.com
Lines: 60
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29972
Article PK: 79203


On 16-Feb-2011 06:14, Prandip wrote:
> Hi,
> I am using ASE version 15.0.3/EBF 17769 ESD#4/P/Sun_svr4/OS
> 5.8/ase1503/2768/64-bit.
> Recently a functionality was deployed to audit data changes
> to come key application tables using for xml. The trigger
> code is as follows:
>
> CREATE TRIGGER tr_XXX ON t_XXX FOR INSERT, UPDATE, DELETE
> AS
> IF @@rowcount = 0 /* no rows deleted */ return
> declare @tablename varchar(32)
> select @tablename = 't_XXX' INSERT INTO t_Audit_XXX
> SELECT @tablename, (select * from deleted for xml), (select
> * from inserted for xml), getdate(), suser_name(),
> host_name()
> IF @@error != 0
> BEGIN
> PRINT 'An error occurred backing up deleted row'
> ROLLBACK TRAN
> END
> RETURN
>
> The issue is that in some sporadic cases, deletes/inserts in
> the table t_XXX might fail the trigger execution stating:
> error 208 : t_XXX not found.
> The same statements would then work fine if fired from a
> different session. In order to make the statment work on the
> same session, either the trigger has to be dropped or
> disabled. Its very difficult to replicate the issue, but in
> rare cases we have been able to get the same issue across
> multiple session/macs. Sometimes recompiling the trigger
> works, in other cases it doesn't.
> So not exactly sure what is going wrong here. Has any one
> else faced this issue? Please advise.

This does not ring a bell, you may be hitting a bug here. You should
really open a case with Sybase TechSupport. Is there a stack trace in
the ASE error log when this error occurs? They will want to have that.
if not, they may instruct you to configure a shared memory dump when
that error happens.

HTH,

Rob V.
-----------------------------------------------------------------
Rob Verschoor

Certified Sybase Professional DBA for ASE 15.0/12.5/12.0/11.5/11.0
and Replication Server 15.0.1/12.5 // TeamSybase

Author of Sybase books (order online at www.sypron.nl/shop):
"Tips, Tricks& Recipes for Sybase ASE" (ASE 15 edition)
"The Complete Sybase ASE Quick Reference Guide"
"The Complete Sybase Replication Server Quick Reference Guide"

rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter: @rob_verschoor
Sypron B.V., The Netherlands | Chamber of Commerce 27138666
-----------------------------------------------------------------


Prandip Posted on 2011-02-17 04:17:23.0Z
Sender: cee.4d5c9222.1804289383@sybase.com
From: Prandip
Newsgroups: sybase.public.ase.general
Subject: Re: Issue with using "for xml"
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4d5ca153.110f.1681692777@sybase.com>
References: <4d5bb424@forums-1-dub>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="-=_forums-1-dub4d5ca153"
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 16 Feb 2011 20:17:23 -0800
X-Trace: forums-1-dub 1297916243 10.22.241.41 (16 Feb 2011 20:17:23 -0800)
X-Original-Trace: 16 Feb 2011 20:17:23 -0800, 10.22.241.41
Lines: 265
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29981
Article PK: 79210

Thanks Rob.
Will raise a case against Sybase tech support.
We are getting stack traces (sample attached) in our DEV
environment.
But again, that is not consistent.
We didn't get any on production.

> On 16-Feb-2011 06:14, Prandip wrote:
> > Hi,
> > I am using ASE version 15.0.3/EBF 17769
> > ESD#4/P/Sun_svr4/OS 5.8/ase1503/2768/64-bit.
> > Recently a functionality was deployed to audit data
> > changes to come key application tables using for xml.
> > The trigger code is as follows:
> >
> > CREATE TRIGGER tr_XXX ON t_XXX FOR INSERT, UPDATE,
> > DELETE AS
> > IF @@rowcount = 0 /* no rows deleted */ return
> > declare @tablename varchar(32)
> > select @tablename = 't_XXX' INSERT INTO t_Audit_XXX
> > SELECT @tablename, (select * from deleted for xml),
> > (select * from inserted for xml), getdate(),
> > suser_name(), host_name()
> > IF @@error != 0
> > BEGIN
> > PRINT 'An error occurred backing up deleted row'
> > ROLLBACK TRAN
> > END
> > RETURN
> >
> > The issue is that in some sporadic cases,
> > deletes/inserts in the table t_XXX might fail the
> > trigger execution stating: error 208 : t_XXX not found.
> > The same statements would then work fine if fired from a
> > different session. In order to make the statment work on
> > the same session, either the trigger has to be dropped
> > or disabled. Its very difficult to replicate the issue,
> > but in rare cases we have been able to get the same
> > issue across multiple session/macs. Sometimes
> > recompiling the trigger works, in other cases it
> > doesn't. So not exactly sure what is going wrong here.
> > Has any one else faced this issue? Please advise.
>
> This does not ring a bell, you may be hitting a bug here.
> You should really open a case with Sybase TechSupport. Is
> there a stack trace in the ASE error log when this error
> occurs? They will want to have that. if not, they may
> instruct you to configure a shared memory dump when that
> error happens.
>
> HTH,
>
> Rob V.
> ----------------------------------------------------------
> ------- Rob Verschoor
>
> Certified Sybase Professional DBA for ASE
> 15.0/12.5/12.0/11.5/11.0 and Replication Server
> 15.0.1/12.5 // TeamSybase
>
> Author of Sybase books (order online at
> www.sypron.nl/shop): "Tips, Tricks& Recipes for Sybase
> ASE" (ASE 15 edition) "The Complete Sybase ASE Quick
> Reference Guide" "The Complete Sybase Replication Server
> Quick Reference Guide"
>
> rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter:
> @rob_verschoor Sypron B.V., The Netherlands | Chamber of
> Commerce 27138666
> ----------------------------------------------------------
> -------
>

00:00000:00251:2011/02/15 04:30:43.10 server Invalid column length: 253. Value must be between 0 and 4 at offset 3 for 'all-pages' row with minimum row length of 14.
00:00000:00251:2011/02/15 04:30:43.11 kernel pc: 0x0000000080f7e8a8 pcstkwalk+0x24(0x0000010008b1cd30, 0x0000010008b1aba8, 0x000000000000270f, 0x0000000000000002, 0x0000000000000000)
00:00000:00251:2011/02/15 04:30:43.11 kernel pc: 0x0000000080f7e6f0 ucstkgentrace+0x1d0(0x000001001f4f0038, 0x0000000000000002, 0x000000000000270f, 0x0000000000000000, 0x0000000000000000)
00:00000:00251:2011/02/15 04:30:43.11 kernel pc: 0x0000000080f08b14 ucbacktrace+0xb4(0x0000000000000000, 0x0000000000000001, 0x0000000000007c00, 0x0000000000000004, 0x0000010024fb6068)
00:00000:00251:2011/02/15 04:30:43.11 kernel pc: 0x000000008047f614 _$o1cexsA0.collocate+0x254(0x00000100c5bf03e4, 0x000001001f4f0038, 0x0000010008b1d2c4, 0x0000000000000003, 0x00000000000000fd)
00:00000:00251:2011/02/15 04:30:43.11 kernel pc: 0x0000000080477e14 _$o1cexqA0.LeSubst+0xe0(0x000001002e459800, 0x000001003dc990a0, 0x00000100c5bf03e4, 0x0000000000000001, 0x000000000000000e)
00:00000:00251:2011/02/15 04:30:43.11 kernel pc: 0x00000000803a3f98 getnext+0xf8(0x00000000826ee400, 0x0000000000800220, 0x000000008039b460, 0x0000000000000001, 0x0000010161185238)
00:00000:00251:2011/02/15 04:30:43.11 kernel pc: 0x00000000803a6154 LeRetnCode LeScanOp::_LeOpNext(ExeCtxt&)+0x154(0x000001002e3ce588, 0x000001002ba78bb8, 0x000001002ba78a80, 0x0000000000000000, 0x0000000010000000)
00:00000:00251:2011/02/15 04:30:43.11 kernel pc: 0x00000000803e44dc _$o1cexlY0.__1cLLeEmitSndOpJ_LeOpNext6MrnHExeCtxt__nKLeRetnCode__+0xbc(0x0000000000000001, 0x00000000803a6000, 0x0000000000000001, 0x000001001f4f0038, 0x000001002e3cf1c0)
00:00000:00251:2011/02/15 04:30:43.11 kernel pc: 0x00000000803a8394 LePlanNext+0xf4(0x000001001f4f0038, 0x000001002ba78bb8, 0x0000000081c4f590, 0x00000000803b68bc, 0x0000000000000002)
00:00000:00251:2011/02/15 04:30:43.11 kernel [Handler pc: 0x0000000080b2fb2c le_execerr installed by the following function:-]
00:00000:00251:2011/02/15 04:30:43.11 kernel [Handler pc: 0x0000000080bff7c0 aritherr installed by the following function:-]
00:00000:00251:2011/02/15 04:30:43.11 kernel pc: 0x00000000803aa638 exec_lava+0x1ac(0x0000000000000000, 0x0000000000000001, 0x0000000000000000, 0x0000000000000002, 0x000001001f4f0038)
00:00000:00251:2011/02/15 04:30:43.11 kernel pc: 0x0000000080523098 _$o1ceylB0.curs_fetch+0x19c(0x000001003e3b3800, 0x000001001f4f0038, 0x0000000000007800, 0x0000010008b1da1c, 0x000001002ba78800)
00:00000:00251:2011/02/15 04:30:43.11 kernel pc: 0x0000000080399cbc s_execute+0x19e4(0x0000000000000001, 0x000001001f4f0038, 0x0000000000000000, 0x000001002e456868, 0x0000000000000108)
00:00000:00251:2011/02/15 04:30:43.11 kernel [Handler pc: 0x0000000080cc341c hdl_stack installed by the following function:-]
00:00000:00251:2011/02/15 04:30:43.11 kernel [Handler pc: 0x0000000080c731b4 s_handle installed by the following function:-]
00:00000:00251:2011/02/15 04:30:43.11 kernel pc: 0x00000000804a3c14 sequencer+0x254(0x0000000000007b60, 0x0000000000000003, 0xffffffffffffffff, 0x0000000000000000, 0x0000000082729800)
00:00000:00251:2011/02/15 04:30:43.11 kernel pc: 0x000000008054e894 LeNsExecStmt+0xf0(0x000001002e456868, 0x0000000000000108, 0x0000000000000000, 0x000001001f4f0038, 0x0000000000007b58)
00:00000:00251:2011/02/15 04:30:43.11 kernel [Handler pc: 0x0000000080b5b5e4 ns_handle installed by the following function:-]
00:00000:00251:2011/02/15 04:30:43.11 kernel pc: 0x0000000080b58340 ns_get_next+0xf8(0x000001002e456868, 0x00000100321b5000, 0x0000000000009320, 0x000001001f4f0038, 0x0000000000009000)
00:00000:00251:2011/02/15 04:30:43.11 kernel pc: 0x0000000080ed20e8 bool Nsql::nsGetNext()+0x28(0x00000101751a4040, 0x0000000000000008, 0x000001001f4f9330, 0x0000000000009000, 0x000001001f4f0038)
00:00000:00251:2011/02/15 04:30:43.11 kernel pc: 0x0000000080ed1d7c bool Nsql::nextRow()+0x4(0x00000101751a4040, 0x00000000825c22c4, 0x00000101751a4530, 0x0000000000000001, 0x0000000000000000)
00:00000:00251:2011/02/15 04:30:43.11 kernel pc: 0x0000000080ed44c0 void SqlxData::generate(SybXmlString&,XmlList<ColInfo*>&,SqlxDoc&,bool)+0x190(0x00000101751a4360, 0x0000010008b1ee40, 0x00000101751a4528, 0x00000101751a4530, 0x0000000081ee7c30)
00:00000:00251:2011/02/15 04:30:43.11 kernel pc: 0x0000000080ed18ec constant*XSqlx::getResults()+0x2c0(0x00000101751a4038, 0x00000101751a4530, 0x0000010008b1edc0, 0x0000000000000001, 0x0000010008b1ee40)
00:00000:00251:2011/02/15 04:30:43.11 kernel pc: 0x0000000080eb6e88 void PtlSqlx::ToSqlx(xml_portal_arg*,xml_portal_arg*,constant**,xml_portal_ret*,int)+0x20c(0x000001017519de10, 0x0000000000000000, 0x0000000000000001, 0x0000000000000000, 0x0000010008b1f540)
00:00000:00251:2011/02/15 04:30:43.11 kernel pc: 0x0000000080eb744c ToSqlx+0x1a0(0x0000000000000000, 0x0000010008b20688, 0x000001017519de10, 0x0000000000000000, 0x0000010008b1f540)
00:00000:00251:2011/02/15 04:30:43.11 kernel [Handler pc: 0x0000000080cc340c hdl_backout installed by the following function:-]
00:00000:00251:2011/02/15 04:30:43.11 kernel [Handler pc: 0x0000000080cc340c hdl_backout installed by the following function:-]
00:00000:00251:2011/02/15 04:30:43.11 kernel [Handler pc: 0x0000000080cc340c hdl_backout installed by the following function:-]
00:00000:00251:2011/02/15 04:30:43.11 kernel [Handler pc: 0x0000000080cc340c hdl_backout installed by the following function:-]
00:00000:00251:2011/02/15 04:30:43.12 kernel pc: 0x0000000080ea27b4 bi__tosqlx+0x54c(0x000001002e416900, 0x0000000000000009, 0x0000010008b1f570, 0x0000000000000080, 0x0000000000000027)
00:00000:00251:2011/02/15 04:30:43.12 kernel pc: 0x0000000080437abc _$o1cexoM0.LeRun+0x172dc(0x0000000000000003, 0x000001002e416080, 0x000001002e416190, 0x000001001f4f0038, 0x0000000000000000)
00:00000:00251:2011/02/15 04:30:43.12 kernel pc: 0x00000000803aadac int LeEvals::LeEvRun(LeRunMode,LeRunStack*,short,LeDataRow*,unsigned char*,int*)+0x4c(0xffffffffffffffff, 0x0000000000000000, 0x0000000000000000, 0xffffffffffffffff, 0x0000000000000000)
00:00000:00251:2011/02/15 04:30:43.12 kernel pc: 0x00000000803bbfd4 LeRetnCode LeInsertOp::_LeOpNext(ExeCtxt&)+0x194(0x0000000000000000, 0x000001002e415c80,
0x000001002e4171c0, 0x000001002e415b48, 0x000001003c2c8908)
00:00000:00251:2011/02/15 04:30:43.12 kernel pc: 0x000000008047f9c8 _$o1cexsE0.__1cNLeEmitNoSndOpJ_LeOpNext6MrnHExeCtxt__nKLeRetnCode__+0x78(0x000001003c2c8908, 0x0000000000000000, 0x000001002e417560, 0x000001002e415c80, 0x00000000803bbe40)
00:00000:00251:2011/02/15 04:30:43.12 kernel pc: 0x00000000803a8394 LePlanNext+0xf4(0x000001001f4f0038, 0x000001002e415c80, 0x000000008214c1c0, 0x00000000803a2b0c, 0x0000000000000002)
00:00000:00251:2011/02/15 04:30:43.12 kernel [Handler pc: 0x0000000080b2fb2c le_execerr installed by the following function:-]
00:00000:00251:2011/02/15 04:30:43.12 kernel [Handler pc: 0x0000000080bff7c0 aritherr installed by the following function:-]
00:00000:00251:2011/02/15 04:30:43.12 kernel pc: 0x00000000803aa56c exec_lava+0xe0(0x0000000000000000, 0x000000000000002c, 0x0000000000000000, 0x0000000000000000, 0x0000000000000000)
00:00000:00251:2011/02/15 04:30:43.12 kernel pc: 0x0000000080399608 s_execute+0x1330(0x0000000000000001, 0x000001001f4f0038, 0x0000000000000000, 0x0000000000000000, 0x00000000000000c3)
00:00000:00251:2011/02/15 04:30:43.12 kernel [Handler pc: 0x0000000080cc341c hdl_stack installed by the following function:-]
00:00000:00251:2011/02/15 04:30:43.12 kernel [Handler pc: 0x0000000080c731b4 s_handle installed by the following function:-]
00:00000:00251:2011/02/15 04:30:43.12 kernel pc: 0x00000000804a3c14 sequencer+0x254(0x0000000000007b60, 0x0000000000000003, 0xffffffffffffffff, 0x0000000000000000, 0x0000000082729800)
00:00000:00251:2011/02/15 04:30:43.12 kernel end of stack trace, spid 251, kpid 1593901560, suid 4