Removing schemabound views with fulltext indexes

• 
  Hi @ooasidnfoisnf,
  
  What version of SQL Source Control and SQL Server are you using?  I'm using SQL Server 2017 and SQL Source Control 7.0.33 and I've just created the example from the first reply here https://social.msdn.microsoft.com/Forums/sqlserver/en-US/84500f97-c3cb-4b1a-8f8e-65b3deb6e1d0/how-do-i-create-a-fulltext-index-on-a-view?forum=sqldatabaseengine  and committed the dbo.test table, dbo.vtest view and the full text catalog test_catalog.  This all worked normally and then I deleted the dbo.vtest from the database and committed that and it refreshed the commit page normally.
  
  Is your example different from this and/or am I misunderstanding what's occurring?
  
  Kind regards,
  Alex

  Alex B August 13, 2019 14:34

  - 0 +
• 
  Thanks for the reply,
  We have sql server 2016 64bit, version 13.0.5026.0 specifically
  SQL source control 7.0.31.9527
  
  I'll try test if this issue replicates easily from empty database first.

  ooasidnfoisnf August 14, 2019 07:05

  - 0 +
• 
  Well, I got quite different kind of error this time. 
  
  Created a simple table with two columns...
  Created a new view through sql server management studio.
  
  Committed these to source control
  
  modified view through alter script to include 'with schemabinding'
  
  committed to source control
  
  added unique index on view
  added full text index on the view
  
  committed to source control
  
  now through sql managet studio removed full text index from view through right click context menu
  
  Committed. Somehow it didn't throw error, however, attached file shows the state. It didn't actually manage remove the fulltext index. 
  
  Next commit attempt will throw following error:
  
  

  CheckIn returned with ChangeSet ID=0, nothing to Check in?

  
  

   Didn't expect 0 actual 0

  
  
  

  ooasidnfoisnf August 14, 2019 07:14

  - 0 +
• 
  Hi @ooasidnfoisnf,
  
  What source control system are you linking to?
  
  I've just followed your steps testing against a working folder and I do see that it takes two commits to fully remove the fulltext index, on the second commit I do not receive an error, it just removes the remnant of the fulltext index and then it's fine.  I tried this in both 7.0.33 and 7.0.31.
  
  Kind regards,
  Alex

  Alex B August 21, 2019 20:35

  - 0 +
• 
  @Alex B
  
  tfs in azure dev ops
  each developer with his own database instance
  
  database default collation is  Finnish_Swedish_CI_AS if it has any difference. I could maybe share the test directory i created with above steps?
  
  
  

  ooasidnfoisnf August 22, 2019 06:16

  - 0 +
• 
  Hi @ooasidnfoisnf,
  
  I've tried it again with my default collation and with your stated collation and it's worked in both cases now against TFSVC within Azure Devops.
  
  Once thing I want to clarify - do you commit the fulltext catalog in there also, or was it previously committed or do you uncheck the "commit dependent objects" when committing the fulltext index?
  
  I've been committing it with the index.
  
  Kind regards,
  Alex

  Alex B August 22, 2019 19:23

  - 0 +
• 
  Yes, committed everything redgate thought was uncommitted every time. Including the fulltext catalog.

  ooasidnfoisnf August 23, 2019 06:22

  - 0 +
• 
  @Alex B
  
  -- drop database foo

  
  

  create database foo

  go

  use foo

  
  

  -- link to source control

  
  

  create table dbo.users (

  username varchar(200) ,

  mytext varchar(max),

  age int,

  primary key(username)

  )

  
  

  create view userview as

  select username, mytext, age from users

  
  

  create fulltext catalog userfulltextcatalog

  
  

  -- commit 

  
  

  alter view userview

  with schemabinding

  as select username, mytext, age from dbo.users

  
  

  CREATE UNIQUE CLUSTERED INDEX uniq_userview ON userview(username)

  
  

  create fulltext index on userview (mytext)

  key index uniq_userview

  on userfulltextcatalog

  
  

  --- commit

  
  

  drop fulltext index on userview 

  
  

  -- commit 

  -- goes in to strange state here

  
  

  this results in to state where on commit tab it still shows there being fulltext index on the view in the version control

  ooasidnfoisnf August 23, 2019 06:56

  - 0 +
• 
  Hi @ooasidnfoisnf,
  
  Right I see this as well, where you have to commit again to fully drop the fulltext index.  I thought that when you did this though there was another error afterwards, but that never happened for me; when I commit the drop of the fulltext index the second time it succeeds and everything is in the correct state with no errors.
  
  I have raised the multiple commit issue as SOC-9950 and will post here with any update on it.
  
  Let me know if there is further issue beyond that, but I've not been able to reproduce any subsequent errors unfortunately!
  
  Kind regards,
  Alex

  Alex B August 29, 2019 15:00

  - 0 +
• 
  Hi @ooasidnfoisnf,
  
  I just wanted to update here to say that to get it working in SoC we just need to turn "Ignore WITH element order" on in the comparison options.
  
  We're not sure why that is the case yet, but it seems to get things to work.
  
  I'll update again when I have more information!
  
  Kind regards,
  Alex

  Alex B October 09, 2019 15:21

  - 0 +

Add comment

Please sign in to leave a comment.