Compare results not as expected

[ronnymr]

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. To rule out invisible characters, I re-keyed the data by hand for a few 'test' rows. I have no clue why I am having this issue. Here is an example of the suspect data '05OE0023' - be careful to enter zero, five, letter O, letter E, zero, zero, two, three. I welcome any suggestions.

[David Atkinson]

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

[ronnymr]

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

[David Atkinson]

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

[kipb7]

ronnymr wrote,

[Brian Donahue]

SQL Data Compare has a "Trim trailing spaces" option that may help.
_________________
Brian Donahue
Technical Support
Red Gate Software Ltd.
44 (0)870 160 0037 ext 8521
US and CAN 1-866-RED GATE ext 8521