none
Filestream Column in SQL Management Studio RRS feed

  • Question

  •  

    I have enabled filestream on my server an my database.  I am creating tables within SQL Server Management Studio.  Is there any way to add the filestream attribute to the varbinary(max) column in the IDE?
    Friday, November 14, 2008 6:07 PM

Answers

  • It looks like there is no support for the FILESTREAM attribute in the Table Designer in Management Studio in SQL Server 2008 (you can specify this attribute only by using the CREATE TABLE or ALTER TABLE statements, in a Query Window). 

    Moreover, after you add this attribute, if you make a change in the Table Designer that requires the table to be re-created, the FILESTREAM attribute is lost (however, by default Management Studio does not allow this, because the "Prevent saving changes that require table re-creation" option is checked by default).

    Razvan
    Sunday, January 4, 2009 6:28 PM

All replies

  • Once you configure the filestream on the server, you need to add a filegroup to the database with CONTAINS FILESTREAM attribute.

    Below code snippet is taken from Using FILESTREAM to Store BLOBs in the NTFS File System in SQL Server 2008.

    --Modify the database with filestream attribute  
    ALTER DATABASE fs  
    ADD FILEGROUP fs_fg_filestream CONTAINS FILESTREAM  
    GO  
    ALTER DATABASE fs  
    ADD FILE  
    (  
        NAME'fs_filestream',  
        FILENAME = 'C:\db\fs' 
    )  
    TO FILEGROUP fs_fg_filestream  
    GO  
     
    --Create a table to hold the varbinary  
    CREATE TABLE dbo.BLOB (  
     ID UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE,  
     BLOB VARBINARY(MAX) FILESTREAM NULL 
    )  
     
    --Insert varbinary data  
    DECLARE @ID UNIQUEIDENTIFIER  
    SET @ID = NEWID()  
    INSERT INTO dbo.BLOB  
     (ID, BLOB)  
    VALUES   
     (@ID, CAST('BLOB Placeholder' AS VARBINARY(MAX)))  
    SELECT ID, BLOB   
    FROM dbo.BLOB  
    WHERE ID = @ID  
    SELECT BLOB.PathName()   
    FROM dbo.BLOB  
    WHERE ID = @ID  
     

    Sankar Reddy | http://sankarreddy.spaces.live.com |
    Saturday, January 3, 2009 8:38 AM
    Moderator
  • It looks like there is no support for the FILESTREAM attribute in the Table Designer in Management Studio in SQL Server 2008 (you can specify this attribute only by using the CREATE TABLE or ALTER TABLE statements, in a Query Window). 

    Moreover, after you add this attribute, if you make a change in the Table Designer that requires the table to be re-created, the FILESTREAM attribute is lost (however, by default Management Studio does not allow this, because the "Prevent saving changes that require table re-creation" option is checked by default).

    Razvan
    Sunday, January 4, 2009 6:28 PM