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].[CubicleConfigur ation] (
[CubicleConfigur ationID] [int] IDENTITY (1, 1) NOT NULL ,
[Description] [nvarchar] (255) COLLATE SQL_Latin1_Gene ral_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].[CubicleConfigur ation2] (
[CubicleConfigur ationID2] [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]

Re: Cannot use TEXTIMAGE_ON when a table...

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_dom_is@hotm ail.com> wrote in message
news:1117665527 .868445.165330@ f14g2000cwb.goo glegroups.com.. .[color=blue]
>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].[CubicleConfigur ation] (
> [CubicleConfigur ationID] [int] IDENTITY (1, 1) NOT NULL ,
> [Description] [nvarchar] (255) COLLATE SQL_Latin1_Gene ral_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].[CubicleConfigur ation2] (
> [CubicleConfigur ationID2] [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?
>[/color]

[Simon Hayes]

Re: Cannot use TEXTIMAGE_ON when a table...

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

select objectproperty( object_id('Cubi cleConfiguratio n',
'TableHasTextIm age'))

If you get 1, and CubicleConfigur ation 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_N oFG 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]

Re: Cannot use TEXTIMAGE_ON when a table...

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

[MrDom]

Re: Cannot use TEXTIMAGE_ON when a table...

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]

Re: Cannot use TEXTIMAGE_ON when a table...

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_dom_is@hotm ail.com> wrote in message
news:1117728125 .050213.13480@g 14g2000cwa.goog legroups.com...[color=blue]
>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!
>[/color]

[Erland Sommarskog]

Re: Cannot use TEXTIMAGE_ON when a table...

John Bell (jbellnewsposts @hotmail.com) writes:[color=blue]
> 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.[/color]

This script repros the problem:

CREATE TABLE [dbo].[CubicleConfigur ation] (
[CubicleConfigur ationID] [int] IDENTITY (1, 1) NOT NULL ,
some_text text,
[Description] [nvarchar] (255) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NULL
) ON [PRIMARY]
Go
ALTER TABLE CubicleConfigur ation DROP COLUMN some_text
go
select objectproperty( object_id('Cubi cleConfiguratio n'),
'TableHasTextIm age')
go
DROP TABLE CubicleConfigur ation

The good news is that SQL 2005 gets it right.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp