9

I have the following table column:

[Content] [varbinary](max) NULL

And I want to make it a filestream column so I tried:

alter table dbo.Files
  alter column Content add filestream

But I get the error: 

Incorrect syntax near 'filestream'.

I also tried

alter table dbo.Files
  alter column Content varbinary(max) filestream not null

But I got the error:

Cannot alter column 'Content' in table 'Files' to add or remove the FILESTREAM column attribute.

How can I add filestream to an existing column?

Miguel Moura
  • 36,732
  • 85
  • 259
  • 481
  • I have suspicion you may have to create another column "alter table dbo.Files add Content_new varbinary(max) filestream not null" then copy what you have in the original column over. – Fuzzy Mar 23 '16 at 17:15

1 Answers1

10

you'll need to do the following (sourced from here):

/* rename the varbinary(max) column
eg. FileData to xxFileData */
sp_RENAME '<TableName>.<ColumnName>', 'xx<ColumnName>' , 'COLUMN'
GO

/* create a new varbinary(max) FILESTREAM column */
ALTER TABLE <TableName>
ADD <ColumnName> varbinary(max) FILESTREAM NULL
GO

/* move the contents of varbinary(max) column to varbinary(max) FILESTREAM column */
UPDATE <TableName>
SET <ColumnName> = xx<ColumnName>
GO

/* drop the xx<ColumnName> column */
ALTER TABLE <TableName>
DROP COLUMN xx<ColumnName>
GO
Fuzzy
  • 3,810
  • 2
  • 15
  • 33
  • 2
    If you encounter an error message "A table that has FILESTREAM columns must have a nonnull unique column with the ROWGUIDCOL property." you can add this property to a column with the following T-SQL statement: `ALTER TABLE ALTER COLUMN ADD ROWGUIDCOL`. Note that the column must be of type uniqueidentifier. – Zsolt Oct 09 '20 at 08:32