From: | "Alban Colley" <AlbanColley(at)taskcare(dot)com> |
---|---|
To: | <pgadmin-hackers(at)postgresql(dot)org> |
Subject: | MS SQL Migration - Fix to migrate milliseconds |
Date: | 2003-09-08 17:00:09 |
Message-ID: | OFEOKNALGJBEDBPMIHIGIENNFAAA.AlbanColley@taskcare.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgadmin-hackers |
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
Attachment | Content-Type | Size |
---|---|---|
migration.patch | application/octet-stream | 3.7 KB |
frmWizard.Migrate_Data.zip | application/x-stuffit | 8.3 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Dave Page | 2003-09-08 18:42:58 | Re: MS SQL Migration - Fix to migrate milliseconds |
Previous Message | Andreas Pflug | 2003-09-08 15:17:29 | Re: Build System/translations |