I am in the middle of designing a SP where in i have to import some data elements from oracle DB to SQL server db. In this process i am importing it into a temp table. i am getting the below error during import on tran_Date column.
"Error converting data type DBTYPE_DBTIMESTAMP to datetime.
Now i have narrowed down the no of columns from 20 to 5 and tested with the below solution posted on some website.
Insert into SQL_TABLE( col1, col2, col3, tran_date, col5)
select * from openquery (LINKED_SERVER, select col1, col2, col3, fn convert(tran_date,SQL_TIMESTAMP)}
Now this works perfectly and now when i go back and include this part of my actual select statement which has to pull 20 columns, i am getting the above error which is
"Error converting data type DBTYPE_DBTIMESTAMP to datetime".