Synch null DATES in SQL 2000 to SQL 2005 table returns TODAY

beachldy

When synchronizing a NULL datetime field FROM SQL 2000 TO SQL 2005 , it returns TODAY's date and time instead of NULL (in SQL 2005), so the tables will never rematch on the refresh comparison. I can script both tables with some data in it, if this needs to be tested. NOTE: And yes, the items were SQL COMPARED first, to check for any discreptancies in the schema, etc. They matched. Why are the NULL dates in SQL 2000 coming back with today's date in them in the SQL 2005 table when synched?

richardjm

I can't see this behaviour. What I've done is created a table looking like...

create table datenull (i int identity primary key, data datetime)

On both SQL 2000 and SQL 2005 then in SQL 2000 I inserted the data.

insert into datenull values (null)

After I ran the comparison the SQL the migrate the table was as I would expect

-- Add row to [dbo].[datenull]
SET IDENTITY_INSERT [dbo].[datenull] ON
INSERT INTO [dbo].[datenull] ([i], [data]) VALUES (1, NULL)
SET IDENTITY_INSERT [dbo].[datenull] OFF

What I'm thinking may be happening is there is a data trigger getting in the way of the insert or the field is a timestamp column or similar. If you exclude the column from the comparison it will insert the default data which may be set to today in your case.

If none of the above helps perhaps you can script the schema and some data from the databases to see if we can reproduce the problem.

HTH

beachldy

Do you want a backup so you can just restore? It'll only one one table for 2000 and the other database backup will house the table for 2005.

richardjm

OK if you can e-mail them to me at richard.mitchell@red-gate.com that would be great.