Full Text Catalogs on FileStream varbinary(max), GUID and a Int primary key.

I'm having some trouble in trying to convert from a table based which stored documents in a varbinary(max) to a filestream variety which
has introduced the need for a ROWGUIDCOL which I have made seems to conflict (if that is the right word) with the primary contactID key which I need to keep.  I can load the filestream coloumn fine and everything seems ok but I cannot create the full text index which return an error (shown in the comments at bottom) which suggests that neither of my keys which are worthy, despite both seeming to be non null and unique.  I have generated the table out in the example below as well as the create index command I am using and the errors.  I have stripped some of the verbose commands out for easier reading.

CREATE TABLE [dbo].[Contact](
	[ContactID] [int] IDENTITY(1,1) NOT NULL,
	[UniqueID] [uniqueidentifier] ROWGUIDCOL  NOT NULL CONSTRAINT [DF_Contact_UniqueID]  DEFAULT (newid()),
	[CVFile] [varbinary](max) FILESTREAM  NULL,
	[CVFileExtension] [nvarchar](5) NULL,
PRIMARY KEY CLUSTERED 
([ContactID] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) 
ON [PRIMARY] FILESTREAM_ON [Generic_FileStream],UNIQUE NONCLUSTERED 

([UniqueID] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]) ON [PRIMARY] FILESTREAM_ON [Generic_FileStream]

CREATE FULLTEXT INDEX ON dbo.Contact
(CVFile TYPE COLUMN CVFileExtension Language 1033 STATISTICAL_SEMANTICS)
KEY INDEX PK_UniqueID On CVDocumentsFTCatalog
WITH STOPLIST=SQLStopList,SEARCH PROPERTY LIST = WordSearchPropertyList,CHANGE_TRACKING AUTO;

--PK_UniqueID is not a valid index to enforce a full-text search key. A full-text search key must be a unique, non-nullable, single-column index which is not offline, is not defined on a non-deterministic or imprecise nonpersisted computed column, does not have a filter, and has maximum size of 900 bytes. Choose another index for the full-text key.
--IX_ContactID is not a valid index to enforce a full-text search key. A full-text search key must be a unique, non-nullable, single-column index which is not offline, is not defined on a non-deterministic or imprecise nonpersisted computed column, does not have a filter, and has maximum size of 900 bytes. Choose another index for the full-text key.

Open in new window


Any suggestions as to what might be blocking this are welcome.
dgloverukAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dgloverukAuthor Commented:
Further to this I found the solution which was 2 part...
Firstly I discovered that SQL management studio unlike the 2000 enterprise version I am used to was far naughtier at showing out of date information... The act of deleting databases via other products and re-creating them you would think could be coped with by 'refreshing' on the node, however many screens and notably in this case the indexes dialogs reported the incorrect index names.  Another wierdness seemed to be that if you changed index names but get an error when clicking ok, the next time you visit the indexes the names look like they have taken despite the error, but closing and re-opening management studio revealed otherwise.
Having discovered these problems I was able to make more sense of what was going on.

CREATE TABLE [dbo].[Contact](
 [ContactID] [int] IDENTITY(1,1) CONSTRAINT [IX_ContactID] PRIMARY KEY NONCLUSTERED ([ContactID] ASC),
 [UniqueID] [uniqueidentifier] ROWGUIDCOL NOT NULL CONSTRAINT [UX_UniqueID] UNIQUE  NONCLUSTERED ([UniqueID] ASC) DEFAULT (newid()),
 [CVFile] [varbinary](max) FILESTREAM  NULL,
 [CVFileExtension] [nvarchar](5) NULL
)

Open in new window


I altered my code so the constraints were created by definition at table create time which set their names in stone.  When I defined the keys without constraints SQL created arbitary names for the indexes which meant my commands to create the catalogs were not correct.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.