CODEWITHSUNDEEP
sql-serversql-server-2008datetimesql-server-2008-r2ms-access-2010
Lior
Lior

Reputation: 21

SQL Server 2008 R2 import from access fails on datetime column

I'm trying to import an Access 2010 DB table (*.accdb) to SQL Server 2008 R2, as a new table not to an existing one.

I'm doing it through SQL Server's "Import an Export Data Wizard".

I have two datetime columns that I know for a fact that contain some non valid datetime values (don't ask me how some genius managed to enter bad values there).

So I thought I can map these columns to nvarchar(max) columns in the wizard and deal with the problem later.

But unfortunately after the mapping, I get this message:

Found 2 unknown column type conversion(s) The package will not be run.

This is a screenshot:

import_error.png

Upvotes: 1

Views: 865

Answers (2)

Gord Thompson
Gord Thompson

Reputation: 123549

In addition to the workaround described in another answer, another approach would be to import a query that does the DateTime -> Text conversion, for example,

SELECT CStr(Field1) AS Field1Text, Field2 FROM Table1

like this:

Import1.png

Import2.png

Upvotes: 0

Lior
Lior

Reputation: 21

ok, so i just found a very weird workaround (bug in the wizard?): when you choose the destination column type to be "nvarchar" the size is set automatically to "max" and you can't change that.

switched to "nchar" (next on the combobox list) and the size is set to "50" (deafult). then switched back to "nvarchar" and the size was still "50", pressed "next" button and voila - i can choose to ignore bad values and was able to run the import. weirdest workaround ever.

Upvotes: 1

Related Questions