Fulltext index references unknown key index error

eaguilar106

I keep getting the error 'Fulltext index references unknown key index...' on initial commit of a sql server database using TFS. The index referenced does indeed exist. It is for a FileTable, which is a fixed schema table. Anybody seen this and have a fix?

Thanks

Tianjiao_Li

Can you try: unlink the database, link it to a brand new empty repository and perform commit of all objects. Can you reproduce the issue? 

eaguilar106

Yes, I have unlinked and relinked several times trying different options to no avail. I also created a new database with a single filetable with a full text index and NOTHING else and tried linking to a new azure devops account with TFS as the source control and I get the same error. I don't think redgate likes filetables. Any help or workaround would be greatly appreciated.

Thank you.

Tianjiao_Li

Would you be able to share the script of the filetable with a full text index to help us a reproduction?

Thanks!

eaguilar106

Sure! See below...

/* First make sure FileStream is enabled on the server instance. You do this through SQL Server Configuration Manager - see books online. Not hard. Next, create a new directory 'C:\FileTablesDemo' for the database filesExecute this script a section at a time, do not just run it. */

use master

EXEC sp_configure filestream_access_level, 2

RECONFIGURE Go

-- if you get an error then FileStream is not enabled. See first note.

　

CREATE DATABASE RedGateFS ON PRIMARY (NAME = RedGateFS_data, FILENAME = 'C:\FileTablesDemo\RedGateFS.mdf'),

FILEGROUP RedGateFSFSGroup CONTAINS FILESTREAM (NAME = RedGateFS_FS, FILENAME = 'C:\FileTablesDemo\RedGateFSFileStream')

LOG ON (NAME = 'RedGateFS_log', FILENAME = 'C:\FileTablesDemo\RedGateFS_log.ldf');

GO

ALTER DATABASE RedGateFS

SET FILESTREAM (NON_TRANSACTED_ACCESS = FULL, DIRECTORY_NAME = N'RedGateFSDataFS')

Use RedGateFS

Go

CREATE TABLE Docs AS FileTable

GO

--Create Full-Text Catalog

Create FullText Catalog RGFullTextCatalog as Default

GO

-- get the PK

DECLARE @pk VARCHAR(50)

SET @pk = (

SELECT OBJECT_NAME(object_id) AS constraints

FROM sys.filetable_system_defined_objects

WHERE OBJECT_NAME(object_id) like ('PK%'));

SELECT @pk -- copy this, should look similar to PK__Docs__5A5B77D59C913F2B, paste in next section

--Create FullText Index

Create FullText Index on dbo.Docs

(name Language 1033, File_stream type column file_type Language 1033)

key Index PK__Docs__5A5B77D5D74C9885 -- REPLACE THIS WITH THE ONE YOU JUST COPIED

on RGFullTextCatalog

with Change_Tracking Auto, StopList=system

Go

/* Now, add this database to source control, in my case TFS on VSTS (devops) Link - no problem, there should be two objects - Docs and the RGFullTextCatalog Commit - looks ok until the end, then you should get the error... "Fulltext index references unknown key index PK__Docs__5A5B77D5D74C9885." Your PK in the error will be different than mine but notice its the same PK that you copied, so it exists.

Thanks for you attention to this, let me know if I can provide any other info. */

eaguilar106

BTW, I'm using SQL Server 2012 Enterprise

Tianjiao_Li

Thanks @eaguilar106 !

I've reproduced and logged as SC-10319 in our internal bug tracking system. 

Please keep an eye on the release note!

Tianjiao_Li

@eaguilar106

I'm pleased to let you know the fix has gone out to frequent update 7.0.7.8791.