April 2, 2012 at 2:24 pm
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
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 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy