Cannot use TEXTIMAGE_ON when a table...

[MrDom]

I am wondering if someone can help solve this question I have a table
in sql server 2000, I setup it using Enterprise manager.

When I generate an SQL Script for this table it scripts as:

CREATE TABLE [dbo].[CubicleConfiguration] (
[CubicleConfigurationID] [int] IDENTITY (1, 1) NOT NULL ,
[Description] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

Which is fine, however when I try to insert that into another database
using query anaylser I get the following error:

Server: Msg 1709, Level 16, State 1, Line 2
Cannot use TEXTIMAGE_ON when a table has no text, ntext, or image
columns.

OK I know I can remove the TEXTIMAGE_ON [PRIMARY] and that solves the
problem, however I have written some scripts to automate script
generation process, and this TEXTIMAGE thing, throws a spanner in the
automation process.

Any to suggestions as to why this is happening?

If I try building a new table manually using enterprise manager
creating the same table definition above, then script it, I get:

CREATE TABLE [dbo].[CubicleConfiguration2] (
[CubicleConfigurationID2] [int] IDENTITY (1, 1) NOT NULL ,
[Description2] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO

Which is correct and should be generated in the first place

Any ideas as to why enterprise manager decides to add a TEXTIMAGE_ON
[PRIMARY] and break it?

The question is has something in the schema been corrupt?, how do I
return it back to normal?

[John Bell]

Hi

It seems that at some point the table may have contained a text or image
column and there is an entry left in sysindexes with an indid of 255 for
that table.

Without modifying sysindexes directly you may have to resort to re-creating
that table under another name, transfering the data, dropping the original
table and renaming the new one. Possibly setting the SQL-DMO TextFileGroup
Property will be possible, but I have not tried it.

John

"MrDom" <mr_d...@hotmail.com> wrote in message
news:1117665527.8...@f14g2000cwb.googlegroups.com...

[Simon Hayes]

I don't really know, but what does this return:

select objectproperty(object_id('CubicleConfiguration',
'TableHasTextImage'))

If you get 1, and CubicleConfiguration doesn't have a text column, then
it's likely that there's some sort of metadata corruption - you could
try dropping and recreating the table to see if it fixes the problem.

Alternatively, if that isn't an option for some reason, and if you only
have one filegroup, then you could use the SQLDMOScript2_NoFG constant
to prevent the filegroup clause from being included in your script. (I
assume you're using SQLDMO to generate your scripts - if you're using a
third-party tool, then you'd have to check the documentation for the
tool).

Simon

[MrDom]

yes when i ran the above script it did infact return 1.

[MrDom]

I checked the the sysindexes for that database and I did infact find
another index, with an indid of 255.

It's strange that the index doesn't show up in enterprise manager, or
DBCC doesn't update it and remove it from the sysindex table.

Thanks for your help guys!

[John Bell]

Hi

That is because it is not really an index.

In BOL the documentation for sysindexes/Indid
255 = Entry for tables that have text or image data

I would have thought DBCC CLEANTABLE and/or DBCC CHECKTABLE would have
mopped it up, but it doesn't seem to.

John

"MrDom" <mr_d...@hotmail.com> wrote in message

news:1117728125....@g14g2000cwa.googlegroups.com...

[Erland Sommarskog]

John Bell (jbellne...@hotmail.com) writes:
> That is because it is not really an index.
>
> In BOL the documentation for sysindexes/Indid
> 255 = Entry for tables that have text or image data
>
> I would have thought DBCC CLEANTABLE and/or DBCC CHECKTABLE would have
> mopped it up, but it doesn't seem to.

This script repros the problem:

CREATE TABLE [dbo].[CubicleConfiguration] (
[CubicleConfigurationID] [int] IDENTITY (1, 1) NOT NULL ,

some_text text,

[Description] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL
) ON [PRIMARY]

Go
ALTER TABLE CubicleConfiguration DROP COLUMN some_text
go
select objectproperty(object_id('CubicleConfiguration'),
'TableHasTextImage')
go
DROP TABLE CubicleConfiguration

The good news is that SQL 2005 gets it right.

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp