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.

Number of variable length columns exceeds limit of 254 for allpage locked tables. CREATE TABLE for 'aaa' failed.

4 posts in General Discussion Last posting was on 2009-08-17 19:23:19.0Z
George Brink Posted on 2009-08-17 18:18:56.0Z
From: George Brink <siberianowl@yahoo.com>
User-Agent: Thunderbird 2.0.0.22 (Windows/20090605)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Number of variable length columns exceeds limit of 254 for allpage locked tables. CREATE TABLE for 'aaa' failed.
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: <4a899f10$3@forums-1-dub>
Date: 17 Aug 2009 11:18:56 -0700
X-Trace: forums-1-dub 1250533136 10.22.241.152 (17 Aug 2009 11:18:56 -0700)
X-Original-Trace: 17 Aug 2009 11:18:56 -0700, vip152.sybase.com
Lines: 14
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28175
Article PK: 77419

I am copying a table from the other database (MS SQL) and stumble upon a
strange error.
Originally the table had a lot of fields, most of them were of the
varchar type. CREATE TABLE failed with an error number 1767 "Number of
variable length columns exceeds limit of 254 for allpage locked tables.
CREATE TABLE for 'aaa' failed."
I converted all varchar fields into char but I am still getting the same
error. Right now, the table has 261 char and two int fields. They are
not variable length, aren't they?
What is wrong and how to fix it?

DB is on the server:
Adaptive Server Enterprise/15.0.3/EBF 16554 ESD#1/P/RS6000/AIX
5.3/ase1503/2681/64-bit/FBO/Mon Mar 9 01:35:28 2009


Sherlock, Kevin [TeamSybase] Posted on 2009-08-17 18:40:35.0Z
From: "Sherlock, Kevin [TeamSybase]" <kevin.sherlock@teamsybase.com>
Newsgroups: sybase.public.ase.general
References: <4a899f10$3@forums-1-dub>
Subject: Re: Number of variable length columns exceeds limit of 254 for allpage locked tables. CREATE TABLE for 'aaa' failed.
Lines: 22
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
X-RFC2646: Format=Flowed; Response
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3198
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4a89a423$1@forums-1-dub>
Date: 17 Aug 2009 11:40:35 -0700
X-Trace: forums-1-dub 1250534435 10.22.241.152 (17 Aug 2009 11:40:35 -0700)
X-Original-Trace: 17 Aug 2009 11:40:35 -0700, vip152.sybase.com
X-Authenticated-User: teamsybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28177
Article PK: 77421

creating a "char(x) null" datatype is the same as a variable length field.
So even if you changed the datatype from "varchar" to "char", if they are
still "nullable", then they are variable length.

"George Brink" <siberianowl@yahoo.com> wrote in message
news:4a899f10$3@forums-1-dub...
>I am copying a table from the other database (MS SQL) and stumble upon a
>strange error.
> Originally the table had a lot of fields, most of them were of the varchar
> type. CREATE TABLE failed with an error number 1767 "Number of variable
> length columns exceeds limit of 254 for allpage locked tables. CREATE
> TABLE for 'aaa' failed."
> I converted all varchar fields into char but I am still getting the same
> error. Right now, the table has 261 char and two int fields. They are not
> variable length, aren't they?
> What is wrong and how to fix it?
>
> DB is on the server:
> Adaptive Server Enterprise/15.0.3/EBF 16554 ESD#1/P/RS6000/AIX
> 5.3/ase1503/2681/64-bit/FBO/Mon Mar 9 01:35:28 2009


George Brink Posted on 2009-08-17 19:23:19.0Z
From: George Brink <siberianowl@yahoo.com>
User-Agent: Thunderbird 2.0.0.22 (Windows/20090605)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Number of variable length columns exceeds limit of 254 for allpage locked tables. CREATE TABLE for 'aaa' failed.
References: <4a899f10$3@forums-1-dub> <4a89a423$1@forums-1-dub>
In-Reply-To: <4a89a423$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: <4a89ae27$1@forums-1-dub>
Date: 17 Aug 2009 12:23:19 -0700
X-Trace: forums-1-dub 1250536999 10.22.241.152 (17 Aug 2009 12:23:19 -0700)
X-Original-Trace: 17 Aug 2009 12:23:19 -0700, vip152.sybase.com
Lines: 27
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28181
Article PK: 77426

Ok, got it, thanks.

BTW, problem is solved by using LOCK DATAPAGES option for the table.

Sherlock, Kevin [TeamSybase] wrote:
> creating a "char(x) null" datatype is the same as a variable length field.
> So even if you changed the datatype from "varchar" to "char", if they are
> still "nullable", then they are variable length.
>
> "George Brink" <siberianowl@yahoo.com> wrote in message
> news:4a899f10$3@forums-1-dub...
>> I am copying a table from the other database (MS SQL) and stumble upon a
>> strange error.
>> Originally the table had a lot of fields, most of them were of the varchar
>> type. CREATE TABLE failed with an error number 1767 "Number of variable
>> length columns exceeds limit of 254 for allpage locked tables. CREATE
>> TABLE for 'aaa' failed."
>> I converted all varchar fields into char but I am still getting the same
>> error. Right now, the table has 261 char and two int fields. They are not
>> variable length, aren't they?
>> What is wrong and how to fix it?
>>
>> DB is on the server:
>> Adaptive Server Enterprise/15.0.3/EBF 16554 ESD#1/P/RS6000/AIX
>> 5.3/ase1503/2681/64-bit/FBO/Mon Mar 9 01:35:28 2009
>
>


Neal Stack [Sybase] Posted on 2009-08-17 19:12:07.0Z
Message-ID: <4A89AB87.9010708@nospam.com>
From: "Neal Stack [Sybase]" <nstack@nospam.com>
User-Agent: Thunderbird 2.0.0.22 (Windows/20090605)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
To: George Brink <siberianowl@yahoo.com>
Subject: Re: Number of variable length columns exceeds limit of 254 for allpagelocked tables. CREATE TABLE for 'aaa' failed.
References: <4a899f10$3@forums-1-dub>
In-Reply-To: <4a899f10$3@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
Date: 17 Aug 2009 12:12:07 -0700
X-Trace: forums-1-dub 1250536327 10.22.241.152 (17 Aug 2009 12:12:07 -0700)
X-Original-Trace: 17 Aug 2009 12:12:07 -0700, vip152.sybase.com
Lines: 24
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28180
Article PK: 77423

Hello,

Depending on your version of ASE, there are different limits.

Try the following to see what they are:
% isql -Usa -P -o limits.out -w 220
1> dbcc traceon(3604)
2> dbcc serverlimits
3> go

..etc..
Table, Index related limits

Max number of columns in a table/view : 1024
Max number of indexes on a table : 250
Max number of user-keys in a single index on an unpartitioned table : 31
Max number of user-keys in a single local index on a partitioned table : 31
Max number of user-keys in a single global index on a partitioned table : 30
Max number of referential constraints per table : 192
Max number of keys in a referential integrity constraint : 16


Regards,
Neal