Add filestream to an existing table column

Question

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?

Answer 1

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