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.

mysterious errormessage 2116

3 posts in General Discussion Last posting was on 2011-01-21 18:37:18.0Z
Milo Minderbinder Posted on 2011-01-21 17:30:24.0Z
From: Milo Minderbinder <noMail@fmail.com>
Reply-To: noMail@fmail.com
Organization: M&M
User-Agent: Mozilla/5.0 (X11; U; Linux i686; de; rv:1.9.2.13) Gecko/20101207 Thunderbird/3.1.7
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: mysterious errormessage 2116
Content-Type: text/plain; charset=ISO-8859-15; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4d39c2b0$1@forums-1-dub>
Date: 21 Jan 2011 09:30:24 -0800
X-Trace: forums-1-dub 1295631024 10.22.241.152 (21 Jan 2011 09:30:24 -0800)
X-Original-Trace: 21 Jan 2011 09:30:24 -0800, vip152.sybase.com
Lines: 18
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29887
Article PK: 79116

Hi,
can somebody explain the following errormessage?

Msg 2116, Level 16, State 1:
Server 'SE15500', Procedure 'mytrigger_trg':
CREATE TRIGGER failed because selecting from a TEXT, IMAGE, UNITEXT or
off-row Java datatype column of the inserted or deleted table is not
supported.

I got this message while trying to compile a trigger into sybase ASE15.5
(on Sun an on HP).
It's correct, there is a table involved, that contains a TEXT-column.
This trigger works fine on ASE12.5.4 (on Sun and on HP).

There is no hint on errormessage-documentation, nothing. Can anybody
explain and point a way out?

Milo


Rob V [ Sybase ] Posted on 2011-01-21 17:51:50.0Z
Message-ID: <4D39C7B4.4030504@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY>
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
To: noMail@fmail.com
Subject: Re: mysterious errormessage 2116
References: <4d39c2b0$1@forums-1-dub>
In-Reply-To: <4d39c2b0$1@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-15; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 21 Jan 2011 09:51:50 -0800
X-Trace: forums-1-dub 1295632310 10.22.241.152 (21 Jan 2011 09:51:50 -0800)
X-Original-Trace: 21 Jan 2011 09:51:50 -0800, vip152.sybase.com
Lines: 57
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29888
Article PK: 79117


On 21-Jan-2011 18:30, Milo Minderbinder wrote:
> Hi,
> can somebody explain the following errormessage?
>
> Msg 2116, Level 16, State 1:
> Server 'SE15500', Procedure 'mytrigger_trg':
> CREATE TRIGGER failed because selecting from a TEXT, IMAGE, UNITEXT or
> off-row Java datatype column of the inserted or deleted table is not
> supported.
>
> I got this message while trying to compile a trigger into sybase
> ASE15.5 (on Sun an on HP).
> It's correct, there is a table involved, that contains a TEXT-column.
> This trigger works fine on ASE12.5.4 (on Sun and on HP).
>
> There is no hint on errormessage-documentation, nothing. Can anybody
> explain and point a way out?
>
> Milo

The background is that the 'inserted' and 'deleted' tables are not real
tables, but virtual ones that are dynamically constructed from the
transaction log records for this transaction. This concept allows you to
access the 'before' and 'after' values of the modified rows through the
'inserted' and 'deleted' in the trigger code.
This works because a log record contains the modified data values for
the insert/deleted/modified row. text and image datatypes, as well as
off-row java classes, are stored in a different way (as a searate page
chain hanging off the row itself) and the modified values are not part
of the log record for an insert/deleted/modified row; instead there are
separate log records for these. In any case, 'before' and 'after'
values of such columns are not part of the 'inserted' and 'deleted'
tables. And for that reason, you cannot access them in 'inserted' and
'deleted' tables in the trigger code.

This has always been the situation, and this restriction applies just as
well to 12.5 (and any earlier version). I would have expected 12.5 to
throw an error as well. If it didn't than that was a bug in itself.

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


Bret Halford Posted on 2011-01-21 18:37:18.0Z
From: Bret Halford <bret@sybase.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.13) Gecko/20101207 Thunderbird/3.1.7
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: mysterious errormessage 2116
References: <4d39c2b0$1@forums-1-dub>
In-Reply-To: <4d39c2b0$1@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-15; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4d39d25e@forums-1-dub>
Date: 21 Jan 2011 10:37:18 -0800
X-Trace: forums-1-dub 1295635038 10.22.241.152 (21 Jan 2011 10:37:18 -0800)
X-Original-Trace: 21 Jan 2011 10:37:18 -0800, vip152.sybase.com
Lines: 138
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29889
Article PK: 79118


On 1/21/2011 10:30 AM, Milo Minderbinder wrote:
> Hi,
> can somebody explain the following errormessage?
>
> Msg 2116, Level 16, State 1:
> Server 'SE15500', Procedure 'mytrigger_trg':
> CREATE TRIGGER failed because selecting from a TEXT, IMAGE, UNITEXT or
> off-row Java datatype column of the inserted or deleted table is not
> supported.
>
> I got this message while trying to compile a trigger into sybase ASE15.5
> (on Sun an on HP).
> It's correct, there is a table involved, that contains a TEXT-column.
> This trigger works fine on ASE12.5.4 (on Sun and on HP).
>
> There is no hint on errormessage-documentation, nothing. Can anybody
> explain and point a way out?
>
> Milo

Hi Milo,


Selecting text or image data from the inserted/deleted pseudotables in
triggers has never been supported; I believe the docs have always stated
that it cannot be done although ASE hasn't previously enforced the
restriction. The fact that it works in some cases is accidental.

The impetus for raising the new message came from hard errors hit using
the new "INSTEAD OF" trigger feature against views, but we realized the
issue was generic to all triggers.

The core issue is that the inserted and deleted tables are essentially
views of data from syslogs, and not all text/image operations are
logged. Even when it is logged, the data returned by the select does not
come from the log because that access method was never programmed,
rather ASE uses the textptr value from the row in syslogs and gets the
text contents from the data pages as it would for a regular table.

That is why the following example returns "after" when it should return
"before" - it is reading the text value from the data page accessed
through the textptr (the textptr generally does not actually change when
the value is updated, ASE reuses the existing allocations for the new
value).

select @@version
go
create table t (x text)
go
create trigger utrig on t for update as
select x as "value_from_inserted" from inserted
select x as "value from_deleted" from deleted
go
insert t values ("before")
go
update t set x = "after"
go

Adaptive Server Enterprise/15.0.3/EBF 16736 ESD#2/P/Sun_svr4/OS
5.8/ase1503/270
7/64-bit/FBO/Sun Jul 26 10:29:50 2009

value_from_inserted




--------------------------------------------
---------------------------------------------
---------------------------------------------
---------------------------------------------
---------------------------------
after





(1 row affected)
value from_deleted




--------------------------------------------
---------------------------------------------
---------------------------------------------
---------------------------------------------
---------------------------------
after

(1 row affected)


logically, this result should be "before" - but the select isn't really
supported.


Another example of a problem when selecting text/image from deleted is
that error 7128 is raised if the trigger was on DELETE.

create trigger deltrig on t for delete
as select x from deleted where x like '%'
go
delete t
go
Msg 7128, Level 16, State 3:
Server 'bret_sun2', Procedure 'deltrig', Line 7:
Text pointer value 0x0000036a000000000000000000001b18 references a data page
which is no longer allocated. This text pointer references first text
page 874,
which belongs to object ID 1273872784.
x





----------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------
---------------------------------

(0 rows affected)


The supported way to get the new inserted/updated text/image value
is to select it from the user table by joining with inserted on the
primary key.

The enforcement of the restriction can be removed by booting ASE with
traceflag 1716. This will give you the same behavior as before
(including incorrect results when selecting from deleted).

There is an open feature request 593730 to formally support text/image
values in triggers.