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,