SSMS not showing or scripting FILESTREAM attribute

Question

I've created a table with a column that has the FILESTREAM attribute applied, like this:

CREATE TABLE dbo.FileStorage
(
    [ID] [uniqueidentifier] ROWGUIDCOL NOT NULL UNIQUE,
    [Filename] [nvarchar](255) NOT NULL,
    [Data] [varbinary](max) FILESTREAM NULL
)
GO

In a stored procedure, I'm referencing the PathName() function that should be available for the Data column above:

CREATE PROCEDURE GetPathName
    -- Add the parameters for the stored procedure here
    @fileId uniqueidentifier,
    @filePath nvarchar(max) output
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    SELECT @filePath = Data.PathName()
    FROM dbo.FileStorage
    WHERE ID = @fileId      
END
GO

However, when I execute the above script to create the stored procedure, SSMS prints an error: "Function PathName is only valid on columns with the FILESTREAM attribute.". I certainly created the table with the FILESTREAM attribute turned on (and filestream support is enabled in the DB), and I've even added a file to the table.

When I tried to verify that the column is indeed a FILESTREAM column, SQL Server Management Studio didn't cooperate. The attribute doesn't appear in the column properties pane, and when I scripted the table out, the FILESTREAM attribute doesn't appear.

Update: another oddity is that my FileStorage table doesn't ever appear in the list of tables presented by SSMS intellisense.

What's the deal? How can I manipulate or verify the FILESTREAM attribute in SSMS?

Answer 1

If you have used SSMS Table Designer to modify your table, the FILESTREAM attribute of your column will be lost. In that case, you need to recreate the column and copy the existing data to it. here's a sample:

/* 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