Filtering Duplicates In SQL2005 Doesn't Work Anymore

Hi,

Made the jump from SQL2000 to 2005 - i'm not a DBA but like to play around with such nice tools- and tried to convert my DTS package into a 2005 one.

I've got a csv file containing some log in information.

As users log in everyday, this keeps getting added to the file.

I'm only interested in the latest appearance of a certain machine on the network so i filter by creating an unique Index

the CSV file get's imported into a temporary table first and then i executed the following on SQL2000



CREATE TABLE [patchings].[dbo].[allfilter] (

[username] varchar (20) NULL,

[machine] varchar (20) NULL,

[os] varchar (20) NULL,

[sp] varchar (20) NULL,

[date] smalldatetime NULL,

[time] varchar(10) NULL,

[logonserver] varchar (20) NULL,

[country] varchar (20) NULL,

[domain] varchar (20) NULL,

varchar (20) NULL,

[brand] varchar (20) NULL,

[model] varchar (20) NULL,

[speed] int NULL

)

CREATE unique INDEX [my filter] ON [dbo].[allfilter]([machine], [os]) with ignore_dup_key ON [PRIMARY]

go

insert into "Allfilter" select * from temptable order by date desc

This worked perfectly in SQL2000.

when i try to do the same in SQL2005, it doens't start inserting the most recent machines (filtered on the date) but machines at random



selecting the select * from temptable order by date desc shows me the correct order for the machines by date so i'ld presume that it adds the most recent combination of machine and operating system from "today" and ignores the older appearances.

if the temptable contained for instance..

MY-MACHINE MY_USER Windows XP Service Pack 2 2006/06/13 10:02:34 Mydomaincontroller BE MY_DOMAIN my_ip IBM type 2593
MY-MACHINE MY_USER Windows XP Service Pack 2 2006/06/15 9:59:59 Mydomaincontroller BE MY_DOMAIN my_ip IBM type 2593
MY-MACHINE MY_USER Windows XP Service Pack 2 2006/06/16 12:33:09 Mydomaincontroller BE MY_DOMAIN my_ip IBM type 2593
MY-MACHINE MY_USER Windows XP Service Pack 2 2006/06/19 10:48:56 Mydomaincontroller BE MY_DOMAIN my_ip IBM type 2593
MY-MACHINE MY_USER Windows XP Service Pack 2 2006/06/20 11:03:49 Mydomaincontroller BE MY_DOMAIN my_ip IBM type 2593



under SQL2000 i'ld see one record in the final "allfilter" table

MY-MACHINE MY_USER Windows XP Service Pack 2 2006/06/20 11:03:49 Mydomaincontroller BE MY_DOMAIN my_ip IBM type 2593

under SQL2005 i don't see the last one though coding is the same

MY-MACHINE MY_USER Windows XP Service Pack 2 2006/06/16 12:33:09 Mydomaincontroller BE MY_DOMAIN my_ip IBM type 2593


I guess i'm missing something here but what?

Tnxs

K