Removing schemabound views with fulltext indexes

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?
Tagged:

Comments

  • Alex BAlex B Posts: 1,151 Diamond 4
    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
    Product Support Engineer | Redgate Software

    Have you visited our Help Center?
  • 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.
  • 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 BAlex B Posts: 1,151 Diamond 4
    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
    Product Support Engineer | Redgate Software

    Have you visited our Help Center?
  • @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 BAlex B Posts: 1,151 Diamond 4
    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
    Product Support Engineer | Redgate Software

    Have you visited our Help Center?
  • Yes, committed everything redgate thought was uncommitted every time. Including the fulltext catalog.
  • @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 BAlex B Posts: 1,151 Diamond 4
    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
    Product Support Engineer | Redgate Software

    Have you visited our Help Center?
  • Alex BAlex B Posts: 1,151 Diamond 4
    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
    Product Support Engineer | Redgate Software

    Have you visited our Help Center?
Sign In or Register to comment.