Activity overview
Latest activity by redeye
The SQL Backup Agent service cannot start
I have installed all my Red-Gate tools (from my toolbelt) onto a new machine running Windows 7 (64 bit, ultimate) ( :P ) and SQL 2008. The SQL server is installed on the same machine. When install...
Collation mismatch warning message
I am comparing two databases where the collations are different. I have set the "Force Binary Collation" option.
Using the synchronisation wizard, it is still giving me a warning saying: "Collation...
Thanks Richard ... but to be clear, it is not just empty tables that this should work for. If two tables are the same but theior seeds are different, Data Compare does nothing. It should synch them up. That shouldn't need a change to the UI -- you already have the tick box which says "synchronise seeds" ... just make that work! [image]
Regards
Paul / comments
Thanks Richard ... but to be clear, it is not just empty tables that this should work for. If two tables are the same but theior seeds are different, Data Compare does nothing. It should synch them...
Richard,
I'm still getting this problem. Could it be that the project was originally set up as comparing "left to right" and now I am comparing "right to left". When the project opens and refreshes the schema, could it be using the seeds from the left hand database ?
The point is, after a synchronisation, I am getting primary key errors. There is then no point in re-synching the databases after that as they are identical, and (as reported by me in a previous post), if the tables are identical (or empty) but the seeds are different, Data Compare does nothing about this.
I really feel this behaviour is erroneous given there is an option to "reseed identity columns". If Data compare says they are the same then the behaviour on both databases should be the same... but it isn't. I understand where you are coming from by way of explanation, but from where I am sitting, this is a bug! [image]
Regards
Paul / comments
Richard,
I'm still getting this problem. Could it be that the project was originally set up as comparing "left to right" and now I am comparing "right to left". When the project opens and refreshes...
Yep, that seems to fix it. If you have a project which compares "A" against "B", and simply switch synchronisation direction after the compare is done, then you get this problem. If, as you say, you refresh the schema after switching direction (or exit Data Compare and start it up again -- assuming you have saved the project after switching direction) then the problem does indeed go away.
It is only when you do everything "in one go" without a schema refresh that the problem occurs.
Thanks for your help.
Paul / comments
Yep, that seems to fix it. If you have a project which compares "A" against "B", and simply switch synchronisation direction after the compare is done, then you get this problem. If, as you say, yo...
To reproduce the problem, please do the following:
1) Create a table on the source database:
CREATE TABLE [dbo].[SeedError](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Data] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_SeedError] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
2) Use SQL compare and have the same table created on the destination databse
3) On the source database, execute the following:
INSERT INTO [SeedError] ( [Data] )
VALUES ( 'Row 1' )
INSERT INTO [SeedError] ( [Data] )
VALUES ( 'Row 2' )
4) Use SQL Data Compare to synch up the contents of this table
5) On the DESTINATION database, execute the following:
INSERT INTO [SeedError] ( [Data] )
VALUES ( 'Row 3' )
INSERT INTO [SeedError] ( [Data] )
VALUES ( 'Row 4' )
6) In SQL Data Compare, switch direction of the synchronisation, and run the wizard for this table. Look at the SQl that is generated. You will see:
-- Reseed identity on [dbo].[SeedError]
DBCC CHECKIDENT('[dbo].[SeedError]', RESEED, 0)
GO
That is incorrect. If you run the synchronisation and then try and insert a row on the source database, you will get a primary key vioaltion error.
Regards
Paul / comments
To reproduce the problem, please do the following:
1) Create a table on the source database:
CREATE TABLE [dbo].[SeedError](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Data] [nvarchar](50) NOT NULL,
CONST...
Seeds incorrect if direction of synchronisation is changed
I have set up a project where I want to synchronise the contents of a database on my local machine to a remote server. That works fine. If I change the direction of the synchronisation, the data is...
Hi Richard,
Thanks for your reply. Yes ... that is exactly waht I have done to get the seeds synched up with each other.
I understand exactly what you are saying, but surely an option to "make sure seeds are synched" would be helpful. Often, I create new tables, load them up with test data, and then deploy an empty table to a live server. This may be a bit obscure, but I can't be the only one who deas that!
Anyway... to have at least one row in the table does the job.
Thanks again
Paul / comments
Hi Richard,
Thanks for your reply. Yes ... that is exactly waht I have done to get the seeds synched up with each other.
I understand exactly what you are saying, but surely an option to "make sure...
Seeds are different if table is empty
I have a table which I use to generate unique customer reference numbers. The customer reference number is based on the seeded value generated when a row is inserted. (FYI, I apply a check digit an...