SSDT script generation bug - fulltext index on views created last

SSDT script generation bug - fulltext index on views created last

So I came across the following bug today:

If you have an indexed view with a fulltext index defined on it, SSDT generates the CREATE FULLTEXT INDEX statement at the end of the script. If, however, you have a stored procedure which contains a fulltext statement accessing that view, the generated script fails because the fulltext index does not yet exist.

The steps to reproduce are the following:

1. Create a table with two columns

CREATE TABLE [dbo].[Test]
(
  [Id] INT NOT NULL PRIMARY KEY,
  [TextColumn] NVARCHAR(200) NULL
)

2. Create an indexed view

CREATE VIEW [dbo].[vTest]
WITH SCHEMABINDING
AS SELECT Id, TextColumn FROM [dbo].[Test]

CREATE UNIQUE CLUSTERED INDEX [Index1]
 ON [dbo].[vTest] (Id)


3. Create a fulltext catalog

CREATE FULLTEXT CATALOG [FullTextCatalog1]

4. Create the fulltext index

CREATE FULLTEXT INDEX
 ON [dbo].[vTest] ([TextColumn])
 KEY INDEX [Index1]
 ON [FullTextCatalog1]
 WITH CHANGE_TRACKING AUTO


5. Create a stored procedure which does a fulltext query on the view

CREATE PROCEDURE [dbo].[Procedure1]
AS
 SELECT *
 FROM dbo.vTest
 WHERE CONTAINS(*, 'a*')
RETURN 0

6. Ensure that Create script (.sql file) is ticked in Project settings

7. Build the project.

8. The generated .sql file has the stored procedure being created before the fulltext index has been created, thus the script fails when run.

Thanks for the detailed steps for reproducing this issue. I have carried out the sequence you described above in our most recent builds. This issue has been fixed and no longer reproduces in our current bits! Both the script created on build (via the option on Project Settings) and a Publish script generated from the publish action have the procedure being created last. Both scripts were able to be executed successfully against a target server.

Thanks again for using SSDT and giving us detailed feedback regarding your issues,

Copyright © 2007-2012 www.chuibin.com Chuibin Copyright