SQL 2000 Oracle Linked Server - Divide by Zero

• ron.bracale

  SSC Enthusiast

  Points: 182

  More actions

  November 11, 2008 at 8:56 am

  #215156

  Situation: SQL Server 2000 with a Linked Server to an Oracle table: in production, but not in Development or Quality about four months ago started returning a divide by zero error in the DTS package when the table was empty, but not when there were rows to join on. Then last month it started returning the error even when there was data to pull.

  -------------------------------------------------

  DTS ERROR with 'MSDAORA':

  Executed as user: AD\SVC.SQL. ...un OnStart: DTSStep_ProcessAllBatches DTSRun OnError: DTSStep_ProcessAllBatches, Error = -2147217900 (80040E14) Error string: OLE DB provider 'MSDAORA' reported an error. Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0 Error Detail Records: Error: -2147217900 (80040E14); Provider Error: 7399 (1CE7) Error string: OLE DB provider 'MSDAORA' reported an error. Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0 Error: -2147217900 (80040E14); Provider Error: 7300 (1C84) Error string: OLE DB error trace [OLE/DB Provider 'MSDAORA' IRowset::GetNextRows returned 0x80004005: ]. Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0 Error: -2147217900 (80040E14); Provider Error: 7312 (1C90) Error string: [OLE/DB provider returned mes... Process Exit Code 1. The step failed.

  Query analyzer returned the same error if I selected from the Linked Server Oracle table with a WHERE clause, but not without a WHERE clause:

  Server: Msg 7399, Level 16, State 1, Line 18

  OLE DB provider 'MSDAORA' reported an error. [OLE/DB provider returned message: ORA-01476: divisor is equal to zero] OLE DB error trace [OLE/DB Provider 'MSDAORA' IRowset::GetNextRows returned 0x80004005: ].

  I tried creating a SQL Server view on the Oracle table. Again it worked in Development and Quality, but not production.

  Here is the view:

  -- --------------------------------------------------------

  CREATE VIEW PAT_ISProj

  AS

  SELECT I.EMP_NO AS EMP_NO,

  I.HOURS AS HOURS,

  I.HOUR_TYPE AS HOUR_TYPE,

  I.ENTRY_DATE AS ENTRY_DATE,

  I.COMPANY_NO AS COMPANY_NO,

  I.PAY_FREQUENCY AS PAY_FREQUENCY,

  I.EXEMPT_FLAG AS EXEMPT_FLAG,

  I.SOURCE_NAME AS SOURCE_NAME

  FROM ISPROJ..ISPROJ.ISPROJ_TIME_EXPORT_T AS I

  GO

  -- --------------------------------------------------------

  Here is the error:

  Server: Msg 7399, Level 16, State 1, Procedure PAT_ISProj, Line 28

  OLE DB provider 'MSDAORA' reported an error.

  [OLE/DB provider returned message: ORA-01476: divisor is equal to zero

  ]

  Obviously selecting with a where clause or creating a view where no division exists indicates that the error is something internal to processing, rather than a data error.

  -------------------------------------------------

  The DBA could find no server differences and DTC ping did not identify any issues.

  We then tried changing the provider, which changed the error we were getting, but still did not allow executing of the stored procedure, nor adding a basic view, but the where clause issue went away.

  DTS Error with provider 'OraOLEDB.Oracle':

  Executed as user: AD\SVC.SQL. ... Executing... DTSRun OnStart: DTSStep_ProcessAllBatches DTSRun OnError: DTSStep_ProcessAllBatches, Error = -2147217900 (80040E14) Error string: The operation could not be performed because the OLE DB provider 'OraOLEDB.Oracle' was unable to begin a distributed transaction. Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0 Error Detail Records: Error: -2147217900 (80040E14); Provider Error: 7391 (1CDF) Error string: The operation could not be performed because the OLE DB provider 'OraOLEDB.Oracle' was unable to begin a distributed transaction. Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0 Error: -2147217900 (80040E14); Provider Error: 7300 (1C84) Error string: OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle' ITransactionJoin::JoinTransaction returned 0x8004d00a]. Error source: Microsoft OLE DB Pr. The step failed.

  -------------------------------------------------

  Query Analyzer Error (same thing happens with Create View):

  DECLARE @intRtn INT

  EXECUTE @intRtn = dbo.sp_ISPAT_Source

  SELECT @intRtn AS SourceRtn

  (0 row(s) affected)

  Server: Msg 7391, Level 16, State 1, Procedure sp_ISPAT_LoadRemoteBatches, Line 42

  The operation could not be performed because the OLE DB provider 'OraOLEDB.Oracle' was unable to begin a distributed transaction.

  [OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]

  OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle' ITransactionJoin::JoinTransaction returned 0x8004d00a].