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








Share This