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

• Microsoft SQL Server version 7.0

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 this to be a problem in SQL Server version 7.0.

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 

Additional query words:

Keywords : kbSQLServ700bug
Version : winnt:7.0
Platform : winnt
Issue type : kbbug