DTS and local servers

[RazeR]

Please, say to me what mean this error message:

OLE/DB provider: "New transaction cannot enlist in the specified transaction coordinator"

It appears when I try to select data from
linked server at such method:
---

begin distributed transaction
select [UserName] from LinkServ.DBName.dbo._SomeTable
where [UserName]='test'
commit tran

---
Without using transaction it works.

[skhanal]

Make sure Distributed Transaction Coordinator service is running in your sql server.

[RazeR]

Transaction Coordinator is running.

[skhanal]

Is it simple select like you have posted?. If you are using complex join with local and remote tables then sql server may have to fetch the remote data in multiple packets and use temporary work table, this may cause the error you are seeing.

To avoid this, use remote stored procedure or view to minimize the rows fetched to local server.

Another situation when this might happen is servers are in different network and you have firewalls between them. DTC uses netbios so both servers should be able to see each other using netbios. You should be able to ping each other by using WINS name.

[RazeR]

Thanks. I wiil try to use remote stored procedures.