Treat empty strings as NULL results in comparison errors

Compares and synchronizes SQL database content.

Moderators: JonathanWatts, Chris Auckland, David Atkinson, Michelle Taylor, chengvoon.tong

Treat empty strings as NULL results in comparison errors

Postby wtaylor » Fri Sep 20, 2013 12:19 am

Hi,

I recently enabled the option "Treat empty strings as NULL". The results showed what seemed to be false differences.

For example, when comparing a column which contains a Y or an N value, many rows which have a Y on both sides of the comparison were highlighted as being different. When I double-click on an example of this, the pop-up Viewer window indicates that they are a match (there is no crossed-out red equal sign). Neither value contains trailing whitespace. The problem is pervasive and occurs on other column types where the values can independently be verified as identical.

This example was conducted in a SQL Server 2012 database. Both of the compared tables are in the same database instance. The column is defined as "Varchar(2), null" on both sides.

When I disable "Treat empty strings as NULL" the comparison behaves as expected.

Any help is appreciated!
wtaylor
 
Posts: 1
Joined: Thu Sep 19, 2013 11:59 pm
Location: Seattle

Postby Brian Donahue » Mon Sep 23, 2013 11:08 am

I created a sample database and cannot reproduce the issue, so I'm at a loss to explain what you are seeing.
Table in both databases:
Code: Select all
CREATE TABLE [dbo].[Table_1](
   [ID] [int] NOT NULL,
   [data] [varchar](2) NULL,
 CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED
(
   [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO


Data:

Code: Select all
[use database1]
INSERT INTO table_1 (id,data) VALUES(1, 'Y ')
INSERT INTO table_1 (id,data) VALUES(2, 'Y')
INSERT INTO table_1 (id,data) VALUES(3, 'Y')
INSERT INTO table_1 (id,data) VALUES(4, 'Y ')
USE [database2]
INSERT INTO table_1 (id,data) VALUES(1, 'Y ')
INSERT INTO table_1 (id,data) VALUES(2, 'Y')
INSERT INTO table_1 (id,data) VALUES(3, 'Y ')
INSERT INTO table_1 (id,data) VALUES(4, 'Y')


All I can think of is maybe the collations are different between the databases, but then it should always show the tables as different...

I don't think we could work this out unless we has all of your data compare settings and maybe even a backup of the database.
Brian Donahue
 
Posts: 6670
Joined: Mon Aug 23, 2004 10:48 am

Having the same issue

Postby rvaiyapuri » Thu Oct 03, 2013 9:34 pm

Hi

I'm using the latest version - 10.4.8.62. I'm also facing the same issue when trying to compare the tables with 'Treat Empty Strings as NULL' option enabled.

I created tables similar to the ones that you have created and I'm getting incorrect results (high-lighting equal values as differences).

Can you please help.
rvaiyapuri
 
Posts: 4
Joined: Fri Apr 12, 2013 12:45 am

Postby Brian Donahue » Fri Oct 04, 2013 1:58 pm

Again, we'll probably need to see the databases to reproduce and fix the problem.
Brian Donahue
 
Posts: 6670
Joined: Mon Aug 23, 2004 10:48 am

Postby Brian Donahue » Wed Oct 09, 2013 10:14 am

Treat empty strings as NULL appears to be broken and we have logged a bug SDC-1651.

The problem seems to be in the results display. When you actually use SQL Data Compare to create the script, it does not try to update any of these columns it has erroneously identified as being different.
Brian Donahue
 
Posts: 6670
Joined: Mon Aug 23, 2004 10:48 am

Postby chetmus » Mon Jun 16, 2014 6:25 pm

We are currently using version 10.7.0.23 and still seeing the issue. Is there a scheduled release date for the fix to this issue ?
chetmus
 
Posts: 1
Joined: Mon Jun 16, 2014 6:18 pm
Location: United States

Postby jmeyer » Tue Jun 24, 2014 6:07 pm

I can confirm this behavior under 10.7.0.23 as well
jmeyer
 
Posts: 21
Joined: Fri Jun 05, 2009 3:07 pm

Postby yagerlin56 » Mon Jul 21, 2014 1:22 pm

String a = null, it will only save a string type in the stack pointer, but the pointer is not just to any string heap.
String a = string. The Empty, in addition to save a string type in the stack pointer, the pointer points to the string data of pile, the Empty string, the string and a = "";Is the same.


_____________________________
http://www.mashgear.com
yagerlin56
 
Posts: 1
Joined: Mon Jul 21, 2014 1:19 pm

jammer

Postby ten » Thu Aug 21, 2014 10:30 am

The Bluetooth Jammer is the good device to use,you can get more details at worldjammer.com
ten
 
Posts: 1
Joined: Thu Aug 21, 2014 10:28 am


Return to SQL Data Compare 10

Who is online

Users browsing this forum: No registered users and 0 guests