August 4, 2006 at 7:33 am
Hello All,
We are wanting to use SS2000 as a Data Warehouse for month-end instances of our DB2. Using the Import/Export Wizard, I was able to import a couple of tables as a test. Unfortunately, I ran into the Datetime problem, but changed the datatype in the transformation from smalldatetime to datetime and it worked.
Is their a way to write some code to have the datatype of date in all DB2 tables to be Datetime in SS and not Smalldatetime? I don't want to have to go through hundreds of tables and manually change the datatype in the transformation.
Unfortunately, I do NOT have any sql server knowledge. I scoured the internet for info, but couldn't find much for DB2 to SS.
TIA,
Jeff
August 7, 2006 at 8:21 am
I don't know much about DB2, but the major difference between SQL Server datetime and smalldatetime is that smalldatetime is not accurate for any time less than a minute, while datetime is accurate down to three-hundreds of a second. (I'd make sure I needed the accuracy before I ran too far on that.)
I ran into a similar situation moving a db from MySQL to SQL Server and I ended up writing a DTS package in which I created the tables by code, not relying on the Import/Export Wizard to figure out what I wanted.(This saved a lot of time debugging code later in the project.)
I believe you have two good options:
1.You could develop a DTS package that creates the SQL Server tables as you specify. (i.e. Create all datetime fields as datetime, not smalldatetime.) Then have other steps that move the data as needed.
2.You could run the import/export wizard once to get the basic table structure. Then drop all of the data. Then change the date fields in the SQL Server DB that are smalldatetime to datetime and try the data import again.
Good Luck!
Just curious why are you leaving DB2 for SQL Server? Is it the better tools?
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply