• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 310
  • Last Modified:

distributed transaction error

i am trying to execute a stored proc into a tempory table using a linked server.
code below:
create table #tmp_nowOrders( origText varchar(100), total int, paid int, canceled int, completed int, creditCard int, eCheck int, wu int, mg int,
                        lockbox int, agentStoreFront int, ace int, prePaidCard int, account int )
insert into #tmp_nowOrders( origText, total, paid, canceled, completed, creditCard, eCheck, wu, mg, lockbox, agentStoreFront, ace, prePaidCard, account )
EXEC ls_oms.orderManagement.dbo.report_InsideSalesPrepaidOrders '11/22/2004', '11/22/2004', 1

select * from #tmp_nowOrders

drop table #tmp_nowOrders

i keep getting this error:
Server: Msg 7391, Level 16, State 1, Line 7
The operation could not be performed because the OLE DB provider 'SQLOLEDB' 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 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a].

however, when i execute the contents of the stored proc, it works fine.

i have been poking around and found many references to sql2000 on win2k3 but that is not relivant.

both servers are running sql2000 the host is win2k adv serv the ls is win2k server.  msdtc is running on both, and neither has enforce dtc checked.

any ideas?
0
meyerc74
Asked:
meyerc74
  • 4
  • 3
1 Solution
 
muzzy2003Commented:
1. Do you have a firewall between the two servers?
2. Are both machines able to resolve the IP address of the opposite end of the transaction (try pinging the machine names each way and see if an IP address is resolved)?
0
 
meyerc74Author Commented:
there is no firewall between the two servers, and the two servers can communicate.  the query within the stored proc, and the stored proc work fine on the host machine.  i get the error when i try to insert the proc results into a temporary table
0
 
muzzy2003Commented:
Sorry to bang on but you are sure that both machines can resolve the other machine's host name to an IP address without needing to manually specify a DNS suffix? Also, are they set to communicate using TCP/IP or named pipes? Try TCP/IP if the latter.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
meyerc74Author Commented:
host names are not being used, but i chaecked anyhow and yes they resolved.  the linked server connects via the backend ip (10.X.X.X).

the servers can communicate fine, EXEC ls_oms.orderManagement.dbo.report_InsideSalesPrepaidOrders '11/22/2004', '11/22/2004', 1
by itself returns a recordset

i only get the error when i try to insert the results directly into a temp table
0
 
muzzy2003Commented:
OK. The reason I ask is that although you kick off a SQL connection from say HOST to LS, if DTS tries to set up a transaction, it is actually kicked off from LS to HOST, so LS needs to resolve HOST, and it WILL do it by host name even if you have connected from HOST to LS by IP address. You won't get the problem with a SELECT, there's no need for a transaction.

What identity is the MSDTC service set to run under on each machine? Is it a network account, or the local system account? If the latter, try changing to the former.
0
 
meyerc74Author Commented:
makes sense...wasn't aware that the ls acted like this...added the name of the host to the ls's hosts file and i worked....thanks
0
 
muzzy2003Commented:
Hate to say I told you so, but ...

Glad it worked.
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now