SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL 2000 Oracle Linked Server - Divide by Zero


SQL 2000 Oracle Linked Server - Divide by Zero

Author
Message
ron.bracale
ron.bracale
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 20
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].
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search