error distributed query

Question

error distributed query

lioi

SSC Journeyman

Points: 84
More actions
November 28, 2002 at 10:27 am

#58428

Hi,
I m running the distributed update query between 2 servers.
I m running the following query at the Query Analyser in both
servers which are in the same domain. It's works fine.
When I tried to run the same query at client's servers, which the 2
servers are in the diffirent domains, I get the following error msg:-
--------------------------------------------------------------------
BEGIN DISTRIBUTED TRANSACTION
UPDATE authors SET au_lname = 'McDonald' WHERE au_id = '409-56-7008'
UPDATE remote.pubs.dbo.authors SET au_lname = 'McDonald' WHERE au_id
= '409-56-7008'
COMMIT TRAN
GO
--------------------------------------------------------------------
msg 7391, level 16, state 1, line 2
the operation could not be performed because the OLE DB
provider 'SQLOLEDB' was unable to begin a distributed transaction.
[OLE/DB provider return message: New Transaction cannot enlist in the
specified transaction coordinator.]
for us information, both of the servers have:
(1) MS DTC started
(2) Linked server added
(3) user's permission to access the particular database is given.
what could be the problem ?
Best Regs

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

K. Brian Kelley SSC Guru Points: 114703 More actions · Answer 1

You've indicated they are in different domains. Are there any other differences, such as a firewall in between or anything of that sort?

K. Brian Kelley

http://www.truthsolutions.com/

Author: Start to Finish Guide to SQL Server Performance Monitoring

http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1

K. Brian Kelley
@kbriankelley

lioi SSC Journeyman Points: 84 More actions · Answer 2

No, there are not firewall between the two domain, and I'm using DNS resolution name.

I had to use WINS name resolution ?

Thanks

K. Brian Kelley SSC Guru Points: 114703 More actions · Answer 3

Name resolution shouldn't matter in this case, so long as to the servers can identify each other. The only other KB article (if there aren't firewalls and the like in the way) I can find is one where more than 16 nodes are trying to enlist in the transaction.

You're able to select from the remote table without issue, right? What happens if you don't wrap it in a transaction?

K. Brian Kelley

http://www.truthsolutions.com/

Author: Start to Finish Guide to SQL Server Performance Monitoring

http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1

K. Brian Kelley
@kbriankelley

lioi SSC Journeyman Points: 84 More actions · Answer 4

I resolve this problem using WINS name resolution on both server.

This is not specified in Microsoft documentation, but this is the only way I find to resolve it

Thanks

K. Brian Kelley SSC Guru Points: 114703 More actions · Answer 5

Hrm. That shouldn't be the only solution because in AD you are supposed to work to try and get away from WINS altogether.

What versions of OS and SQL Server? If both are Win2K, does each server have both domains configured for domain suffix search order under DNS?

K. Brian Kelley

http://www.truthsolutions.com/

Author: Start to Finish Guide to SQL Server Performance Monitoring

http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1

Edited by - bkelley on 11/29/2002 1:18:08 PM

K. Brian Kelley
@kbriankelley

Antares686 SSC Guru Points: 125444 More actions · Answer 6

If you were using the computer name and that name is not recognized on the local subnet then it may not be able to find it. We have run across this with WINS serveral times. When doing TCP/IP is is easier to use the IP address if known rather than the name as if there is no listing for the machine name of the WINS server that a machine is hitting it will not look too far. Adding an entry to LMHOST file should have had the same effect or even using Client Networking utility to map an IP to an alias.

K. Brian Kelley SSC Guru Points: 114703 More actions · Answer 7

True, if they are both not on the same subnet, then a broadcast won't resolve a NetBIOS based name. Hence the need for WINS. However, if DNS is set up and both servers point to DNS and self-register, this isn't an issue, either. I guess I assumed he had dynamic DNS up, but that's probably not a safe assumption.

K. Brian Kelley

http://www.truthsolutions.com/

Author: Start to Finish Guide to SQL Server Performance Monitoring

http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1

K. Brian Kelley
@kbriankelley

lioi SSC Journeyman Points: 84 More actions · Answer 8

I think that the name resolution was a problem of MSDTC, because the single queries works fine on both server, and only the distributed transaction don't work.

Thanks