Create index was aborted because pages in the data only locked table are not linked

[liez...@gmail.com]

Hello Guys,

I am getting the following error on Sybase Adaptive Server 15
Error 1581
"Create index was aborted because pages in the data only locked table
are not linked"

By the way... - the table is an ALL PAGES SCHEME!

I firstly create a table
-------------------------------
create table temp_liz
(
log_id int
)

Then I select into this table from other tables in a join clause. (in
the real script)
but this also causes the error...
----------------------------------------------

select log_id
into temp_liz
from temp_LL - just dummy names of course:)

Then I create a clustered index on the leading column which includes
the "with sorted_data" option and others, like "with consumers = 4"
--------------------------------------------------------------
create clustered index idx_liz
on temp_liz (log_id)
with sorted_data, consumers = 4

Even though this is NOT a "DOL" (data only locked) table, I still get
the issue
NB: sp_help reports ALL PAGES locked scheme. (Lock scheme Allpages)

Our Sybase server version is:
Enterprise 15.0.2 EBF 15093 ESD#2PSun_svr4OS 5.8ase1502251664-bit
FBOThu Dec 20 040537 2007

On another Sybase installation, I only get this error:
--------------------------------------------------------------------------

Adaptive Server Enterprise/12.5.3/EBF 13325 ESD#7/P/Sun_svr4/OS 5.8/
ase1253/1951/64-bit/FBO/Fri Mar 24 11:00:22 2006

Create index with sorted_data was aborted because of row out of
order.
Primary key of first out of order row is '1018'
Msg: 1530, Level: 16, State: 2

I am aware of the issue surrounding the with SORTED_DATA on a DOL
table, but like I said this is an ALL PAGES Locked scheme and the data
was not bcp'ed into the table.

Any ideas, will help a lot!
Liz

[Bret_Halford]

I don't find any known bugs that exactly match this. The message is
certainly
incorrect, so if nothing else the wording of the message is a bug.

If you drop the "sorted_data" option, do you get an error?

Cheers,
-bret

[liez...@gmail.com]

> -bret- Hide quoted text -
>
> - Show quoted text -

Hey Bret,
Thanks for your reply!
It did work yesterday, without the "sorted_data" option, but not the
ideal work around, because this means I can't make use of the space
saving advantage brought about with this option.
I may be wrong, but I think ASE doesn't like the with "sorted_data"
option added to the create clustered syntax, when the data it is
created on, may actually be unsorted. (evident from what the ASE
12.5.3 message indicates)
But yes the message certainly is the wrong one.

Still curious why this happens on the APL schema though...

Liz

[liez...@gmail.com]

> Liz- Hide quoted text -

>
> - Show quoted text -

The server wide locking scheme was changed by mistake after
installation, so the table was created with the DATA ONLY LOCKED
scheme, however, it still reported ALLPAGES in the sp_help output.

I didn't check the the server wide setting and assumed this was either
datarows or datapages.
Since then, one of the DBA's changed it on the fly back to ALLPAGES,
and the above command worked again with the sorted_data option.

...Yes, Luckily this was not production!

Thanks again
Liz

[Carl Kayser]

<liez...@gmail.com> wrote in message
news:68ceca82-d229-4ec5...@a2g2000prm.googlegroups.com...

Thanks again
Liz

Perhaps I am missing something here. I assume that the table was created
after the deafult server locking scheme was set to DOL. In which case there
is a bug with sp_help. Right? Changing the default locking scheme
shouldn't affect drop/create index; the lock scheme is an attribute of the
table. I find the above described scenario to be inconsistent.