Is it possible to drop an indexed view using SQL Source Control?

samhanes

I have an existing database with an indexed view that I would like to drop. Unfortunately, my build fails on the "Redgate DLM Automation Build" step with the following error:

<div><div><u><u>[19:20:44]</u></u><i><i>[Validating database state] </i>STARTING: Validating database state
</i></div></div><div><div><u><u>[19:20:44]</u></u><i><i>[Validating database state] </i>Starting: C:\Program Files (x86)\Red Gate\DLM Automation Suite 1\SC\SQLCompare.exe /include="Identical" /include="StaticData" /scripts1="C:\BuildAgent\temp\buildTmp\SQL CI\te21jk22.mca\db\state" /server2="np:\\.\pipe\LOCALDB#CD36AE7A\tsql\query" /database2="sqlCI_a74f6775-16ac-421a-93d5-774b63b9d099" /synchronize /tempInstance="Data Source=np:\\.\pipe\LOCALDB#CD36AE7A\tsql\query;Integrated Security=True" /options="DecryptPost2KEncryptedObjects,IgnoreFillFactor,IgnoreWhiteSpace,IncludeDependencies,IgnoreFileGroups,IgnoreUserProperties,IgnoreWithElementOrder,IgnoreDatabaseAndServerName,UseCompatibilityLevel,UseMigrationsV2"
</i></div></div><div><div><u><u>[19:20:44]</u></u><i><i>[Validating database state] </i>SQL Release: activated, edition: standard, serial number: *********
</i><u style="background-color: transparent; color: inherit; font-size: inherit; font-family: Roboto, "Helvetica Neue", Arial, sans-serif;"><u>[19:20:44]</u></u><i style="background-color: transparent; color: inherit; font-size: inherit; font-family: Roboto, "Helvetica Neue", Arial, sans-serif;"><i>[Validating database state] </i>SQL Compare Command Line V11.6.0.1493
</i><u style="background-color: transparent; color: inherit; font-size: inherit; font-family: Roboto, "Helvetica Neue", Arial, sans-serif;"><u>[19:20:44]</u></u><i style="background-color: transparent; color: inherit; font-size: inherit; font-family: Roboto, "Helvetica Neue", Arial, sans-serif;"><i>[Validating database state] </i>==============================================================================
</i><u style="background-color: transparent; color: inherit; font-size: inherit; font-family: Roboto, "Helvetica Neue", Arial, sans-serif;"><u>[19:20:44]</u></u><i style="background-color: transparent; color: inherit; font-size: inherit; font-family: Roboto, "Helvetica Neue", Arial, sans-serif;"><i>[Validating database state] </i>Copyright Copyright c 1999 - 2016 Red Gate Software Ltd
</i><u style="background-color: transparent; color: inherit; font-size: inherit; font-family: Roboto, "Helvetica Neue", Arial, sans-serif;"><u>[19:20:44]</u></u><i style="background-color: transparent; color: inherit; font-size: inherit; font-family: Roboto, "Helvetica Neue", Arial, sans-serif;">[Validating database state] 
</i><u style="background-color: transparent; color: inherit; font-size: inherit; font-family: Roboto, "Helvetica Neue", Arial, sans-serif;"><u>[19:20:44]</u></u><i style="background-color: transparent; color: inherit; font-size: inherit; font-family: Roboto, "Helvetica Neue", Arial, sans-serif;"><i>[Validating database state] </i>Registering data sources
</i><u style="background-color: transparent; color: inherit; font-size: inherit; font-family: Roboto, "Helvetica Neue", Arial, sans-serif;"><u>[19:20:46]</u></u><i style="background-color: transparent; color: inherit; font-size: inherit; font-family: Roboto, "Helvetica Neue", Arial, sans-serif;"><i>[Validating database state] </i>Creating mappings
</i><u style="background-color: transparent; color: inherit; font-size: inherit; font-family: Roboto, "Helvetica Neue", Arial, sans-serif;"><u>[19:20:46]</u></u><i style="background-color: transparent; color: inherit; font-size: inherit; font-family: Roboto, "Helvetica Neue", Arial, sans-serif;"><i>[Validating database state] </i>Comparing</i></div></div><div><div><u><u>[19:20:46]</u></u><i>[Validating database state] 
</i><u style="background-color: transparent; color: inherit; font-size: inherit; font-family: Roboto, "Helvetica Neue", Arial, sans-serif;"><u>[19:20:46]</u></u><i style="background-color: transparent; color: inherit; font-size: inherit; font-family: Roboto, "Helvetica Neue", Arial, sans-serif;"><i>[Validating database state] </i>Applying Command Line Items</i></div></div><div><div><u><u>[19:20:46]</u></u><i>[Validating database state] 
</i><u style="background-color: transparent; color: inherit; font-size: inherit; font-family: Roboto, "Helvetica Neue", Arial, sans-serif;"><u>[19:20:46]</u></u><i style="background-color: transparent; color: inherit; font-size: inherit; font-family: Roboto, "Helvetica Neue", Arial, sans-serif;"><i>[Validating database state] </i>Checking for identical databases
</i><u style="background-color: transparent; color: inherit; font-size: inherit; font-family: Roboto, "Helvetica Neue", Arial, sans-serif;"><u>[19:20:47]</u></u><i style="background-color: transparent; color: inherit; font-size: inherit; font-family: Roboto, "Helvetica Neue", Arial, sans-serif;"><i>[Validating database state] </i>Creating SQL</i></div></div><div><div><u><u>[19:20:48]</u></u><i><i>[Validating database state] </i>Inserting static data SQL into deployment script
</i><u style="background-color: transparent; color: inherit; font-size: inherit; font-family: Roboto, "Helvetica Neue", Arial, sans-serif;"><u>[19:20:48]</u></u><i style="background-color: transparent; color: inherit; font-size: inherit; font-family: Roboto, "Helvetica Neue", Arial, sans-serif;"><i>[Validating database state] </i>Deploying changes (from DB1 to DB2)</i></div></div><div><div><u><u>[19:20:48]</u></u><i><i>[Validating database state] </i>Error: Synchronization of 'Scripts.state' and
</i><u style="background-color: transparent; color: inherit; font-size: inherit; font-family: Roboto, "Helvetica Neue", Arial, sans-serif;"><u>[19:20:48]</u></u><i style="background-color: transparent; color: inherit; font-size: inherit; font-family: Roboto, "Helvetica Neue", Arial, sans-serif;"><i>[Validating database state] </i>'np:\\.\pipe\LOCALDB#CD36AE7A\tsql\query.sqlCI_a74f6775-16ac-421a-93d5-774b63b9d
</i><u style="background-color: transparent; color: inherit; font-size: inherit; font-family: Roboto, "Helvetica Neue", Arial, sans-serif;"><u>[19:20:48]</u></u><i style="background-color: transparent; color: inherit; font-size: inherit; font-family: Roboto, "Helvetica Neue", Arial, sans-serif;"><i>[Validating database state] </i>099' failed: Cannot drop the index
</i><u style="background-color: transparent; color: inherit; font-size: inherit; font-family: Roboto, "Helvetica Neue", Arial, sans-serif;"><u>[19:20:48]</u></u><i style="background-color: transparent; color: inherit; font-size: inherit; font-family: Roboto, "Helvetica Neue", Arial, sans-serif;"><i>[Validating database state] </i>'dbo.myIndexedView.myClusteredIndex', because it does not
</i><u style="background-color: transparent; color: inherit; font-size: inherit; font-family: Roboto, "Helvetica Neue", Arial, sans-serif;"><u>[19:20:48]</u></u><i style="background-color: transparent; color: inherit; font-size: inherit; font-family: Roboto, "Helvetica Neue", Arial, sans-serif;"><i>[Validating database state] </i>exist or you do not have permission. Error executing the following SQL: DROP
</i><u style="background-color: transparent; color: inherit; font-size: inherit; font-family: Roboto, "Helvetica Neue", Arial, sans-serif;"><u>[19:20:48]</u></u><i style="background-color: transparent; color: inherit; font-size: inherit; font-family: Roboto, "Helvetica Neue", Arial, sans-serif;"><i>[Validating database state] </i>INDEX [myClusteredIndex] ON [dbo].[myIndexedView]
</i><u style="background-color: transparent; color: inherit; font-size: inherit; font-family: Roboto, "Helvetica Neue", Arial, sans-serif;"><u>[19:20:48]</u></u><i style="background-color: transparent; color: inherit; font-size: inherit; font-family: Roboto, "Helvetica Neue", Arial, sans-serif;"><i>[Validating database state] </i>FINISHED WITH ERROR: Validating database state
</i></div></div>

I see a related issue from several years ago, but I haven't found any follow up: https://forum.red-gate.com/discussion/20122/bug-in-drop-and-create-with-indexed-view

Can anyone help with this issue? At this point, I can't even remove the view manually, since my database builds without the view always fail.

Jessica R

Hi Sam,

Thanks for your post!

The issue in that older forum post was resolved in SQL Compare v11.1.7.47 and the version of DLM you're using has the SQL Compare engine from v11.6.0.1493 so the fix for that particular issue should be fixed there. (The problem was that DROP/CREATE instead of ALTER would drop a view then try to refresh it.)

To confirm though, the index in question does exist in the repository? If you use the latest version of SQL Compare to compare the repository to a blank database, does it fail with the same error?

samhanes

Thanks for your response Jessica. In this case, we have an existing database with an indexed view, and I have committed a change to our repository to remove both the index and the view. That is, the commit is simply removing the file in the "Views" folder that contains the "CREATE VIEW" and "CREATE INDEX" statements.

The build step that is failing is the "Redgate DLM Automation Build" - which I believe is creating an empty temporary database to compare against. So the issue is not occurring when we attempt to deploy the db change - we aren't even able to build the package.

Thanks again for your help!

Jessica R

Thanks @samhanes!

The DLM Build step uses SQL Compare behind the scenes to compare the repository to the empty temporary database and see if deploying to the empty database succeeds.

As DLM uses SQL Compare 12, I just wanted to check if the issue still happens if you manually run that with the latest SQL Compare 13? (in which case the bug should be fixed in SQL Change Automation which replaced DLM).

If it does still exist in v13 though, we can take a look at the deployment script created to see what's going on.

samhanes

Thanks @Jessica R - I don't have a license for SQL Compare, and our infrastructure team isn't able to upgrade the DLM Build component right now. We are going to explore other options for now - thanks again for your help.

Jessica R

Ah ok, no worries! Just let us know if you'd like to return to this down the line.