Create table causes an error

Question

I have a table in which one field uses a stream. If I right-click and create the create table, T-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, has access to the database and can add files and receive them, but I do not know why the script is incorrect.

Thanks.

Answer 1

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

Since the table is [dbo]. [Ficheros] no columns of text or image, this flag cannot be used and throws an error. Try removing the TEXTIMAGE_ON flag from the last line of the create table statement so that it reads as follows:

 ) ON [PRIMARY] FILESTREAM_ON [FILESTREAM_CMMS_DATA]