DTS Problems! - Horror Stories Getting Access Time Data into SQL Server via DTS
I came across the problem when attempting
to perform the apparently simple task of transferring a table (which happened
to include dates) from an Access 2000 database to an SQL Server 7.0 database.
You may see the included Access 2000.mdb
so that you can duplicate the problem yourself. The same problem occurred
when transferring from Access 97 to SQL Server 7.0. DTS errors occur when
using DTS to import time format data from an Access 2000 database, to
a SQL server 7.0 databases. I have confirmed this on SQL Server 7.0 (SP1
and SP2), and the SQL 2000 final version. The Microsoft Upsizing Wizard
does not encounter the same problem.
My problem occurred when trying
to export Date/Time fields with a time format (short, medium, long encounter
the same problem). DTS seems to recognise these times all as a data type
of smalldatetime, and tries to copy them as timestamps. This obviously
causes an error, as seen in the screen shots below. The solution is to
change them to datetime via a DTS transform, which seems to work correctly.
Conclusion
For a seamless
transition from Access to SQL Server, the Developer has to use many tools
to:
1. Isolate bad data
2. Find structural problems (the source of most errors)
3. Counter problems in the official Upsizing tools provided by Microsoft.
These tools
include:
- Microsoft SQL Server DTS
- Cutting and Pasting in Microsoft Access
- SSW Upsizing PRO!
With the
deficiencies of DTS in mind, the Upsizing Wizard is your best overall
solution to upsizing. I recommend using the Upsizing Wizard as your primary
tool, with DTS as an ancillary tool for troubleshooting minor problems.
In sum, use Upsizing PRO! in conjunction with the upsizing wizard for
a relatively hassle-free upsizing process.
Without these tools, there is a steep hill to climb, particularly when
you are faced with the upsize of a large and/or complicated legacy Access
database.
David
Klein and Adam Cogan
December 2000