I am not positive, but I believe that if the table itself has one of those
data types, then you cannot re-index online irrespective of what the indexes
hold.

I vaguely recall running into the same road block a few months back.




-----Original Message-----
From: Garth Joubert
[mailto:mssqldba-ezmlmshield-x40356200.[Email address protected]
Sent: Tuesday, February 27, 2007 7:55 PM
To: LazyDBA Discussion
Subject: SQL 2005 - Online Indexing Error with XML Data Type

Hi All,

I am using SQL 2005 Enterprise with SP1 and Re-Indexing Tables with
ONLINE Option set to "ON", but keep getting the below error:

Msg 2725, Level 16, State 2, Line 1
Online index operation cannot be performed for index 'PK_tblExodus'
because the index contains column 'EventStack' of data type text, ntext,
image, varchar(max), nvarchar(max), varbinary(max) or xml. For
non-clustered index the column could be an include column of the index,
for clustered index it could be any column of the table. In case of
drop_existing the column could be part of new or old index. The
operation must be performed offline.

Now this column "EventStack" is XML data type, but NOT part of any
index.

My understanding is that as long as it is not part of an Index, then I
can Re-Index Online. I could be wrong.

Can anyone help me please.

Thanks

Garth Joubert
Information Services
DataBase Administrator

Phone : 07 574 4800
(DDI): 07 574 4888 Ext 4864
(Cell): 021 2278557
Facsimile : 07 574 4863
Email: garth.[Email address protected]

TrustPower Limited
Private Bag 12023
Tauranga
New Zealand



---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html



MS Sql Server LazyDBA home page