Red Gate forums :: View topic - Conflicts with SQL Server 2012 Sequence objects
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Source Control 3
SQL Source Control 3 forum

Conflicts with SQL Server 2012 Sequence objects

Search in SQL Source Control 3 forum
Post new topic   Reply to topic
Jump to:  
Author Message
riko



Joined: 11 Sep 2013
Posts: 7

PostPosted: Wed Sep 11, 2013 6:29 pm    Post subject: Conflicts with SQL Server 2012 Sequence objects Reply with quote

I have create some sequence objects and successfully checked them into source control.

However, whenever I get latest, they are returned as conflicts, even thought the sequences in the database and source control are shown to be exactly the same.

E.g. (mine and theirs exactly the same):

Code:

-- Sequence

CREATE SEQUENCE [dbo].[seq_EnvID]
AS identifier
START WITH 1
INCREMENT BY 1
MINVALUE -9223372036854775808
MAXVALUE 9223372036854775807
NO CYCLE
CACHE
GO


Note: In this case, "identifier" is a custom type I have created and checked in:
Code:
CREATE TYPE [dbo].[identifier] FROM [bigint] NOT NULL
.

When I select "theirs" and click the get latest button, it fails to run the script against the database, as it generates empty "ALTER" statements for the sequences, e.g.:
Code:

ALTER SEQUENCE [dbo].[seq_EnvID]
GO


If I choose "Keep mine" and check the changes in, I am back to square one.

Please help!
Back to top
View user's profile Send private message
riko



Joined: 11 Sep 2013
Posts: 7

PostPosted: Wed Sep 11, 2013 7:40 pm    Post subject: Reply with quote

It is also worth noting that I was getting a "The given key was not present in the dictionary." error when linking my database to source control in shared database mode (but not in dedicated database mode).

This only happens after I add the sequences to source control. The error goes away again if I remove the sequences from source control, they are simply shown as "new objects" not yet checked in.

Please can you fix SQL Source Control for sequences? I am currently evaluating the product and cannot use it if it doesn't support sequences properly... after all it is almost 18 months since the SQL Server 2012 RTM...
Back to top
View user's profile Send private message
riko



Joined: 11 Sep 2013
Posts: 7

PostPosted: Wed Sep 11, 2013 8:09 pm    Post subject: Reply with quote

I can confirm that the issue (in both cases) seems to be the fact that the sequence has a custom type and not a built-in type.

Even though this is perfectly valid in SQL Server 2012, it is probably not supported by SQL Source Control?

This works:
Code:
CREATE SEQUENCE [dbo].[seq_EnvID] AS bigint


This doesn't:
Code:
CREATE SEQUENCE [dbo].[seq_EnvID] AS identifier


Can someone confirm this? And if it is an issue, is it something that will be fixed in the near future?

Thanks
Riko
Back to top
View user's profile Send private message
james.billings



Joined: 16 Jun 2010
Posts: 1123
Location: My desk.

PostPosted: Tue Sep 17, 2013 1:55 pm    Post subject: Reply with quote

I've just done a quick test of this, and I seem to see the same behaviour as you are.
I'll see if I can find out more...
Back to top
View user's profile Send private message
james.billings



Joined: 16 Jun 2010
Posts: 1123
Location: My desk.

PostPosted: Tue Sep 17, 2013 2:39 pm    Post subject: Reply with quote

OK, the error reports you sent in have been logged as a bug under ref. SOC-5274.
Unfortunately it doesn't look like this will get fixed in the immediate term; currently you're the only person to have hit this, so unless we start to see it happening a lot more from other users it'll not get looked at for a little while. It's actually a problem in the SQL Compare engine rather than SQL Source Control itself which unfortunately makes it a more involved fix.

Sorry to be the bearer of bad news, but thanks for bringing it to our attention.
Back to top
View user's profile Send private message
riko



Joined: 11 Sep 2013
Posts: 7

PostPosted: Tue Sep 17, 2013 3:10 pm    Post subject: Reply with quote

Thanks for confirming the issue.

I guess I'll just ask my employer to cancel my SQL Toolbelt license in the "immediate term" shall I? Wink
Back to top
View user's profile Send private message
riko



Joined: 11 Sep 2013
Posts: 7

PostPosted: Thu Sep 19, 2013 10:02 am    Post subject: Reply with quote

Just a note that I am being forced to check in my sequences if I want to create a migration script, which then subsequently breaks sql source control completely.

So I cannot use the migration scripts feature at all, which is more serious. Previously I could add sequences to my filter and ignore them for source control, which was a sensible workaround.
Back to top
View user's profile Send private message
james.billings



Joined: 16 Jun 2010
Posts: 1123
Location: My desk.

PostPosted: Thu Sep 19, 2013 2:51 pm    Post subject: Reply with quote

Is your migration script referencing the sequences? If so, then I believe it does force you to commit that too.

If not, then you should be able to bypass it, so there's probably a different issue. What's the scenario you're hitting that you need to use Migrations for, out of interest?
Back to top
View user's profile Send private message
riko



Joined: 11 Sep 2013
Posts: 7

PostPosted: Thu Sep 19, 2013 3:09 pm    Post subject: Reply with quote

I am altering tables that contain existing data that I would like to keep. Migration scripts are required for that I believe.

I am currently creating a custom migration script by using a SQL Compare script as a starting point; it doesn't complain about my sequences.

In fact SQL Compare seems perfectly happy with my sequences - it can sync changes to the sequences, the objects that depend on them or the custom type being used in the sequences fine.

It's only SQL Source Control that gets itself all worked up Smile [/code]
Back to top
View user's profile Send private message
james.billings



Joined: 16 Jun 2010
Posts: 1123
Location: My desk.

PostPosted: Thu Sep 19, 2013 3:38 pm    Post subject: Reply with quote

If Compare seems ok, the chances are the problem is in the script-reader side.
Out of interest, can you try running SQL Compare between your DB and its related Working Base folder? (to find that, right-click the URL you're linked to on the source control setup tab, and you'll see the option)
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group