Distributed transaction error over linked server query

Question

Post reply

Distributed transaction error over linked server query

Gopal-49563

Ten Centuries

Points: 1209
More actions
September 26, 2007 at 9:35 am

#177608

I have couple of servers with SQL Server 2000 on windows 2003 SP1.
Simulate scenario
-- Run on SQLSERVER2
BEGIN DISTRIBUTED TRANSACTION
select * from SQLSERVER1.pubs.dbo.sysobjects
COMMIT
When I am using above linked server query to fetch data from one server and insert data to local server following SQL error is returned.
[OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a].
Msg 7391, Level 16, State 1, Line 2
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.
Microsoft refrence article
http://support.microsoft.com/kb/839279/en-us
Please note
1) MSDTC is configured to allow Network DTC Access
2) There is no firewall between the servers

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

mengus Ten Centuries Points: 1187 More actions · Answer 1

Gopal ,

Have you rebooted the servers? MSDTC should be enabled on both servers.

You may want to give this a try. If this does not work, let me know, there is a work-around, without using Distributed Transactions, but this will depend on the requirements.

Regards,

Wameng Vang

MCTS

Gopal-49563 Ten Centuries Points: 1209 More actions · Answer 2

MSDTC was configured long time back and servers are rebooted couple of time after the configuration.

Please post the work around.

mengus Ten Centuries Points: 1187 More actions · Answer 3

Gopal,

Note: After re-evaluating your posting, I am assuming that your original code was something like this.

Possible Problem:[/u]

INSERT DestDB.dbo.Table

select from SourceServer.SourceDB.dbo.Table

--NOTE:** Something like this will require Distributed Transactions to be fully functional.

Sample Work-around Code using Temp table as a temporary placeholder[/u]

if (exists (select 1 from tempdb.dbo.sysobjects where name like '%Mengus%'))

begin

drop table #Mengus

end

-- Grab data from Linked Server/Database (Source)

select

*

INTO #Mengus

from LinkedServerSource.master.dbo.sysobjects

-- Transactions when applicable....

-- Then INSERT DATA FROM Temp Table into Local Database

--......

-- Handle Transaction (Rollback/Commit) when applicable

Regards,

Wameng Vang

MCTS

Gopal-49563 Ten Centuries Points: 1209 More actions · Answer 4

Actually the original code looks like as follows

INSERT INTO DestTable

EXEC SourceServer.database.dbo.usp_returnOneRecordSet

I don't have choice of editing code of source server.

mengus Ten Centuries Points: 1187 More actions · Answer 5

Gopal,

*** Check this old forum post:http://www.sqlservercentral.com/Forums/Topic200830-8-1.aspx

Regarding your comments:

If that's the case, temporary tables will not work. I have tried it for a couple cases. However if you rewrite the logic and convert the stored procedure to an actual SELECT statement, you can use temporary tables and "SELECT INTO."

Example:

Instead of

INSERT INTO #temp

EXEC SourceServer.database.dbo.uspSproc

Must be converted to:

select

--Appropriate Columns (as returned by stored procedure)

INTO #temp

from SourceServer.database.dbo.Table

Regards,

Wameng Vang

MCTS

Gopal-49563 Ten Centuries Points: 1209 More actions · Answer 6

Thanks. I am aware of this kind of approach but I don't have option to modify code on source server.

BTW Does anybody know of real cause of this problem.