How can we help you today? How can we help you today?
jmeyer
Activity overview Badges (0) Posts (30) Comments (40)

Comments

Sort by recent activity
The comparison happens on the primary key, but the foreign key is linked to a unique index. Reproduction: -- Source System USE tempdb; GO SET ANSI_NULLS, QUOTED_IDENTIFIER, ANSI_PADDING ON; GO IF EXISTS ( SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TableXRef]') AND type IN (N'U') ) DROP TABLE dbo.TableXRef; IF EXISTS ( SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TableBase]') AND type IN (N'U') ) DROP TABLE dbo.TableBase; GO CREATE TABLE dbo.TableBase (id INT IDENTITY(1, 1) NOT NULL , NaturalKey VARCHAR(20) NOT NULL , CONSTRAINT PK_dbo_TableBase PRIMARY KEY CLUSTERED (id ASC) ON [PRIMARY] , CONSTRAINT UQ_dbo_TableBase_NaturalKey UNIQUE NONCLUSTERED (NaturalKey ASC) ON [PRIMARY]) ON [PRIMARY]; GO CREATE TABLE dbo.TableXRef (id INT IDENTITY(1, 1) NOT NULL , NaturalKey VARCHAR(20) NOT NULL , DummyData CHAR(4) NOT NULL , CONSTRAINT PK_dbo_TableXRef PRIMARY KEY CLUSTERED (id ASC) ON [PRIMARY] , CONSTRAINT UQ_dbo_TableBase_DummyData UNIQUE NONCLUSTERED (DummyData ASC) ON [PRIMARY]) ON [PRIMARY]; GO ALTER TABLE dbo.TableXRef ADD CONSTRAINT FK_dbo_TableXRef_NaturalKey_dbo_TableBase_NaturalKey FOREIGN KEY ( NaturalKey ) REFERENCES dbo.TableBase ( NaturalKey ); -- generate fake data INSERT INTO dbo.TableBase (NaturalKey ) SELECT number FROM master.dbo.spt_values WHERE number BETWEEN 1 AND 100 GROUP BY number ORDER BY number; INSERT INTO dbo.TableXRef (NaturalKey , DummyData ) SELECT MIN(number) AS number , SOUNDEX(name) AS DummyData FROM master.dbo.spt_values WHERE number BETWEEN 1 AND 100 AND SOUNDEX(name) IS NOT NULL GROUP BY SOUNDEX(name) ORDER BY number; code for the destination system including data destruction to trigger data sync -- Destination system USE tempdb; GO SET ANSI_NULLS, QUOTED_IDENTIFIER, ANSI_PADDING ON; GO IF EXISTS ( SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TableXRef]') AND type IN (N'U') ) DROP TABLE dbo.TableXRef; IF EXISTS ( SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TableBase]') AND type IN (N'U') ) DROP TABLE dbo.TableBase; GO CREATE TABLE dbo.TableBase (id INT IDENTITY(1, 1) NOT NULL , NaturalKey VARCHAR(20) NOT NULL , CONSTRAINT PK_dbo_TableBase PRIMARY KEY CLUSTERED (id ASC) ON [PRIMARY] , CONSTRAINT UQ_dbo_TableBase_NaturalKey UNIQUE NONCLUSTERED (NaturalKey ASC) ON [PRIMARY]) ON [PRIMARY]; GO CREATE TABLE dbo.TableXRef (id INT IDENTITY(1, 1) NOT NULL , NaturalKey VARCHAR(20) NOT NULL , DummyData CHAR(4) NOT NULL , CONSTRAINT PK_dbo_TableXRef PRIMARY KEY CLUSTERED (id ASC) ON [PRIMARY] , CONSTRAINT UQ_dbo_TableBase_DummyData UNIQUE NONCLUSTERED (DummyData ASC) ON [PRIMARY]) ON [PRIMARY]; GO ALTER TABLE dbo.TableXRef ADD CONSTRAINT FK_dbo_TableXRef_NaturalKey_dbo_TableBase_NaturalKey FOREIGN KEY ( NaturalKey ) REFERENCES dbo.TableBase ( NaturalKey ); -- generate fake data INSERT INTO dbo.TableBase (NaturalKey ) SELECT number FROM master.dbo.spt_values WHERE number BETWEEN 1 AND 100 GROUP BY number ORDER BY number; INSERT INTO dbo.TableXRef (NaturalKey , DummyData ) SELECT MIN(number) AS number , SOUNDEX(name) AS DummyData FROM master.dbo.spt_values WHERE number BETWEEN 1 AND 100 AND SOUNDEX(name) IS NOT NULL GROUP BY SOUNDEX(name) ORDER BY number; -- destroy some data DELETE x FROM dbo.TableXRef x WHERE id>80 DELETE b FROM dbo.TableBase b LEFT JOIN dbo.TableXRef x ON b.NaturalKey = x.NaturalKey WHERE x.id IS NULL Deployment script being generated, please note that the foreign key is being disabled but he unique key is supposed to be dropped, this is what is then causing the failure /* Run this script on: QSQL08OCR.tempdb - This database will be modified to synchronize it with: MJ00KVMP.tempdb You are recommended to back up your database before running this script Script created by SQL Data Compare version 12.0.32.3340 from Red Gate Software Ltd at 10/14/2016 9:43:52 AM */ SET NUMERIC_ROUNDABORT OFF GO SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS, NOCOUNT ON GO SET DATEFORMAT YMD GO SET XACT_ABORT ON GO SET TRANSACTION ISOLATION LEVEL SERIALIZABLE GO BEGIN TRANSACTION -- Pointer used for text / image updates. This might not be needed, but is declared here just in case DECLARE @pv binary(16) PRINT(N'Drop constraints from [dbo].[TableXRef]') ALTER TABLE [dbo].[TableXRef] NOCHECK CONSTRAINT [FK_dbo_TableXRef_NaturalKey_dbo_TableBase_NaturalKey] PRINT(N'Drop unused indexes from [dbo].[TableXRef]') ALTER TABLE [dbo].[TableXRef] DROP CONSTRAINT [UQ_dbo_TableBase_DummyData] PRINT(N'Drop unused indexes from [dbo].[TableBase]') ALTER TABLE [dbo].[TableBase] DROP CONSTRAINT [UQ_dbo_TableBase_NaturalKey] PRINT(N'Add rows to [dbo].[TableBase]') SET IDENTITY_INSERT [dbo].[TableBase] ON INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (15, '15') INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (16, '16') INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (17, '17') INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (18, '18') INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (19, '19') INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (20, '20') INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (21, '21') INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (22, '22') INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (23, '23') INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (24, '24') INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (25, '25') INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (26, '26') INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (27, '27') INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (28, '28') INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (29, '29') INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (30, '30') INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (31, '31') INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (32, '32') INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (33, '33') INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (34, '34') INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (35, '35') INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (36, '36') INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (37, '37') INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (38, '38') INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (39, '39') INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (40, '40') INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (41, '41') INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (42, '42') INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (43, '43') INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (44, '44') INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (45, '45') INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (46, '46') INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (47, '47') INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (48, '48') INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (49, '49') INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (50, '50') INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (51, '51') INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (52, '52') INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (53, '53') INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (54, '54') INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (55, '55') INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (56, '56') INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (57, '57') INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (58, '58') INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (59, '59') INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (60, '60') INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (61, '61') INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (62, '62') INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (63, '63') INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (64, '64') INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (65, '65') INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (66, '66') INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (67, '67') INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (68, '68') INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (69, '69') INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (70, '70') INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (71, '71') INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (72, '72') INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (73, '73') INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (74, '74') INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (75, '75') INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (76, '76') INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (77, '77') INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (78, '78') INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (79, '79') INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (80, '80') INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (81, '81') INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (82, '82') INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (83, '83') INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (84, '84') INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (85, '85') INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (86, '86') INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (87, '87') INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (88, '88') INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (89, '89') INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (90, '90') INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (91, '91') INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (92, '92') INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (93, '93') INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (94, '94') INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (95, '95') INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (96, '96') INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (97, '97') INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (98, '98') INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (99, '99') INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (100, '100') SET IDENTITY_INSERT [dbo].[TableBase] OFF PRINT(N'Operation applied to 86 rows out of 86') PRINT(N'Add rows to [dbo].[TableXRef]') SET IDENTITY_INSERT [dbo].[TableXRef] ON INSERT INTO [dbo].[TableXRef] ([id], [NaturalKey], [DummyData]) VALUES (81, '16', 'R525') INSERT INTO [dbo].[TableXRef] ([id], [NaturalKey], [DummyData]) VALUES (82, '16', 'B210') INSERT INTO [dbo].[TableXRef] ([id], [NaturalKey], [DummyData]) VALUES (83, '16', 'C423') INSERT INTO [dbo].[TableXRef] ([id], [NaturalKey], [DummyData]) VALUES (84, '16', 'D110') INSERT INTO [dbo].[TableXRef] ([id], [NaturalKey], [DummyData]) VALUES (85, '16', 'D236') INSERT INTO [dbo].[TableXRef] ([id], [NaturalKey], [DummyData]) VALUES (86, '16', 'T650') INSERT INTO [dbo].[TableXRef] ([id], [NaturalKey], [DummyData]) VALUES (87, '26', 'R165') INSERT INTO [dbo].[TableXRef] ([id], [NaturalKey], [DummyData]) VALUES (88, '32', 'L352') INSERT INTO [dbo].[TableXRef] ([id], [NaturalKey], [DummyData]) VALUES (89, '32', 'S263') INSERT INTO [dbo].[TableXRef] ([id], [NaturalKey], [DummyData]) VALUES (90, '32', 'H133') INSERT INTO [dbo].[TableXRef] ([id], [NaturalKey], [DummyData]) VALUES (91, '32', 'S640') INSERT INTO [dbo].[TableXRef] ([id], [NaturalKey], [DummyData]) VALUES (92, '64', 'A631') INSERT INTO [dbo].[TableXRef] ([id], [NaturalKey], [DummyData]) VALUES (93, '64', 'S332') INSERT INTO [dbo].[TableXRef] ([id], [NaturalKey], [DummyData]) VALUES (94, '64', 'S616') INSERT INTO [dbo].[TableXRef] ([id], [NaturalKey], [DummyData]) VALUES (95, '64', 'P600') SET IDENTITY_INSERT [dbo].[TableXRef] OFF PRINT(N'Operation applied to 15 rows out of 15') PRINT(N'Add indexes to [dbo].[TableXRef]') ALTER TABLE [dbo].[TableXRef] ADD CONSTRAINT [UQ_dbo_TableBase_DummyData] UNIQUE NONCLUSTERED ([DummyData]) ON [PRIMARY] PRINT(N'Add indexes to [dbo].[TableBase]') ALTER TABLE [dbo].[TableBase] ADD CONSTRAINT [UQ_dbo_TableBase_NaturalKey] UNIQUE NONCLUSTERED ([NaturalKey]) ON [PRIMARY] PRINT(N'Add constraints to [dbo].[TableXRef]') ALTER TABLE [dbo].[TableXRef] WITH CHECK CHECK CONSTRAINT [FK_dbo_TableXRef_NaturalKey_dbo_TableBase_NaturalKey] COMMIT TRANSACTION GO / comments
  1. Community
  2. SQL Data Compare
  3. Foreign key issues
The comparison happens on the primary key, but the foreign key is linked to a unique index. Reproduction: -- Source System USE tempdb; GO SET ANSI_NULLS, QUOTED_IDENTIFIER, ANSI_PADDING ON; GO I...
0 votes
Update: I am using options "Disable foreign keys" and "Drop primary keys, indexes, and unique constraints" enabled. Anyway, yes, if I do not enable to option to drop/recreate indexes it will work. However, if I do enable to drop/recreate indexes then SQL DataCompare should be smart enough to then also drop/create/check the corresponding foreign keys instead of disable/enable. And the reason I prefer to drop/create indexes is that I regularly insert/update/delete millions of records via SQL DataCompare and I do not want the indexes to be updated for every single insert/update/delete statement and potentially run into issues with unique constraints or unique indexes. / comments
  1. Community
  2. SQL Data Compare
  3. Foreign key issues
Update: I am using options "Disable foreign keys" and "Drop primary keys, indexes, and unique constraints" enabled. Anyway, yes, if I do not enable to option to drop/recreate indexes it will work. ...
0 votes
Sam: This seems to be fixed in the latest version, I now see little filter icons to the side of the table names when filters are active. / comments
  1. Community
  2. SQL Data Compare
  3. Missing filter indicator
Sam: This seems to be fixed in the latest version, I now see little filter icons to the side of the table names when filters are active.
0 votes
This appears to be fixed via a spinner indicator though it would be nice to rather have a progress indicator and/or table indicator. / comments
  1. Community
  2. SQL Data Compare
  3. Progress indicators missing
This appears to be fixed via a spinner indicator though it would be nice to rather have a progress indicator and/or table indicator.
0 votes
Sam: This appears to be fixed in the latest version. / comments
  1. Community
  2. SQL Data Compare
  3. Table names incorrect in progress dialog box
Sam: This appears to be fixed in the latest version.
0 votes
Andrew: Correct, it should go to SQLCompare. And no, I do not want to disable this manually table by table if I deal with 10s or 100s of columns in a variety of tables. This should be an optional flag as you are overriding explicit table/column design choices. / comments
  1. Community
  2. SQL Data Compare
  3. Automap "feature"
Andrew: Correct, it should go to SQLCompare. And no, I do not want to disable this manually table by table if I deal with 10s or 100s of columns in a variety of tables. This should be an optional f...
0 votes
Well, doesn't look like the window location and size is not always retained. 1) Resize from window to full screen and then back to window --> location/size is retained 2) Resize from window to full screen and then back to window, then close and re-open SQL Compare --> location/size is retained 3) Resize from window to full screen and then back to window then back to full screen, then close and re-open SQL Compare --> location/size is not retained, instead SQL Compare starts out as nearly full screened / comments
  1. Community
  2. SQL Compare
  3. Window location and size not restored
Well, doesn't look like the window location and size is not always retained. 1) Resize from window to full screen and then back to window --> location/size is retained 2) Resize from window to full...
0 votes
The issue with my scenario #3 is that it did not retain the rather small window size and the location. Instead, when it switched from full screen to windowed, the window size was nearly identical to the full screen size 9 (off by a couple of pixels) and the window was centered. This is on a 3-screen system, all screens at 1920x1200 using v12.0.33.3389 / comments
  1. Community
  2. SQL Compare
  3. Window location and size not restored
The issue with my scenario #3 is that it did not retain the rather small window size and the location. Instead, when it switched from full screen to windowed, the window size was nearly identical t...
0 votes
Sam: This appears to be fixed and deployment script generation also seems to be running a bit faster. / comments
  1. Community
  2. SQL Data Compare
  3. Deployment script creation progress
Sam: This appears to be fixed and deployment script generation also seems to be running a bit faster.
0 votes
Matt: Thanks for the reply. May primary issue is that the deployment script get unnecessarily large if you're synchronizing large tables. I regularly sync 100,000+ or even millions of records in our DEV/QA data warehouse environments. Regarding the argument that the target may have changed, then we're having a bigger issue with the synchronization tot he target as the sync itself may no longer be valid. / comments
  1. Community
  2. SQL Data Compare
  3. Including WHERE clause in deployment script
Matt: Thanks for the reply. May primary issue is that the deployment script get unnecessarily large if you're synchronizing large tables. I regularly sync 100,000+ or even millions of records in ou...
0 votes
Total activity
97
Last activity
Member since
Following
0 users
Followed by
0 users
Votes
0
Subscriptions
27