script includes ALTER SEQUENCE when it shouldn't

Compares and synchronizes SQL Server databases, backups and scripts.

Moderators: JonathanWatts, Chris Auckland, David Atkinson, eddie davis, Anu Deshpande, Michelle Taylor, alice.easey, james.billings, chengvoon.tong

script includes ALTER SEQUENCE when it shouldn't

Postby todtrexler » Thu Feb 06, 2014 3:45 pm

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?
todtrexler
 
Posts: 3
Joined: Fri Nov 22, 2013 1:05 am

Postby eddie davis » Tue Feb 11, 2014 3:18 pm

Thank you for your forum post.

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:
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.


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.
Eddie Davis
Technical Support Engineer
Red Gate Software Ltd
E-mail: support@red-gate.com
eddie davis
 
Posts: 943
Joined: Wed Jun 14, 2006 3:47 pm
Location: Red Gate Software


Return to SQL Compare 10

Who is online

Users browsing this forum: No registered users and 0 guests