question

JohnOke-3422 avatar image
0 Votes"
JohnOke-3422 asked MelissaMa-msft commented

Adding Stored Procedure from Oracle Server

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,



sql-server-generalsql-server-transact-sql
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @JohnOke-3422,

Could you please validate all the answers and provide any update?

Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread. 

Thank you for understanding!

Best regards,
Melissa

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered

Hi @JohnOke-3422,

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.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.