Fulltext index references unknown key index error

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

  Tianjiao_Li November 09, 2018 12:23

  - 0 +
• 

  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.
  

  eaguilar106 November 09, 2018 18:21

  - 0 +
• 
  Would you be able to share the script of the filetable with a full text index to help us a reproduction?
  
  Thanks!

  Tianjiao_Li November 15, 2018 16:24

  - 0 +
• 

  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 November 19, 2018 18:57

  - 0 +
• 
  BTW, I'm using SQL Server 2012 Enterprise
  

  eaguilar106 November 19, 2018 19:08

  - 0 +
• 
  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 November 22, 2018 11:16

  - 0 +
• 
  @eaguilar106
  
  I'm pleased to let you know the fix has gone out to frequent update 7.0.7.8791.

  Tianjiao_Li January 10, 2019 09:48

  - 0 +

Add comment

Please sign in to leave a comment.