Thread: MS SQL Migration - Fix to migrate milliseconds

Hi All

I was attempting to migrate a large MSSQL2000 DB when I was surprised to be
inform that I had duplicate entries on the primary key. I then update the
type map for int to be int8 and date to be timestamp which helped but still
gave me problems.  I then realised that the tables that where failings had
timestamps as part of there primary keys (log tables).  On investigation I
found that the milliseconds where not being migrated.

Issue:

MSSQL2000 only show seconds by default for a select
i.e. 2001-12-04 09:57:08.950  becomes  04-12-2001 09:57:09

Fix:

For timestamp fields replace 'select datefield from tables' with 'select
CONVERT(char(30), datefield, 121) as datefield from table'

This was harder than I expected as the current code does a select * to
import the data which required me to create a field list.

I have implemented this fix and attached is the patch and the Migrate_Data()
procedure containing my mods.

Alban

Hi Alban,

Thanks, patch applied to CVS.

Regards, Dave.

> -----Original Message-----
> From: Alban Colley [mailto:AlbanColley@taskcare.com]
> Sent: 08 September 2003 18:00
> To: pgadmin-hackers@postgresql.org
> Subject: [pgadmin-hackers] MS SQL Migration - Fix to migrate
> milliseconds
>
>
> Hi All
>
> I was attempting to migrate a large MSSQL2000 DB when I was
> surprised to be inform that I had duplicate entries on the
> primary key. I then update the type map for int to be int8
> and date to be timestamp which helped but still gave me
> problems.  I then realised that the tables that where
> failings had timestamps as part of there primary keys (log
> tables).  On investigation I found that the milliseconds
> where not being migrated.
>
> Issue:
>
> MSSQL2000 only show seconds by default for a select
> i.e. 2001-12-04 09:57:08.950  becomes  04-12-2001 09:57:09
>
> Fix:
>
> For timestamp fields replace 'select datefield from tables'
> with 'select CONVERT(char(30), datefield, 121) as datefield
> from table'
>
> This was harder than I expected as the current code does a
> select * to import the data which required me to create a field list.
>
> I have implemented this fix and attached is the patch and the
> Migrate_Data() procedure containing my mods.
>
> Alban
>