Red Gate forums :: View topic - Compare results not as expected
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Data Compare Previous Versions
SQL Data Compare Previous Versions forum

Compare results not as expected

Search in SQL Data Compare Previous Versions forum
Post new topic   Reply to topic
Jump to:  
Author Message
ronnymr



Joined: 18 Jan 2007
Posts: 2

PostPosted: Fri Jan 19, 2007 12:06 am    Post subject: Compare results not as expected Reply with quote

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.
Back to top
View user's profile Send private message
David Atkinson



Joined: 05 Dec 2005
Posts: 1123
Location: Twitter: @dtabase

PostPosted: Fri Jan 19, 2007 11:45 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail
ronnymr



Joined: 18 Jan 2007
Posts: 2

PostPosted: Fri Jan 19, 2007 4:17 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
David Atkinson



Joined: 05 Dec 2005
Posts: 1123
Location: Twitter: @dtabase

PostPosted: Fri Jan 19, 2007 6:58 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail
kipb7



Joined: 08 Feb 2011
Posts: 13

PostPosted: Fri Sep 16, 2011 12:39 am    Post subject: trailing space Reply with quote

ronnymr wrote,
Quote:
"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'
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6674

PostPosted: Tue Oct 11, 2011 9:12 pm    Post subject: Reply with quote

SQL Data Compare has a "Trim trailing spaces" option that may help.
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group