Hi,
I am trying to create a stored procedure in sql server 2005 which also has
link server to oracle 9i. When i enclose the insert statements in distributed
transaction it always throw Err 7391
"The operation could not be performed because the OLE DB provider was unable
to begin a distributed transaction.".

The MSDTC is running on both the servers. The stored proc runs absolutely
fine when distributed transaction is commented out.

The code for the stored proc is given below :
ALTER PROCEDURE [dbo].[LEAVEDETAILS]
@LEAVE_CODE varchar(20),
@LEAVE_TYPE_ID int,
@EMP_ID int,
@APPROVER_ID int,
@FROM_DATE datetime,
@TO_DATE datetime,
@DATE_APPLIED datetime,
@LEAVE_DAYS int,
@LEAVE_ADDRESS varchar(255),
@LEAVE_PIN_CODE varchar(10),
@LEAVE_PURPOSE varchar(255),
@LEAVE_REMARKS varchar(255),
@LEAVE_PHONE varchar(20),
@APPROVAL_DATE datetime,
@CREATION_BY varchar(20),
@CREATION_ON datetime,
@EMP_CODE varchar(10)
AS
SET NOCOUNT ON

BEGIN TRANSACTION DPLDINSERT

DECLARE @ERROR int

SELECT @ERROR = 0

INSERT INTO [LEAVE_DETAILS] (
[LEAVE_CODE],
[LEAVE_TYPE_ID],
[EMP_ID],
[APPROVER_ID],
[FROM_DATE],
[TO_DATE],
[DATE_APPLIED],
[LEAVE_DAYS],
[LEAVE_ADDRESS],
[LEAVE_PIN_CODE],
[LEAVE_PURPOSE],
[LEAVE_REMARKS],
[LEAVE_PHONE],
[APPROVAL_DATE],
[CREATION_BY],
[CREATION_ON]
) VALUES (
@LEAVE_CODE,
@LEAVE_TYPE_ID,
@EMP_ID,
@APPROVER_ID,
@FROM_DATE,
@TO_DATE,
@DATE_APPLIED,
@LEAVE_DAYS,
@LEAVE_ADDRESS,
@LEAVE_PIN_CODE,
@LEAVE_PURPOSE,
@LEAVE_REMARKS,
@LEAVE_PHONE,
@APPROVAL_DATE,
@CREATION_BY,
@CREATION_ON
)

INSERT INTO [ORACLDB..PAY.TEMP_LTS_TRANSACTION] (
[EMP_EMP_ID],
[LEAVE_APP_ID],
[LEAVE_ID],
[LEAVE_AVAILED],
[FROM_DATE],
[DATE_TO],
[LEAVE_ADDRESS],
[PIN_NO],
[CONATACT_PHONE_NO],
[PURPOSE_OF_LEAVE],
[DATE_APPLIED],
[APVD_BY],
[REMARKS],
[LEAVE_STATUS],
[APROVAL_DATE],
) VALUES (
@EMP_CODE,
@LEAVE_TYPE_ID,
@EMP_ID,
@APPROVER_ID,
@FROM_DATE,
@TO_DATE,
@LEAVE_ADDRESS,
@LEAVE_PIN_CODE,
@LEAVE_ADDRESS,
@LEAVE_PIN_CODE,
@LEAVE_PURPOSE,
@LEAVE_REMARKS,
@LEAVE_PHONE,
@APPROVAL_DATE,
@CREATION_BY,
@CREATION_ON
)
SET NOCOUNT OFF

SELECT @ERROR = @@ERROR

IF @ERROR = 0 BEGIN
COMMIT TRANSACTION DPLDINSERT
END ELSE BEGIN
ROLLBACK TRANSACTION DPLDINSERT
END
Any insite into the problem is appreciated.

Thanks in advance

Did you try:

BEGIN DISTRIBUTED TRANSACTION

--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous

You can't help someone get up a hill without getting a little closer to the top yourself.
- H. Norman Schwarzkopf


"Puneet Jain" <Puneet Jain (AT) discussions (DOT) microsoft.com> wrote

Hi Arnie,

yes i tried that too....in fact i tried maintaining transaction from c# code
also after removing references from stored proc....but it fails too

"Arnie Rowland" wrote: