Create Table Throws a TextImage Error On Creation

Question

I have a table which has one field that uses a filestream. When I attempt to insert the table I get this error

Cannot use TEXTIMAGE_ON when a table has no text, ntext, image, varchar(max), nvarchar(max), non-FILESTREAM columns.

Why?

SQL

USE [CMMS]
GO

/****** Object:  Table [dbo].[Ficheros]    Script Date: 05/09/2014 12:48:48 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Ficheros](
    [IDFichero] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
    [IDDocumento] [bigint] NOT NULL,
    [Fichero] [varbinary](max) FILESTREAM  NULL,
 CONSTRAINT [PK_Ficheros] PRIMARY KEY CLUSTERED 
(
    [IDFichero] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] FILESTREAM_ON [FILESTREAM_CMMS_DATA],
 CONSTRAINT [IX_Ficheros] UNIQUE NONCLUSTERED 
(
    [IDDocumento] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] FILESTREAM_ON [FILESTREAM_CMMS_DATA]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[Ficheros] ADD  CONSTRAINT [DF_Ficheros_IDFichero]  DEFAULT (newid()) FOR [IDFichero]
GO

ALTER TABLE [dbo].[Ficheros]  WITH CHECK ADD  CONSTRAINT [FK_Ficheros_Documentos] FOREIGN KEY([IDDocumento])
REFERENCES [dbo].[Documentos] ([IDDocumento])
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[Ficheros] CHECK CONSTRAINT [FK_Ficheros_Documentos]
GO

When I delete the table and use the script I get this error:

Mens. 1709, Nivel 16, Estado 1, Línea 2
No se puede usar TEXTIMAGE_ON cuando una tabla no tiene columnas de tipo text, ntext, image, varchar(max), nvarchar(max), distintas de FILESTREAM varbinary(max), xml o CLR grande.
Mens. 4902, Nivel 16, Estado 1, Línea 2
No se encuentra el objeto "dbo.Ficheros" porque no existe o no tiene permisos.
Mens. 4902, Nivel 16, Estado 1, Línea 2
No se encuentra el objeto "dbo.Ficheros" porque no existe o no tiene permisos.
Mens. 4902, Nivel 16, Estado 1, Línea 2
No se encuentra el objeto "dbo.Ficheros" porque no existe o no tiene permisos.

The application works fine, access to the database and can add files and get it, but I don't know why the script is incorrect.

Kumar

Answer 1

The TEXTIMAGE_ON flag is used to specify that any text or image columns should be stored in a different filegroup to the rest of the table.

Since there are no text or image columns in the [dbo].[Ficheros] table, this flag cannot be used and throws the error. Try removing the TEXTIMAGE_ON flag from the final line of the create table statement, so that it reads like this:

) ON [PRIMARY] FILESTREAM_ON [FILESTREAM_CMMS_DATA]

Automation

Sure would be nice if that did not cause an error, or at least have an option to have it not cause an error, esp. since SQL generated a script with it in the first place! – Osteoplastic

Thanks, this did it for me. But I agree with Dave, as SSMS is the one that generated this script, and now it does not work with its very own script. – Decare

Answer 2

I had this error message when I forgot to close the brackets of the create statement.

Selfreliance