Adding Stored Procedure from Oracle Server

Question

I am creating a stored procedure that will run daily to copy data from the larger archive to another database that will be used to populate some PowerBI dashboards. The table I am attempting to pull from has a hidden column tied to an Oracle server. I was able to get passed this previously with OpenQuery but when I try OpenQuery in a procedure it fails. I have tried all of the individual components of the procedure in isolation and it seems to work fine, however when I execute the create procedure I get this error

Msg 7354, Level 16, State 1, Procedure Sync_ROYALTY_CALCULATIONS, Line 17 [Batch Start Line 9]
The OLE DB provider "OraOLEDB.Oracle" for linked server "P2DATABASE" supplied invalid metadata for column "CREATE_DATE_TIME". The data type is not supported.

Which I thought I solved with the OpenQuery implementation.

Here is the create procedure:

USE [P2DATABASEWAREHOUSE]
GO

/****** Object:  StoredProcedure [dbo].[Sync_ROYALTY_CALCULATIONS]    Script Date: 5/21/2021 10:05:25 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[Sync_ROYALTY_CALCULATIONS] AS 

INSERT INTO [ROYALTY_CALCULATIONS]
([ROYALTY_OBLIGATION_SYS_ID])
--Insert new rows into SQL table
SELECT [ROYALTY_OBLIGATION_SYS_ID]
FROM OPENQUERY([P2DATABASE], 'SELECT * FROM METRIX.ROYALTY_CALCULATIONS')
WHERE [ROYALTY_OBLIGATION_SYS_ID] NOT IN (SELECT [ROYALTY_OBLIGATION_SYS_ID] FROM [ROYALTY_CALCULATIONS])


--Delete excess rows
DELETE FROM [ROYALTY_CALCULATIONS]
WHERE [ROYALTY_OBLIGATION_SYS_ID] NOT IN (SELECT [ROYALTY_OBLIGATION_SYS_ID] FROM OPENQUERY([P2DATABASE], 'SELECT ROYALTY_OBLIGATION_SYS_ID FROM METRIX.ROYALTY_CALCULATIONS'))

--Update matching rows
UPDATE t2 
SET 
t2.[ROYALTY_OBLIGATION_SYS_ID] = t1.[ROYALTY_OBLIGATION_SYS_ID],
t2.[CREATE_USER] = t1.[CREATE_USER]
      ,t2.[CREATE_PROGRAM] = t1.[CREATE_PROGRAM]
      ,t2.[CREATE_DATE_TIME] = t1.[CREATE_DATE_TIME]
FROM [ROYALTY_CALCULATIONS] AS t2
INNER JOIN [P2DATABASE]..[METRIX].[ROYALTY_CALCULATIONS] AS t1 
ON t2.[ROYALTY_OBLIGATION_SYS_ID]=t1.[ROYALTY_OBLIGATION_SYS_ID]
GO

Any insight as to why it's not working in a procedure would be appreciated.

Thanks,

Answer 1

Hi @John Oke ,

Welcome to Microsoft Q&A!

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

According to above error message, you could check the date type of column "CREATE_DATE_TIME" between Oracle and SQL Server.

You could try to convert your Oracle date to text and allow SQL Server to automatically convert the text to date.

If you are pulling from Oracle, look into DBMS_HS_PASSTHROUGH. It is many times faster than selecting through a database link.

Best regards,
Melissa

---

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.

Answer 2

What if you instead of SELECT * list the columns you need in the Query to Oracle?