script includes ALTER SEQUENCE when it shouldn't
todtrexler
Posts: 7
STEPS TO RECREATE:
I have a set of SEQUENCE objects. The same object exists in all databases, but the sequence is maintained separately for each. Sometimes the "START WITH" value differs between the source and the targets.
I have "SEquence" unchecked in my default filter.
I also have a series of filter files, NONE of which have Sequence checked.
I bring up a comparison, and change to one of my filter files.
The Sequence objects are not shown in the results pain. I select the objects I want to deploy and start the deployment wizard. I select Deploy Using SQL Compare, and both "Recompare after Deployment" and "Save a copy of deployment script" are checked.
I click Next
SQL compare runs through its steps. The Deployment Script pane is shown.
Now, I can search the script for "SEQUENCE" and see that the script includes something like this:
PRINT N'Altering sequences'
GO
ALTER SEQUENCE [dbo].[SequenceSite] RESTART WITH 29
GO
I have to manually remove that or I break our deployments (the sequences are used to provide unique numbers, and if I deploy this is restarts with numbers that have already been used.)
ALSO of note: at the top, under "3 - Review dependencies >" it says "no dependencies". However, I note that if I close the deployment wizard without applying, select the "Nothing Excluded" entry from the "Filter" list, then when I run the wizard again I end up seeing a list of dependencies that includes the sequence objects. If I uncheck "deploy dependencies" and continue, then the sequence objects are not scripted. In fact in comparing these scenarios I see that in my first case, which is my normal workflow, ALL of the depencies are being scripted, and they also include other objects that I do not want to deploy. But in the first case it doesn't list them as dependencies and so never shows me that it is deploying them unless I manually go through the script before applying it.
Any thoughts on how I can filter what I am seeing without causing this issue, or force sql compare to NEVER script alters on Sequences?
I have a set of SEQUENCE objects. The same object exists in all databases, but the sequence is maintained separately for each. Sometimes the "START WITH" value differs between the source and the targets.
I have "SEquence" unchecked in my default filter.
I also have a series of filter files, NONE of which have Sequence checked.
I bring up a comparison, and change to one of my filter files.
The Sequence objects are not shown in the results pain. I select the objects I want to deploy and start the deployment wizard. I select Deploy Using SQL Compare, and both "Recompare after Deployment" and "Save a copy of deployment script" are checked.
I click Next
SQL compare runs through its steps. The Deployment Script pane is shown.
Now, I can search the script for "SEQUENCE" and see that the script includes something like this:
PRINT N'Altering sequences'
GO
ALTER SEQUENCE [dbo].[SequenceSite] RESTART WITH 29
GO
I have to manually remove that or I break our deployments (the sequences are used to provide unique numbers, and if I deploy this is restarts with numbers that have already been used.)
ALSO of note: at the top, under "3 - Review dependencies >" it says "no dependencies". However, I note that if I close the deployment wizard without applying, select the "Nothing Excluded" entry from the "Filter" list, then when I run the wizard again I end up seeing a list of dependencies that includes the sequence objects. If I uncheck "deploy dependencies" and continue, then the sequence objects are not scripted. In fact in comparing these scenarios I see that in my first case, which is my normal workflow, ALL of the depencies are being scripted, and they also include other objects that I do not want to deploy. But in the first case it doesn't list them as dependencies and so never shows me that it is deploying them unless I manually go through the script before applying it.
Any thoughts on how I can filter what I am seeing without causing this issue, or force sql compare to NEVER script alters on Sequences?
Comments
At the time of this reply, SQL Compare V10.4.8.87 does not support SQL 2012 Sequence Objects. Therefore I have submitted Bug Report / Enhancement request SC-6837 for SQL Compare to support sequence objects.
Further to this problem you have found:
This is a known intermittent bug that is affecting a small number of users, where no dependencies are detected when running the deployment wizard. However the process of stepping back through the wizard before stepping forward again through the wizard where the dependencies appear. The Bug Report reference is SC-6770.
Also a support ticket has been created for you, call reference #12796 which can be reviewed HERE.
Senior Product Support Engineer
Redgate Software Ltd
Email: support@red-gate.com