locked
When I create a script with a table that use file stream I get an error RRS feed

  • Question

  • I have a database that has a table with file stream. The table is this:

    USE [CMMS]
    GO
    
    /****** Object:  Table [dbo].[Ficheros]    Script Date: 2016-06-10 11:14:14 ******/
    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, FILLFACTOR = 90) ON [PRIMARY] FILESTREAM_ON [FILESTREAM_CMMS_DATA],
     CONSTRAINT [IX_Documento] 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
    
    

    i want to create a script to export the schema of the database. I use the wizard to do it.

    To test if all is right, I create a new empty database and try to run the script in this new database, but I get this error:

    Msg 1709, Level 16, State 1, Line 548
    Cannot use TEXTIMAGE_ON when a table has no text, ntext, image, varchar(max), nvarchar(max), non-FILESTREAM varbinary(max), xml or large CLR type columns.
    Msg 4902, Level 16, State 1, Line 1268
    Cannot find the object "dbo.Ficheros" because it does not exist or you do not have permissions.
    Msg 4902, Level 16, State 1, Line 1417
    Cannot find the object "dbo.Ficheros" because it does not exist or you do not have permissions.
    Msg 4902, Level 16, State 1, Line 1421
    Cannot find the object "dbo.Ficheros" because it does not exist or you do not have permissions.

    How could I solve this problem?

    Thanks.

    Friday, June 10, 2016 9:17 AM

Answers

  • After creating a new table just with the primary key and the filestream column, I see that the script don't add the TEXTIMAGE_ON parameter.

    Then I delete this parameter from the script and I ensure that the application works as expected.

    Then, I guess that the problem is that in a first moment the oriingal table had a BLOB column that I convert to filestream column, but the information about the BLOB remains.

    Regards.

    • Marked as answer by ComptonAlvaro Monday, June 13, 2016 8:58 AM
    Friday, June 10, 2016 2:13 PM

All replies

  • After creating a new table just with the primary key and the filestream column, I see that the script don't add the TEXTIMAGE_ON parameter.

    Then I delete this parameter from the script and I ensure that the application works as expected.

    Then, I guess that the problem is that in a first moment the oriingal table had a BLOB column that I convert to filestream column, but the information about the BLOB remains.

    Regards.

    • Marked as answer by ComptonAlvaro Monday, June 13, 2016 8:58 AM
    Friday, June 10, 2016 2:13 PM
  • Hi ComptonAlvaro,

    Have you resolved this issue? If you resolve it, you can mark your reply as answer, that way, other community members could benefit from your solution.


    Thanks,
    Lydia Zhang


    Lydia Zhang
    TechNet Community Support


    Monday, June 13, 2016 8:55 AM
  • Yes, I solved my problem, but I didn't know if it is a good practice to mark as answer my own question, but I will mark it.

    Thanks.

    Monday, June 13, 2016 8:58 AM