How can we help you today? How can we help you today?
myoldhouse
Activity overview Badges (0) Posts (1) Comments (1)

Activity overview

Latest activity by myoldhouse

Hi Jessica Thanks for your attention. Regarding your questions: Yes, LocationID is the primary key of this table. Create script for source table : SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Locations](     [LocationID] [hierarchyid] NOT NULL,     [LocationHierarchyLevel]  AS ([LocationID].[GetLevel]()) PERSISTED,     [LocationType] [tinyint] NOT NULL,     [LocationSubtype] [tinyint] NULL,     [LocationDescription] [varchar](25) NOT NULL,     [Classification] [int] NULL,     [Active] [bit] NOT NULL,     [PropagateActivationSettings] [bit] NOT NULL,     [OrderBy] [smallint] NOT NULL,     [ImageCollectionID] [int] NULL,     [GeographicData] [geography] NULL,     [ConcurrencyCheck] [timestamp] NOT NULL,  CONSTRAINT [PK_Locations] PRIMARY KEY CLUSTERED (     [LocationID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],  CONSTRAINT [IX_Locations_Type] UNIQUE NONCLUSTERED (     [LocationID] ASC,     [LocationType] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO Create script for target table : SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Locations](     [LocationID] [hierarchyid] NOT NULL,     [LocationHierarchyLevel]  AS ([LocationID].[GetLevel]()) PERSISTED,     [LocationType] [tinyint] NOT NULL,     [LocationSubtype] [tinyint] NULL,     [LocationDescription] [varchar](25) NOT NULL,     [Classification] [int] NULL,     [Active] [bit] NOT NULL,     [PropagateActivationSettings] [bit] NOT NULL,     [OrderBy] [smallint] NOT NULL,     [ImageCollectionID] [int] NULL,     [GeographicData] [geography] NULL,     [ConcurrencyCheck] [timestamp] NOT NULL,  CONSTRAINT [PK_Locations] PRIMARY KEY CLUSTERED (     [LocationID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],  CONSTRAINT [IX_Locations_Type] UNIQUE NONCLUSTERED (     [LocationID] ASC,     [LocationType] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO Screenshots with Value Details for records with id '/5/2/' in both tables. As you can see, SQL Data Compare is not matching these records but both have the same id: [image] [image] I performed a comparison using the following queries in Sql server management studio: SELECT COUNT(*) FROM caromil_v2_prod.dbo.locations [source] LEFT JOIN caromil_v2dev.dbo.Locations [target] ON [source].locationId = [target].LocationID WHERE [target].LocationID IS NULL SELECT COUNT(*) FROM caromil_v2_prod.dbo.locations [source] RIGHT JOIN caromil_v2dev.dbo.Locations [target] ON [source].locationId = [target].LocationID WHERE [source].LocationID IS NULL SELECT COUNT(*) FROM caromil_v2_prod.dbo.locations [source] INNER JOIN caromil_v2dev.dbo.Locations [target] ON [source].locationId = [target].LocationID WHERE [source].Classification <> [target].Classification SELECT COUNT(*) FROM caromil_v2_prod.dbo.locations [source] INNER JOIN caromil_v2dev.dbo.Locations [target] ON [source].locationId = [target].LocationID WHERE [source].Classification = [target].Classification Obtaining the following results: Only in source (query 1):172 Only in target (query 2):1 In both but different:2 Identical:418 Running the comparison with SQL Data Compare, the result is the following: Only in source (query 1):920 Only in target (query 2):749 In both but different:3 Identical:440 I will really appreciate your help. / comments
  1. Community
  2. SQL Data Compare
  3. Data comparison fails when table primary key is oh hierarchyId type
Hi JessicaThanks for your attention. Regarding your questions:Yes, LocationID is the primary key of this table.Create script for source table :SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO CREATE T...
0 votes
Data comparison fails when table primary key is oh hierarchyId type
  1. Community
  2. SQL Data Compare
I am performing a comparison of the data on tables with a hierarchyId  column as the primary key, but SQL Data compare is unable to match any row based on the value of the primary key, so, after th...
2 followers 3 comments 0 votes
Total activity
3
Last activity
Member since
Following
0 users
Followed by
0 users
Votes
0
Subscriptions
1