Distributed transactions with multiple instances of Microsoft SQL Server

[chairleg]

Hi,

I'm having a problem running a distributed transaction between two
linked servers that both have multiple instances of SQL Server
installed on them. This is the error message that I receive:

"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]."

The query follows the format:

"BEGIN DISTRIBUTED TRAN
UPDATE [LINKEDSERVER1\INSTANCE_NAME].DB.OWNER.TABLENAME
SET fieldname = alias2.fieldname
FROM tablename alias2
JOIN [LINKEDSERVER1\INSTANCE_NAME].DB.OWNER.TABLENAME alias1
on alias2.urn=alias1,urn"

From what I can gather from various sources the SQL Server must be

named the same as the computer which it is installed on. However, if I
have two instances of SQL Server, they cannot both be named the same as
the computer. Does anyone know of a way around this or whether I'm
barking up the wrong tree completely?

Many thanks.

[Simon Hayes]

Assuming that you can query the linked server successfully (ie run a
SELECT query), then the issue may be DTC rather than the instance names
- there are a number of possible reasons for the error:

http://support.microsoft.com/kb/306212
http://support.microsoft.com/kb/816701
http://support.microsoft.com/kb/839279

Simon

[chairleg]

Thanks! This one fixed it: http://support.microsoft.com/kb/816701. I
was using Windows 2003 server which has DTC access disabled by default.