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