Copying SQL Table with Service Provider

John Oke 21 Reputation points
2021-05-17T16:51:54.613+00:00

I am attempting to move some SQL tables from an archive DB to a new DB in use by corporate dashboards.

The archive is a linked server with OraOLEDB.Oracle as a provider.

When I attempt to copy my table I get the following error: 

The OLE DB provider "OraOLEDB.Oracle" for linked server "P2DATABASE" supplied invalid metadata for column "CREATE_DATE_TIME". The data type is not supported

Looking at the schema there is no column CREATE_DATE_TIME but can still not copy the data over. I have been unable to use OPENQUERY to move the data.

Thanks,

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,691 questions
0 comments
Accepted answer
  1. Erland Sommarskog 100.9K Reputation points MVP
    2021-05-20T21:20:52.107+00:00

    There is not a ton of information to work from here, and I know very little about Oracle. If am a to make a guess there is a computed column or view on the Oracle side involved here.

    Rather than using four-part notation, it may be better to use OPENQUERY: 

    SELECT *. FROM OPENQUERY(MyOracle, 'SELECT col1, col2, col3 FROM tbl')

    The query inside OPENQUERY runs on Oracle, which means that you use Oracle syntax. This also means that you can convert troublesome data types to something that SQL Server can digest. For instance, you can convert dates to strings.

    Note that the timestamp datatypes in Oracle and SQL Server are entirely unrelated. The most closest data type to Oracle's timestamp in SQL server is datetime2, but vaguely recall that Oracle supports a broader range which causes problems.

    Anyway, start with a straight rewrite with OPENQUERY and work from there.

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. CarrinWu-MSFT 6,851 Reputation points
    2021-05-18T06:36:37.047+00:00

    Hi @John Oke ,

    Welcome to Microsoft Q&A!

    May I ask question, are you going to copy data from SQL Server to Oracle using SSIS or linked server? From you error message, I think you are using linked server, but you added an SSIS tag. If you are using linked server, please refer to this blog. The error may occurs when you execute a remote query that involves an Oracle TIMESTAMP column, please check your data type in Oracle first. If I misunderstanding your question, please let me know and provide more details about this error.

    Best regards,
    Carrin

    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.