Running ODI 12.2.1 on a Windows Server 2012, local ODI repository is SQL Server 2014 SP2. Source database is SQL Server 2014, it is also a read only database in an always on-cluster. We have encountered the following error several times and have been unable to pinpoint its source. The error occurs during our source to stage load at 2:30 in the morning. Once we catch the error we are able to restart the entire source to stage load and it completes without issue. The error message is:
oracle.odi.runtime.agent.exception.ExecutionEngineException: java.sql.SQLException: [FMWGEN][SQLServer JDBC Driver][SQLServer]Could not complete cursor operation because the table schema changed after the cursor was declared.
at oracle.odi.runtime.agent.execution.sql.concurrent.FastJDBCRecordSet.call(FastJDBCRecordSet.java:276)
at oracle.odi.runtime.agent.execution.sql.concurrent.FastJDBCRecordSet.call(FastJDBCRecordSet.java:37)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at java.lang.Thread.run(Thread.java:748)
Caused by: java.sql.SQLException: [FMWGEN][SQLServer JDBC Driver][SQLServer]Could not complete cursor operation because the table schema changed after the cursor was declared.
at weblogic.jdbc.sqlserverbase.ddcw.b(Unknown Source)
at weblogic.jdbc.sqlserverbase.ddcw.a(Unknown Source)
at weblogic.jdbc.sqlserverbase.ddcv.b(Unknown Source)
at weblogic.jdbc.sqlserverbase.ddcv.a(Unknown Source)
at weblogic.jdbc.sqlserver.tds.ddr.v(Unknown Source)
at weblogic.jdbc.sqlserver.tds.ddr.a(Unknown Source)
at weblogic.jdbc.sqlserver.tds.ddq.a(Unknown Source)
at weblogic.jdbc.sqlserver.tds.ddm.a(Unknown Source)
at weblogic.jdbc.sqlserver.tds.ddr.c(Unknown Source)
at weblogic.jdbc.sqlserver.tds.ddm.a(Unknown Source)
at weblogic.jdbc.sqlserver.tds.ddm.e(Unknown Source)
at weblogic.jdbc.sqlserver.tds.ddm.g(Unknown Source)
at weblogic.jdbc.sqlserver.dda2.j(Unknown Source)
at weblogic.jdbc.sqlserverbase.ddea.next(Unknown Source)
at oracle.odi.runtime.agent.execution.sql.concurrent.FastJDBCRecordSet.call(FastJDBCRecordSet.java:256)
... 5 more
Target code:
insert into ODISTAGE.dbo.C$_0GRANGER_OBJECTS
(
C1_OBJECTID,
C2_NAME,
C3_OBJCATEGORYID,
C4_ACCINTERFACEID,
C5_VISIBLE,
C6_LOCATIONCODE,
C7_ENTITY,
C8_CUSTVENDTERMGUID,
C9_CUSTVENDTERMID
)
values
(
:C1_OBJECTID,
:C2_NAME,
:C3_OBJCATEGORYID,
:C4_ACCINTERFACEID,
:C5_VISIBLE,
:C6_LOCATIONCODE,
:C7_ENTITY,
:C8_CUSTVENDTERMGUID,
:C9_CUSTVENDTERMID
)
Source Code:
select
CUSTOMER.CustomerGuid as C1_OBJECTID,
CUSTOMER.DisplayName as C2_NAME,
1 as C3_OBJCATEGORYID,
CUSTOMER.AccountCode as C4_ACCINTERFACEID,
CUSTOMER.Active as C5_VISIBLE,
LOCATIONS.HumanId as C6_LOCATIONCODE,
CUSTOMER.Entity as C7_ENTITY,
CUSTOMER.CustomerGuid as C8_CUSTVENDTERMGUID,
CUSTOMER.CustomerId as C9_CUSTVENDTERMID
from granger.CUSTOMER.Customer as CUSTOMER LEFT JOIN granger.dbo.Locations as LOCATIONS ON CUSTOMER.DefaultLocationGuid=LOCATIONS.LocationGuid
where (1=1)