Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
delpiero
Yak Posting Veteran
98 Posts |
 Posted - 2005-03-29 : 22:21:48
|
| Hi all, Suppose I use DTS import/export wizard to import a Visual Foxpro 7.0 table into SQL Server table. I specify to "create the table" during the import. There are some datetime fields in the VFP table. I found that some datetime fields are converted into "smalldatetime" in SQL while some are converted into "datetime". The existence of smalldatetime fields when creating the SQL Server table causes some insert statements to fail due to date overfloaw. Can I control what fields to be created if I do not wish to create the destination table manually by myself? One more thing, I found that NULL date values in VFP will generate a date value of "1899-12-30 00:00:00.000" in SQL Server during import. Is there any method to control this? Anyway, this date value is one of the reason why the insert to smalldatetime field fails because this date is considered out of range in smalldatetime fields. Thanks a lot,delpiero |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-03-29 : 22:27:28
|
| When you set the destination in DTS, and choose to create a new table, you can specify everything about the new table including its name, column names, and data types. Clicking on the "Create" button will bring up a small window with the CREATE TABLE script it will use to generate it. Simply edit those columns to make them datetime instead of smalldatetime. |
 |
|
|
delpiero
Yak Posting Veteran
98 Posts |
Posted - 2005-03-29 : 22:59:18
|
| Thanks, but is there any rule to determine when will it convert to datetime and when to convert into smalldatetime? Coz I wish to automate the tasks without the need to manually modify the table definitions ...And also the NULL date is difficult to handle during the import. delopiero |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-03-29 : 23:06:53
|
| I don't know what the criteria is for cutoff, I would suppose the FoxPro driver would be the determining factor.As far as automating this, how many tables to you need to import? Is this a one-time migration? If not, are you importing data from the same FoxPro tables every day, for example? You'd only need to create the SQL Server table once. |
|
|
|
delpiero
Yak Posting Veteran
98 Posts |
Posted - 2005-03-30 : 04:06:29
|
| Well, this is a one-time migration. But there are quite a number of destination tables in SQL Server where the data would migrate to. This means that I will need to create a lot of tables as destination, so I wonder if there is any automation.But of course I can still create the table in DTS once, script it and create the remaining ones. Anyway, I think I have got what I need from your reply. Thanks a lot!delpiero |
|
|
|
|
|
|
|
|
datetime field in DTS import/export