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?
LVL 1
meyerc74Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.