I'm trying to migrate data into SQL Server from an existing legacy set of MS Access databases. Currently everything is working except a few core tables that are wide and contain a number of smalldatetime columns. The problem is an error that SQL Server reports when trying to move these tables:

Msg 8114, Level 16, State 8, Line 1 Error converting data type DBTYPE_DBTIMESTAMP to datetime.

I have a stored procedure which generates dynamic sql to assist with moving each table. I converted all the datetime tables in SQL Server to datetime2 columns, and I added a workaround, or so i thought, to deal with this datetime issue, but it doesn't seem to be working. the stored procedures are kind of bulky so the following is the relevant excerpt:

IF (@Conversion = 1 AND (@ColType LIKE '%date%' OR @ColType LIKE '%time%'))
        SET @ColumnList = @ColumnList + ', IIF(ISDATE([' + @ColName + ']) = 1, [' + @ColName + '], CONVERT(datetime2, ''12/31/1899'')) AS ['+ @ColName + ']'
        SET @ColumnList = @ColumnList + ', [' + @ColName + ']'

Currently, the actual data that is "bad" (can't be converted) is not really important, so I'm trying to substitute it for an obviously invalid date just to get the process to complete without errors. The MS Access frontend has no code to prevent users from entering invalid dates, so for example if someone makes a typo and creates an order with the ship date of "june 2nd, 202", instead of "2015", MS Access will happily store this and then SQL Server fails to convert that value to a datetime.

EDIT: To clarify, this procedure is running on a schedule. I can manually repair the bad data now, but if or when more bad data is input by a user, my process will crash again.

As a test, I ran this, and got the same error:


However, this works:


How am I going to convert the data if it causes a crash before it can even be passed into the CONVERT function??

EDIT #2:

These also crash:


Can anyone tell me what I'm doing wrong? I would appreciate it greatly.

  • duckduckgo.com/… has some pretty good answers that might help. – Max Vernon Dec 23 '14 at 22:57
  • I'm afraid I need more information to better understand your question. Can you more thoroughly describe how you are migrating the databases? Are you attempting to use Micrsoft's wizards to do this? Or are you attempting to create linked tables and insert the data into them? – Mark Iannucci Dec 26 '14 at 22:07
  • The MDB backends are attached as "linked servers" from inside MS SQL Server. The driver is Microsoft.ACE.OLEDB.12.0. My testing so far indicates that SQL Server is trying to translate the MS Access smalldatetime type into a datetime type before any user query code is executed. Which causes it to choke on a value that doesnt fit into SQL Server's datetime type, before I can cast it or check it or anything. – cathode Dec 27 '14 at 9:05
  • Even without coding, you could easily set a validation rule on the Access backend tables date fields. Something like Between date()-365 And Date()+365 (which would limit entry to dates within +- 1 year of current date) – Patrick Honorez Sep 8 '15 at 10:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.