TableDiff generates incorrect SQL for Datetime with null value (SQL Server 2008 R2)

Question

When I upgraded to R2, tablediff began to generate insert statements with values of    N'Null'     for datetime columns with null values.

This of course fails with "Conversion failed when converting date and/or time from character string." when you try to run the insert

I just installed SP1 for R2 and the problem persists.

Any fix or workaround?

Thanks

 

Answer 1

Hi Brick,

I created a sample and reproduced the issue:

1. Create two tables in different databases: 

USE [TestDB1]

CREATE TABLE [dbo].[tblTableDef](
	[Col1] [int] NOT NULL,
	[Col2] [datetime] NULL
 CONSTRAINT [PK_tblTableDef] PRIMARY KEY CLUSTERED 
(
	[Col1] 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

INSERT INTO tblTableDef VALUES (1,NULL)

USE [TestDB2]

CREATE TABLE [dbo].[tblTableDef](
	[Col1] [int] NOT NULL,
	[Col2] [datetime] NULL
 CONSTRAINT [PK_tblTableDef] PRIMARY KEY CLUSTERED 
(
	[Col1] 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

INSERT INTO tblTableDef VALUES (1,'20110818')

2. Use TableDiff to create a T-SQL script to synchronize the two tables:

"C:\Program Files\Microsoft SQL Server\100\COM\tablediff.exe" -sourceserver localhost -sourcedatabase TestDB1 -sourcetable tblTableDef -destinationserver localhost -destinationdatabase TestDB2 -destinationtable tblTableDef -f D:\table_differences.sql

3. The generated script is:

-- Host: localhost
-- Database: [TestDB2]
-- Table: [dbo].[tblTableDef]
UPDATE [dbo].[tblTableDef] SET [Col2]=N'Null' WHERE [Col1] = 1

In this case, please submit a feedback on our Connect site: http://connect.microsoft.com/sql

 

Jian Kang
Forum Support
Please remember to mark the replies as answers if they help and unmark them if they provide no help. If you have feedback for TechNet Subscriber Support, contact [e-mail].

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Get or Request Code Sample from Microsoft
If you have any feedback, please tell us.

Answer 2

Thanks Jian.

Feedback submitted:  https://connect.microsoft.com/SQLServer/feedback/details/684614/tablediff-generates-incorrect-sql-for-datetime-with-null-value-sql-server-2008-r2

 Note to John Eisbrener: Jian has reproduced the bug, this does not provide a fix or workaround.

Installation of SQL Server 2008 R2 SP1 does not resolve the issue

Answer 3

Would also like to point out that this applies to other data types.  I have the same issue with nulls in varchar/char strings, though no exception is raised as it's a valid date insert.

Answer 4

I am using MS SQL 2008 R2 SQL Express edition. I want to use tablediff.exe for comparing data from 2 tables of different schemas. I get an error 'Conversion failed when converting date and/or time from character string.' if the date column is null. The same thing happens for decimal/numeric field also.

The original version was - 10.50.1600.1. I upgraded it to 10.50.2500 (SP1) as someone in the forum had suggested it. But it still gives the same error. I then applied the last patch on SP1 to take it to 10.50.2796.0 and tried it but still gives the same error.

It seems it was working on SQL server 2005 but this is a bug on SQL server 2008 R2.

Does anybody have a solution for this?

Answer 5

Make sure you view this:

https://connect.microsoft.com/SQLServer/feedback/details/684614/tablediff-generates-incorrect-sql-for-datetime-with-null-value-sql-server-2008-r2

Its is marked incorrectly as resolved - not reproducible.  Is can be reproduced 100% of the time.  

Replace (value, "N'Null'", "NULL")

Where value is the SQL returned

is the only work-around I have used

Hopefully this issue will be resolved in the next SP