meyerc74
asked on
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
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::JoinTran
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?
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
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::JoinTran
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?
ASKER
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
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.
ASKER
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
by itself returns a recordset
i only get the error when i try to insert the results directly into a temp table
the servers can communicate fine, EXEC ls_oms.orderManagement.dbo
by itself returns a recordset
i only get the error when i try to insert the results directly into a temp table
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
Hate to say I told you so, but ...
Glad it worked.
Glad it worked.
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)?