BUG: Object Scripting Tool Places a TEXTIMAGE_ON Clause When no Text, Ntext, or Image Column is Present (231848)

The information in this article applies to:

Microsoft SQL Server 7.0
Microsoft SQL Server 2000 (all editions)

This article was previously published under Q231848

BUG #: 55284 (SQLBUG_70)

SYMPTOMS

When you run the "Generate SQL Scripts" tool in SQL Enterprise Manager, SQL Server produces a table creation (DDL) script with a TEXTIMAGE_ON clause in the table definition even when the table does not contain any text, ntext, or image column. This causes the script execution to fail with the following error:

Server: Msg 1709, Level 16, State 1, Line 1

Cannot use TEXTIMAGE_ON when a table has no text, ntext, or image columns.

CAUSE

A text, ntext, or image column was present in the table at one time. It was either removed through the SQL Enterprise Manager, or by using the ALTER TABLE command.

WORKAROUND

The workaround is to remove this clause from the table definition and run the script.

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

MORE INFORMATION

Here is a simple script that illustrates the problem:

create table mytable (
myint     int,
mytext    text
)
go

alter table mytable drop column mytext
go

--- Running the "Generate SQL Scripts" Tool will generate:

if exists (select * from sysobjects where id =
object_id(N'[dbo].[mytable]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[mytable]
GO

CREATE TABLE [dbo].[mytable] (
	[myint] [int] NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

TEXTIMAGE_ON [PRIMARY] should not be a part of the Table Definition. Remove it and then execute the script.

Modification Type:	Major	Last Reviewed:	10/16/2003
Keywords:	kbBug kbpending KB231848