SSMS does not show or does not script attribute FILESTREAM

Question

I created a table with a column with the FILESTREAM attribute, for example:

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

In the stored procedure, I refer to the PathName() function, which 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 a stored procedure, SSMS prints an error: "The PathName function is valid only for columns with the FILESTREAM attribute." Of course, I created a table with the FILESTREAM attribute enabled (and file stream support is included in the database), and I even added the file to the table.

When I tried to verify that the column is indeed a FILESTREAM column, SQL Server Management Studio is not interacting. The attribute does not appear in the column properties panel, and when I scripted the table, the FILESTREAM attribute does not appear.

Update : another oddity is that my FileStorage table no longer appears in the list of tables provided by SSMS intellisense.

What a deal? How can I manipulate or check the FILESTREAM attribute in SSMS?

Answer 1

If you used the SSMS table constructor to modify your table, the FILESTREAM attribute of your column will be lost. In this case, you need to recreate the column and copy the existing data onto it. here is 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 

Answer 2

Microsoft has confirmed that SSMS loses the FILESTREAM attribute when changing the table in the design of the SSMS table: FILESTREAM settings are lost after changing the size of the field through SSMS

As of 6/7/2012, it has been fixed and is scheduled to be included in the "upcoming release" ...