Problem with executing Distributed Transactions on Linked Servers

I have a Linked Server i.e. my SQL Server 2000 is Linked to the Oracle
Database.
The Link is perfect and dont have any problems in accessing the data
from ORACLE. The only problem i am facing is when i am trying
to initiate a Distributed transaction. A simple Insert Query is
working fine but when i try to perform the same through a Stored
Procedure having the Begin Transaction i am getting the error
mentioned below.
Server: Msg 7391, Level 16, State 1, Procedure InsertGFSTemp, Line 12
The operation could not be performed because the OLE DB provider
'MSDAORA' was unable to begin a distributed transaction.
OLE DB error trace [OLE/DB Provider 'MSDAORA'
ITransactionJoin::JoinTransaction returned 0x8004d01b].
I have tried all the help available and have all the supported
softwares installed as well on the SQL Server machine. Still facing
this problem.
If anybody could help me with the same.
Hi!
The error 7391 troubleshooting guide is at
http://support.microsoft.com/default...;en-us;306212. Check it, it
might help you.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"Raja" <Raja@.discussions.microsoft.com> wrote in message
news:F74A2D92-C765-4A09-A5C7-903CB7C92E9D@.microsoft.com...
> I have a Linked Server i.e. my SQL Server 2000 is Linked to the Oracle
> Database.
> The Link is perfect and dont have any problems in accessing the data
> from ORACLE. The only problem i am facing is when i am trying
> to initiate a Distributed transaction. A simple Insert Query is
> working fine but when i try to perform the same through a Stored
> Procedure having the Begin Transaction i am getting the error
> mentioned below.
> Server: Msg 7391, Level 16, State 1, Procedure InsertGFSTemp, Line 12
> The operation could not be performed because the OLE DB provider
> 'MSDAORA' was unable to begin a distributed transaction.
> OLE DB error trace [OLE/DB Provider 'MSDAORA'
> ITransactionJoin::JoinTransaction returned 0x8004d01b].
> I have tried all the help available and have all the supported
> softwares installed as well on the SQL Server machine. Still facing
> this problem.
> If anybody could help me with the same.
>
|||"=?Utf-8?B?UmFqYQ==?=" <Raja@.discussions.microsoft.com> wrote in
news:F74A2D92-C765-4A09-A5C7-903CB7C92E9D@.microsoft.com:

> oblem i am facing is when i am trying
> to initiate a Distributed transaction. A simple Insert Query is
> working fine but when i try to perform the same through a Stored
> Procedure having the Begin Transaction i am getting the error
> mentioned below.
>
You need to SET IMPLICIT_TRANSACTIONS OFF
See BOL: SET IMPLICIT_TRANSACTIONS

Problem with executing Distributed Transactions on Linked Servers

I have a Linked Server i.e. my SQL Server 2000 is Linked to the Oracle
Database.
The Link is perfect and dont have any problems in accessing the data
from ORACLE. The only problem i am facing is when i am trying
to initiate a Distributed transaction. A simple Insert Query is
working fine but when i try to perform the same through a Stored
Procedure having the Begin Transaction i am getting the error
mentioned below.
Server: Msg 7391, Level 16, State 1, Procedure InsertGFSTemp, Line 12
The operation could not be performed because the OLE DB provider
'MSDAORA' was unable to begin a distributed transaction.
OLE DB error trace [OLE/DB Provider 'MSDAORA'
ITransactionJoin::JoinTransaction returned 0x8004d01b].
I have tried all the help available and have all the supported
softwares installed as well on the SQL Server machine. Still facing
this problem.
If anybody could help me with the same.Hi!
The error 7391 troubleshooting guide is at
http://support.microsoft.com/default.aspx?scid=kb;en-us;306212. Check it, it
might help you.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"Raja" <Raja@.discussions.microsoft.com> wrote in message
news:F74A2D92-C765-4A09-A5C7-903CB7C92E9D@.microsoft.com...
> I have a Linked Server i.e. my SQL Server 2000 is Linked to the Oracle
> Database.
> The Link is perfect and dont have any problems in accessing the data
> from ORACLE. The only problem i am facing is when i am trying
> to initiate a Distributed transaction. A simple Insert Query is
> working fine but when i try to perform the same through a Stored
> Procedure having the Begin Transaction i am getting the error
> mentioned below.
> Server: Msg 7391, Level 16, State 1, Procedure InsertGFSTemp, Line 12
> The operation could not be performed because the OLE DB provider
> 'MSDAORA' was unable to begin a distributed transaction.
> OLE DB error trace [OLE/DB Provider 'MSDAORA'
> ITransactionJoin::JoinTransaction returned 0x8004d01b].
> I have tried all the help available and have all the supported
> softwares installed as well on the SQL Server machine. Still facing
> this problem.
> If anybody could help me with the same.
>|||"=?Utf-8?B?UmFqYQ==?=" <Raja@.discussions.microsoft.com> wrote in
news:F74A2D92-C765-4A09-A5C7-903CB7C92E9D@.microsoft.com:
> oblem i am facing is when i am trying
> to initiate a Distributed transaction. A simple Insert Query is
> working fine but when i try to perform the same through a Stored
> Procedure having the Begin Transaction i am getting the error
> mentioned below.
>
You need to SET IMPLICIT_TRANSACTIONS OFF
See BOL: SET IMPLICIT_TRANSACTIONS

Problem with executing Distributed Transactions on Linked Server

I have a Linked Server i.e. my SQL Server 2000 is Linked to the Oracle
Database.
The Link is perfect and dont have any problems in accessing the data
from ORACLE. The only problem i am facing is when i am trying
to initiate a Distributed transaction. A simple Insert Query is
working fine but when i try to perform the same through a Stored
Procedure having the Begin Transaction i am getting the error
mentioned below.
Server: Msg 7391, Level 16, State 1, Procedure InsertGFSTemp, Line 12
The operation could not be performed because the OLE DB provider
'MSDAORA' was unable to begin a distributed transaction.
OLE DB error trace [OLE/DB Provider 'MSDAORA'
ITransactionJoin::JoinTransaction returned 0x8004d01b].
I have tried all the help available and have all the supported
softwares installed as well on the SQL Server machine. Still facing
this problem.
If anybody could help me with the same.
Raja,
That error code you are getting - " 0x8004d01b" translates into - "The
Transaction Manager is not available". I think that seems to be the
problem. What are the OS of the boxes involved?
Thanks,
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

Problem with executing Distributed Transactions on Linked Server

I have a Linked Server i.e. my SQL Server 2000 is Linked to the Oracle
Database.
The Link is perfect and dont have any problems in accessing the data
from ORACLE. The only problem i am facing is when i am trying
to initiate a Distributed transaction. A simple Insert Query is
working fine but when i try to perform the same through a Stored
Procedure having the Begin Transaction i am getting the error
mentioned below.
Server: Msg 7391, Level 16, State 1, Procedure InsertGFSTemp, Line 12
The operation could not be performed because the OLE DB provider
'MSDAORA' was unable to begin a distributed transaction.
OLE DB error trace [OLE/DB Provider 'MSDAORA'
ITransactionJoin::JoinTransaction returned 0x8004d01b].
I have tried all the help available and have all the supported
softwares installed as well on the SQL Server machine. Still facing
this problem.
If anybody could help me with the same.Raja,
That error code you are getting - " 0x8004d01b" translates into - "The
Transaction Manager is not available". I think that seems to be the
problem. What are the OS of the boxes involved?
Thanks,
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

problem with distributed transaction via linked server

hi,
i am trying to connect to oracle 9i using sqlserver 2000 linked server.
here the linked server name is intersql. when i put this statement in begin
and end transaction block and try to execute it is giving the following
error
Server: Msg 7391, Level 16, State 1, Procedure
ihmtproc_emp_assmbly_interface, Line 93
The operation could not be performed because the OLE DB provider 'MSDAORA'
was unable to begin a distributed transaction.
OLE DB error trace [OLE/DB Provider 'MSDAORA'
ITransactionJoin::JoinTransaction returned 0x8004d01b].
INSERT INTO eemp_assembly_interface
(
[drawing_file_nm],
[part_number_txt],
[drawing_status_txt],
[drawing_revision_txt],
[model_file_nm],
[model_title_txt],
[parent_part_number_txt],
[release_status_txt],
[emp_desc],
[created_by_user_id],
[created_on_dt]
)
SELECT drawing_filename,
part_number,
drawing_status,
drawing_revision,
model_filename,
model_title ,
parent_part_number,
release_status,
description,
999999999 as created_by_user_id,
GETDATE() as created_on_dt
FROM OPENQUERY(productcenter,
'SELECT
drawing_filename,
part_number,
drawing_status,
drawing_revision,
model_filename,
model_title ,
parent_part_number,
release_status,
description
FROM EMP1 WHERE FLAG=''N'''
)
please help me
- Thanks
ravi sankar gvsIs it because you are specifying [productcenter] instead of [intersql] as
your linked server name?
Raj Moloye
"Nishanth" <cvnishanth@.hotmail.com> wrote in message
news:uha5aF2aFHA.1456@.TK2MSFTNGP15.phx.gbl...
> hi,
> i am trying to connect to oracle 9i using sqlserver 2000 linked server.
> here the linked server name is intersql. when i put this statement in
> begin
> and end transaction block and try to execute it is giving the following
> error
> Server: Msg 7391, Level 16, State 1, Procedure
> ihmtproc_emp_assmbly_interface, Line 93
> The operation could not be performed because the OLE DB provider 'MSDAORA'
> was unable to begin a distributed transaction.
> OLE DB error trace [OLE/DB Provider 'MSDAORA'
> ITransactionJoin::JoinTransaction returned 0x8004d01b].
> INSERT INTO eemp_assembly_interface
> (
> [drawing_file_nm],
> [part_number_txt],
> [drawing_status_txt],
> [drawing_revision_txt],
> [model_file_nm],
> [model_title_txt],
> [parent_part_number_txt],
> [release_status_txt],
> [emp_desc],
> [created_by_user_id],
> [created_on_dt]
> )
> SELECT drawing_filename,
> part_number,
> drawing_status,
> drawing_revision,
> model_filename,
> model_title ,
> parent_part_number,
> release_status,
> description,
> 999999999 as created_by_user_id,
> GETDATE() as created_on_dt
> FROM OPENQUERY(productcenter,
> 'SELECT
> drawing_filename,
> part_number,
> drawing_status,
> drawing_revision,
> model_filename,
> model_title ,
> parent_part_number,
> release_status,
> description
> FROM EMP1 WHERE FLAG=''N'''
> )
> please help me
> - Thanks
> ravi sankar gvs
>

Problem with distributed transacion.

Hi everybody, I'm having problems with distributed transactions. Every
time i execute the store procedure, get this error:
System.Data.SqlClient.SqlException: The operation could not be performed
because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed
transaction.
This process use to work, but on the weekend I change one of the server
are involved. The server that runs this query now has win2003 sp1,
sql2000 sp3 installed,
when it works has this configuration: win2000 sp4, sql2000 sp3.
The remote server has win2000 sp4, sql2000 sp3.
I will appreciate a lot your help.
*** Sent via Developersdex http://www.codecomments.com ***
Network access to the distributed transaction coordinator
(MS DTC) is disabled by default on Windows 2003. Check the
following article for steps to enable it on the Windows 2003
box:
How to enable network DTC access in Windows Server 2003
http://support.microsoft.com/?id=817064
-Sue
On Tue, 17 Jan 2006 15:02:00 -0800, MariaGuzman
<marisa@.devdex.com> wrote:

>Hi everybody, I'm having problems with distributed transactions. Every
>time i execute the store procedure, get this error:
>System.Data.SqlClient.SqlException: The operation could not be performed
>because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed
>transaction.
>This process use to work, but on the weekend I change one of the server
>are involved. The server that runs this query now has win2003 sp1,
>sql2000 sp3 installed,
>when it works has this configuration: win2000 sp4, sql2000 sp3.
>The remote server has win2000 sp4, sql2000 sp3.
>I will appreciate a lot your help.
>
>
>
>*** Sent via Developersdex http://www.codecomments.com ***
|||Thanks a lot for your help.
I apply the changes that you recommended to me, but this works only if i
use the begin transaction clause. But If I use the begin distributed
transaction, it doesnt work.
Do you know any other issue that i have to consider?
Thanks a lot!!
*** Sent via Developersdex http://www.codecomments.com ***
|||Thanks a lot for your help. At the end, I resolve this problem following
this link.
http://support.microsoft.com/kb/831425/en-us
Regards,
*** Sent via Developersdex http://www.codecomments.com ***

Problem with distributed transacion.

Hi everybody, I'm having problems with distributed transactions. Every
time i execute the store procedure, get this error:
System.Data.SqlClient.SqlException: The operation could not be performed
because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed
transaction.
This process use to work, but on the weekend I change one of the server
are involved. The server that runs this query now has win2003 sp1,
sql2000 sp3 installed,
when it works has this configuration: win2000 sp4, sql2000 sp3.
The remote server has win2000 sp4, sql2000 sp3.
I will appreciate a lot your help.
*** Sent via Developersdex http://www.developersdex.com ***Network access to the distributed transaction coordinator
(MS DTC) is disabled by default on Windows 2003. Check the
following article for steps to enable it on the Windows 2003
box:
How to enable network DTC access in Windows Server 2003
http://support.microsoft.com/?id=817064
-Sue
On Tue, 17 Jan 2006 15:02:00 -0800, MariaGuzman
<marisa@.devdex.com> wrote:
>Hi everybody, I'm having problems with distributed transactions. Every
>time i execute the store procedure, get this error:
>System.Data.SqlClient.SqlException: The operation could not be performed
>because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed
>transaction.
>This process use to work, but on the weekend I change one of the server
>are involved. The server that runs this query now has win2003 sp1,
>sql2000 sp3 installed,
>when it works has this configuration: win2000 sp4, sql2000 sp3.
>The remote server has win2000 sp4, sql2000 sp3.
>I will appreciate a lot your help.
>
>
>
>*** Sent via Developersdex http://www.developersdex.com ***

Problem with distributed transacion.

Hi everybody, I'm having problems with distributed transactions. Every
time i execute the store procedure, get this error:
System.Data.SqlClient.SqlException: The operation could not be performed
because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed
transaction.
This process use to work, but on the weekend I change one of the server
are involved. The server that runs this query now has win2003 sp1,
sql2000 sp3 installed,
when it works has this configuration: win2000 sp4, sql2000 sp3.
The remote server has win2000 sp4, sql2000 sp3.
I will appreciate a lot your help.
*** Sent via Developersdex http://www.codecomments.com ***Network access to the distributed transaction coordinator
(MS DTC) is disabled by default on Windows 2003. Check the
following article for steps to enable it on the Windows 2003
box:
How to enable network DTC access in Windows Server 2003
http://support.microsoft.com/?id=817064
-Sue
On Tue, 17 Jan 2006 15:02:00 -0800, MariaGuzman
<marisa@.devdex.com> wrote:

>Hi everybody, I'm having problems with distributed transactions. Every
>time i execute the store procedure, get this error:
>System.Data.SqlClient.SqlException: The operation could not be performed
>because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed
>transaction.
>This process use to work, but on the weekend I change one of the server
>are involved. The server that runs this query now has win2003 sp1,
>sql2000 sp3 installed,
>when it works has this configuration: win2000 sp4, sql2000 sp3.
>The remote server has win2000 sp4, sql2000 sp3.
>I will appreciate a lot your help.
>
>
>
>*** Sent via Developersdex http://www.codecomments.com ***|||Thanks a lot for your help.
I apply the changes that you recommended to me, but this works only if i
use the begin transaction clause. But If I use the begin distributed
transaction, it doesnt work.
Do you know any other issue that i have to consider'
Thanks a lot!!
*** Sent via Developersdex http://www.codecomments.com ***|||Thanks a lot for your help. At the end, I resolve this problem following
this link.
http://support.microsoft.com/kb/831425/en-us
Regards,
*** Sent via Developersdex http://www.codecomments.com ***

problem with distributed trans 2000 to 2005

I have a test environment setup consisiting of one SQL Server 2000 32 bit instance on one box and one SQL Server 2005 64 bit edtion on another box. Nearly everything works fine but have found this one snag...

Linked Server name: SQL2005

From the SQL Server 2000 instance in Query Analyzer...

-- this works fine
Delete from [SQL2005].myDB.dbo.myTbl

-- this just hangs
Begin Tran
Delete from [SQL2005].myDB.dbo.myTbl

It doesn't make any difference what provider I use on the Linked Server.

I have tried it on a SQL Server 2000 to SQL Server 2000 configuration and, interestingly, it would only work if I used set XACT_ABORT ON. However, in the SQL Server 2000 to 2005 configuration, this doesn't help.

We have a number of DTS packages that do deletes across the linked server that are failing due to this issue so we're being stopped from continuing with the migration to SQL Server 2005.

Any suggestions?

CilvePerhaps your DTC service is not running.

I have these 2 related links:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=230390&SiteID=1
http://support.microsoft.com/kb/899191

By the way, if you intend to migrate for an old SQL2000 DB to a new SQL2005 DB, why not to backup the old one and restore it on your SQL2005 server?|||The DTC service is runnnig on both servers.

Actually, the problem isn't limited to Deletes within a distributed transaction... The problem is the distributed transaction itself. I repeated the test doing an insert across the servers. The insert works on its own but within a distributed transaction, it hangs.

I have restored the database onto the SQL Server 2005 box to migrate them. I was refering to application DTS packages not working on the 2000 server because of the distributed transaction problem.

Thanks,
Clive