Compare results not as expected

Comments

• David Atkinson Redgate › Posts: 1,458 Rose Gold 2

  January 19, 2007 5:45AM

  Hi,
  
  I've tried to reproduce the problem but haven't had any success. Could you please let me know what version of SQL Data Compare you as using. Also, do you have any other columns in the table, and if so, which one is being used as the comparison key.
  
  Lastly, are both tables identical in structure, with the same collation?
  
  (If you can give me a script that creates two tables that cause the problem, it would be even better!)
  
  Thanks,
  
  David
  Red Gate Software

  David Atkinson
  Product Manager
  Redgate Software

  0 · Share on Twitter
• ronnymr Posts: 3

  January 19, 2007 10:17AM

  Thanks, David for the reply.
  
  I am using SQL Data Compare v 5.3.0.68. I would be happy to send also send you some sample data where I am seeing this issue.
  
  Below is the script for the two tables I am comparing:
  
  TABLE 1 – comparison key [ObligationNo] column
  
  CREATE TABLE [dbo].[OE_OBLIGATIONS] (
  [ACTIVITYNUMBER] [nvarchar] (255) NULL ,
  [BUDGET_CLASS] [nvarchar] (255) NULL ,
  [BUDGET_CODE] [nvarchar] (255) NULL ,
  [CONTRACTOR] [nvarchar] (255) NULL ,
  [CONTRACTOR_TYPE] [nvarchar] (255) NULL ,
  [DELETEFLAG] [nvarchar] (255) NULL ,
  [DEOBLIGATIONS] [nvarchar] (255) NULL ,
  [DOCUMENT_TYPE] [nvarchar] (255) NULL ,
  [DUTY] [nvarchar] (255) NULL ,
  [ENTRY_DATE] [nvarchar] (255) NULL ,
  [EXP_DATE] [nvarchar] (255) NULL ,
  [EXPIRATION_DATE] [nvarchar] (255) NULL ,
  [FISCAL_YEAR] [nvarchar] (255) NULL ,
  [FM_OBLIGATION] [float] NULL ,
  [FM_RESERVED] [nvarchar] (255) NULL ,
  [FUNDTYPE] [nvarchar] (255) NULL ,
  [FYNETOBLIGAMT] [float] NULL ,
  [INCREMENTS] [nvarchar] (255) NULL ,
  [LASTMODDATE] [nvarchar] (255) NULL ,
  [LASTMODUSER] [nvarchar] (255) NULL ,
  [OBLIGATION_DATE] [nvarchar] (255) NULL ,
  [OBLIGATIONNO] [nvarchar] (255) NOT NULL ,
  [OE_INVOICES] [float] NULL ,
  [SQLUPDATE] [nvarchar] (255) NULL ,
  [STATUS] [nvarchar] (255) NULL ,
  [TDP_ESTIMATE] [nvarchar] (255) NULL ,
  [UNLIQ_BALANCES] [nvarchar] (255) NULL
  ) ON [PRIMARY]
  GO
  
  
  
  TABLE 2 – comparison key [Obligation_Num] column
  
  CREATE TABLE [dbo].[TDA_OBLIGATIONS_V] (
  [OBLIGATION_NUM] [nvarchar] (255) NULL ,
  [ACTIVITY_NUM] [nvarchar] (255) NULL ,
  [ACTION_MEMO_NUM] [float] NULL ,
  [CONTRACTED_AMT] [float] NULL ,
  [DATE_OBLIGATED] [nvarchar] (255) NULL ,
  [VENDOR_NAME] [nvarchar] (255) NULL ,
  [IDIQ_POOL] [ntext] NULL ,
  [VENDOR_ID] [float] NULL ,
  [TRAVEL_TYPE] [nvarchar] (150) NULL ,
  [TRAVEL_PURPOSE] [nvarchar] (150) NULL ,
  [TRAVEL_BEGIN_DATE] [nvarchar] (150) NULL ,
  [TRAVEL_END_DATE] [nvarchar] (150) NULL ,
  [TYPE_LOOKUP_CODE] [nvarchar] (25) NULL ,
  [DUTY_OFFICER] [nvarchar] (150) NULL ,
  [DISTRIBUTION_NUM] [float] NULL ,
  [QUANTITY_ORDERED] [float] NULL ,
  [AMOUNT_ORDERED] [float] NULL ,
  [QUANTITY_DELIVERED] [float] NULL ,
  [AMOUNT_DELIVERED] [float] NULL ,
  [QUANTITY_BILLED] [float] NULL ,
  [AMOUNT_BILLED] [float] NULL ,
  [QUANTITY_CANCELLED] [float] NULL ,
  [UNLIQUIDATED_AMOUNT] [float] NULL ,
  [GL_ENCUMBERED_DATE] [datetime] NULL ,
  [GL_CANCELLED_DATE] [datetime] NULL ,
  [AM_NUM] [nvarchar] (150) NULL ,
  [USSGL_TRANSACTION_CODE] [nvarchar] (30) NULL ,
  [ACCRUE_ON_RECEIPT_FLAG] [nvarchar] (1) NULL ,
  [BFY] [nvarchar] (25) NULL ,
  [FUND] [nvarchar] (25) NULL ,
  [FUND_DESCRIPTION_TYPE] [nvarchar] (255) NULL ,
  [PROGRAM] [nvarchar] (25) NULL ,
  [REGION] [nvarchar] (25) NULL ,
  [COUNTRY_CODE] [nvarchar] (240) NULL ,
  [COUNTRY_DESCRIPTION] [nvarchar] (240) NULL ,
  [PROJECT_NUM] [nvarchar] (255) NULL ,
  [PROJECT_ID] [float] NULL ,
  [ACTIVITY_ID] [float] NULL ,
  [ACTIVITY_TYPE] [nvarchar] (150) NULL ,
  [PRICE] [float] NULL ,
  [PK] [int] IDENTITY (1, 1) NOT NULL
  ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
  GO

  0 · Share on Twitter
• David Atkinson Redgate › Posts: 1,458 Rose Gold 2

  January 19, 2007 12:58PM

  Hi,
  Would you mind drafting a couple of insert statements to populate these tables with data that causes the issue to occur.
  
  Also, are the tables in the same database, or separate? Is this SQL Server 2000 or 2005?
  
  You mentioned initially that the problem was for a varchar(255) column. Did you mean an nvarchar(255) column?
  
  Thanks for helping us reproduce this,
  
  David
  Red Gate Software

  David Atkinson
  Product Manager
  Redgate Software

  0 · Share on Twitter
• kipb7 Posts: 20

  September 15, 2011 7:39PM

  ronnymr wrote,

  "I am comparing 1 column of data (varchar 255) and getting unexpected results. Although many rows match, there are instances where rows do not match, yet the data appears identical. I have tried querying both database tables with the suspect data and I get matches in both. "

  If your varchar has a trailing space in one version and not in the other, SQL Data Compare will say they are different, but they will look alike.
  You can also do the SELECT * FROM xxx WHERE vchar='hello'
  and get the row from each one -- even though one of them has vchar='hello ' (with trailing space)
  
  even doing len(vchar) will give the space-trimmed length, so you won't see the difference.
  
  One way to see the difference is to do
  select vchar+'x' [vcharx] from xxx where vchar='hello'

  0 · Share on Twitter
• Brian Donahue Posts: 6,590 Bronze 1

  October 11, 2011 4:12PM

  SQL Data Compare has a "Trim trailing spaces" option that may help.

  0 · Share on Twitter