Red Gate forums :: View topic - Treat empty strings as NULL results in comparison errors
Return to www.red-gate.com RSS Feed Available

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

Treat empty strings as NULL results in comparison errors

Search in SQL Data Compare 10 forum
Post new topic   Reply to topic
Jump to:  
Author Message
wtaylor



Joined: 19 Sep 2013
Posts: 1
Location: Seattle

PostPosted: Fri Sep 20, 2013 12:19 am    Post subject: Treat empty strings as NULL results in comparison errors Reply with quote

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!
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6668

PostPosted: Mon Sep 23, 2013 11:08 am    Post subject: Reply with quote

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:
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:

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



Joined: 12 Apr 2013
Posts: 4

PostPosted: Thu Oct 03, 2013 9:34 pm    Post subject: Having the same issue Reply with quote

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



Joined: 23 Aug 2004
Posts: 6668

PostPosted: Fri Oct 04, 2013 1:58 pm    Post subject: Reply with quote

Again, we'll probably need to see the databases to reproduce and fix the problem.
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6668

PostPosted: Wed Oct 09, 2013 10:14 am    Post subject: Reply with quote

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



Joined: 16 Jun 2014
Posts: 1
Location: United States

PostPosted: Mon Jun 16, 2014 6:25 pm    Post subject: Reply with quote

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



Joined: 05 Jun 2009
Posts: 15

PostPosted: Tue Jun 24, 2014 6:07 pm    Post subject: Reply with quote

I can confirm this behavior under 10.7.0.23 as well
Back to top
View user's profile Send private message
yagerlin56



Joined: 21 Jul 2014
Posts: 1

PostPosted: Mon Jul 21, 2014 1:22 pm    Post subject: Reply with quote

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



Joined: 21 Aug 2014
Posts: 1

PostPosted: Thu Aug 21, 2014 10:30 am    Post subject: jammer Reply with quote

The Bluetooth Jammer is the good device to use,you can get more details at worldjammer.com
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