We've created a stored procedure to pull in full copies of some Oracle tables with a Linked Server.
Over the course of 11 hours, the procedure correctly populated the first 11 tables but failed with this error on table 12:
Msg 7399, Level 16, State 1, Procedure Refresh_From_Oracle, Line 45
The OLE DB provider "OraOLEDB.Oracle" for linked server "ORACLE_LIVE" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Procedure Refresh_From_Oracle, Line 45
Cannot fetch a row from OLE DB provider "OraOLEDB.Oracle" for linked server "ORACLE_LIVE".
Is there anything there that gives a clue as to why the procedure failed when it got to table 12? Or is there a limit to how long a procedure can run, or a query can run against a linked server?
All tables except 12 and 13 have <2,000,000 rows, and 12 and 13 have about 70,000,000 each.
Here is the stored procedure:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Jack Douglas
-- Create date: 2016-10-07
-- Description: Truncates and re-populates UAT copies of Oracle data
-- =============================================
ALTER PROCEDURE [dbo].[Refresh_From_Oracle]
AS
BEGIN
SET NOCOUNT ON;
TRUNCATE TABLE dbo.TABLE1;
INSERT INTO dbo.TABLE1 SELECT * FROM ORACLE_LIVE..ORALIVE.TABLE1;
…
TRUNCATE TABLE dbo.TABLE12;
INSERT INTO dbo.TABLE12 SELECT * FROM ORACLE_LIVE..ORALIVE.TABLE12;
TRUNCATE TABLE dbo.TABLE13;
INSERT INTO dbo.TABLE13 SELECT * FROM ORACLE_LIVE..ORALIVE.TABLE13;
END
Best Answer
The problem turned out to be on the Oracle side:
It's the classic 'Snapshot too old' issue with a long-running transaction and MVCC.