Removing schemabound views with fulltext indexes

ooasidnfoisnf

We have a view with schemabind and a fulltext index over this view committed. It has proven to be a bit flunky with redgate overall, but especially now when we are trying to remove it from from source control it seems to just crash after the commit and complains about missing index.

Is this scenario supported at all by redgate or is there a workaround to actually delete it manually?

Alex B

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

ooasidnfoisnf

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

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

Alex B

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

ooasidnfoisnf

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

Alex B

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

ooasidnfoisnf

Yes, committed everything redgate thought was uncommitted every time. Including the fulltext catalog.

ooasidnfoisnf

@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

Alex B

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

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