locked
FILESTREAM DATATYPE-HOW TO ALTER A COLUMN TO FILESTREAM DATATYPW RRS feed

  • Question

  • Hi,

    I have added the support to FileStream Data Type to my database.

    How to update the table to support the filestram datatype.

    i have done this

    ALTER

     

    TABLE QuoteHistory

     

    ALTER COLUMN QuoteFile VARBINARY(MAX) FILESTREAM NULL

    But it throws error:

    Cannot alter column 'QuoteFile' in table 'QuoteHistory' to add or remove the FILESTREAM column attribute.

    How to overcome this issue

    thanks in advance.

    Tuesday, June 8, 2010 4:21 AM

Answers

  • Hi Rao,

     

    As far as I know we cannot add the FILESTREAM column attribute to an existing column. We can add another VARCHAR (MAX) FILESTREAM column, move data from the old column to the new column, then drop the old column and rename the new column. Please note, when using FILESTREAM storage, each row of the table must have a unique row ID.

    This is shown is the following example

    --Creating a TEST table

    CREATE TABLE TEST

      (

         Column1 VARBINARY(MAX),

         Column2 UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE 

      )

    --Inserting record into the TEST table

    INSERT INTO TEST

    VALUES (CAST('Baseball Catelog' AS VARBINARY(MAX)),NEWID())

    --Adding a new column Column3  in the FileStream

    ALTER TABLE TEST ADD Column3 VARBINARY(MAX) FILESTREAM

    --Updating TEST TABLE

    UPDATE TEST SET Column3=Column1

    --Dropping column Column1

    ALTER TABLE TEST DROP COLUMN Column1

    --Renaming the Column3 column in the TEST table to Column1

    EXEC SP_RENAME 'TEST.Column3', 'Column1', 'COLUMN'

     

     

    Thanks,

    Ai-Hua Qiu

     


    Constant dropping wears away a stone.
    • Proposed as answer by oj-Updated Thursday, June 10, 2010 6:50 AM
    • Marked as answer by KJian_ Monday, June 14, 2010 11:30 AM
    Thursday, June 10, 2010 6:34 AM

All replies

  • Hi,

    I think you have to drop & recreate the table.

    Right now you can not do with alter table.

    please see below links :

    http://www.sqlmonster.com/Uwe/Forum.aspx/sql-server-programming/75565/SQL-2008-attribute-FILESTREAM-problem

    http://social.msdn.microsoft.com/Forums/en/transactsql/thread/e4ae96e8-fe79-4235-a89b-018e3db26541

     

    Please Vote & "Mark As Answer" if this post is helpful to you.

    Cheers
    Bikash Dash
    MCDBA/MCITP

    • Proposed as answer by oj-Updated Thursday, June 10, 2010 6:50 AM
    Tuesday, June 8, 2010 4:50 AM
  • Hi Rao,

     

    As far as I know we cannot add the FILESTREAM column attribute to an existing column. We can add another VARCHAR (MAX) FILESTREAM column, move data from the old column to the new column, then drop the old column and rename the new column. Please note, when using FILESTREAM storage, each row of the table must have a unique row ID.

    This is shown is the following example

    --Creating a TEST table

    CREATE TABLE TEST

      (

         Column1 VARBINARY(MAX),

         Column2 UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE 

      )

    --Inserting record into the TEST table

    INSERT INTO TEST

    VALUES (CAST('Baseball Catelog' AS VARBINARY(MAX)),NEWID())

    --Adding a new column Column3  in the FileStream

    ALTER TABLE TEST ADD Column3 VARBINARY(MAX) FILESTREAM

    --Updating TEST TABLE

    UPDATE TEST SET Column3=Column1

    --Dropping column Column1

    ALTER TABLE TEST DROP COLUMN Column1

    --Renaming the Column3 column in the TEST table to Column1

    EXEC SP_RENAME 'TEST.Column3', 'Column1', 'COLUMN'

     

     

    Thanks,

    Ai-Hua Qiu

     


    Constant dropping wears away a stone.
    • Proposed as answer by oj-Updated Thursday, June 10, 2010 6:50 AM
    • Marked as answer by KJian_ Monday, June 14, 2010 11:30 AM
    Thursday, June 10, 2010 6:34 AM