Post reply

Error on datetime column in MS Access DB linked to SQL Server 2008

  • April 2, 2012 at 2:24 pm

    #259357

    I've created a DB Link to an MS Access database using Microsoft Access Database Engine 2010 Redistributable (Provider=Microsoft.ACE.OLEDB.12.0) on MS SQL 2008, and one Date/Time column in MS Access has "5/31/200" which is out of range for MS SQL and due to this I'm getting this error when importing the data into MS SQL tables:

    Msg 8114, Level 16, State 8, Line 1

    Error converting data type DBTYPE_DBTIMESTAMP to datetime.

    I'm able to isolate the row and column that's causing the problem using openquery:

    SELECT * FROM openquery(DD_Access,'select AppraisalDate from tblData where SortNo = 31')

    But this returns when I try to select out just that one row:

    Msg 8114, Level 16, State 9, Line 1

    Error converting data type (null) to datetime.

    Any suggestions on how to update these values in MS Access through TSQL? I've tried selecting data by IsDate, but I get the DBTYPE_DBTIMESTAMP to datetime error with that.

    Thanks for any suggestions...

    Sam

  • April 2, 2012 at 3:51 pm

    #1467934

    Interesting problem! I've never run into that, and wonder if you could just either select around it???

    Like select all columns by name from the openquery with criteria inside the openquery clause that eliminates the bad row(s) union select all columns by name except the date column, make that an aliased constant with criteria inside the openquery clause that selects the bad row(s), I'll assume the bad row comes up with SortNo = 31.

    Like:

    select sortno, column1, column2, column3, columnthatcontainsfunkydate

    from openquery(DD_Access,'select * from tblData where SortNo <> 31')

    union all

    select sortno, column1, column2, column3, '2012/4/2' columnthatcontainsfunkydate

    from openquery(DD_Access,'select * from tblData where SortNo = 31')

    Notice that for the bad row I substitute a quoted constant for the bad date, aliasing the column.

    I also wonder if you could fix the record through the openquery itself???

    This page has some update query syntax http://rip747.wordpress.com/2007/12/14/linked-servers-performing-select-insert-update-and-delete-crud-actions/.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply