November 8, 2006 at 12:41 am
Hi,
I'm new to SQL and facing problem and need expert helps with distributed transaction. I had created the link server which is working fine when i test from SQL query analyzer and able to insert record in oracle server.
But when i use the distrbuted transaction in SP code I get error saying
"The operation could not be performed because OLE DB provider "MSDAORA" for linked server "ORCLDB" was unable to begin a distributed transaction."
I had started DTS service on both oracle and sQL server. I had also changed the registry entry of hkey-local-machine--software-microsoft-msdtc-mtxoci
oracleocilib - oci.dll
oraclesqlib - orasql8.dll
oraclexalib - oraclient8.dll
The code for SP is
set
ANSI_NULLS ON
set
QUOTED_IDENTIFIER ON
go
/****** Object: Stored Procedure dpLeaveDetailsInsert ******/
ALTER
PROCEDURE [dbo].[leave]
@emp_id
varchar(50),@leave_id varchar(50)
AS
BEGIN
TRY
SET XACT_ABORT ON
BEGIN TRANSACTION DPLDINSERT
INSERT INTO [leavetest] (
[emp_id]
, [leave_id])
values
(@emp_id,@leave_id)
INSERT
INTO OPENQUERY(ORCLDB, 'SELECT * FROM PAY.LEAVE_TEST') VALUES (@emp_id,@leave_id)
COMMIT TRANSACTION DPLDINSERT
SET XACT_ABORT OFF
END
TRY
BEGIN
CATCH
ROLLBACK TRANSACTION DPLDINSERT
EXEC usp_ErrorDetails
END
CATCH
November 8, 2006 at 8:59 pm
we are using windows 2000 with SP4 and SQL 2005 standard edition. If someone can tell us where i'm going wrong it'll be great help..
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply