Tuesday, September 27, 2011

A table with FILESTREAM column(s) must have a non-NULL unique ROWGUID column

I was trying to add a filestream column in one of my existing tables, initially i thought it would be very easy for me, but when i got the error "A table with FILESTREAM column(s) must have a non-NULL unique ROWGUID column."  i started to look here and there. Surfed on the net but didn't find a solution.
Below is the solution which i tried.

Workaround to produce the problem again

CREATE TABLE Employee (
    [emp_id] [int] IDENTITY(1,1) NOT NULL,
    [emp_name] [nvarchar](150) NULL,
    [emp_FHname] [nvarchar](150) NULL,
    [emp_age] [int] NULL,
    [emp_sex] [bit] NULL,
 CONSTRAINT [PK_Employee_1] PRIMARY KEY CLUSTERED
(
    [emp_id] ASC
)
) ON [PRIMARY]

GO

emp_id is defined as IDENTITY and Primary key

now i wants to add a filestream enabled column named "emp_pic" to store employee pictures, and the table doesn't have any ROWGUIDCOL so i added a empid

ALTER TABLE Employee ADD
    empid UNIQUEIDENTIFIER DEFAULT (newid()) ROWGUIDCOL NOT NULL


After this i executed this statement

ALTER TABLE Employee ADD
    emp_pic [varbinary](max) FILESTREAM
 

it will give you the error  Msg 5505, Level 16, State 1, Line 1
A table with FILESTREAM column(s) must have a non-NULL unique ROWGUID column.

To solve this problem i just changed my primary key column to empid that i added later as ROWGUIDCOL
After changing the primary key, execute the statement

ALTER TABLE Employee ADD
    emp_pic [varbinary](max) FILESTREAM

It will be executed successfully








5 comments:

  1. Hi
    I had the same problem, I just made the ROWGUIDCOL to be unique, and it solved :) thanks for your useful post.

    ReplyDelete
  2. thanks !!! I had the same problem,

    ReplyDelete
  3. Took Anonymous one advice. Kept Primary Key and created a unique constraint on the uniqueIdentifier column..

    ReplyDelete
  4. Took Anonymous 1 advice. Kept Primary Key, but created a unique constraint on uniqueidentifier column. Designated that column as ROWGUIDCOL.

    ReplyDelete
  5. I had same problem, you can add 'id' like a primary key or other kind of identificator, buy always you need a uniqueidentifier with FileStream column,either it dont work.
    Grettings from Costa Rica.

    ReplyDelete

Share This