March 5, 2003 at 11:03 am
On the Connections tab on the SQL Server Properties, there is a checkbox called 'Enforce Distributed Transactions (MTS)' and it is not checked by default in the SQL 2000 Installation.
Does anyone know if this should be checked if one is doing many Linked Server connections?
The only thing I can find about it at Microsoft is how to turn it on, but I don't know what it is or what it does.
I'm running 4 SQL2k SP3 Enterprise Edition systems and am having numerous errors or slow reponses to distributed queries. I am at the point of grasping at straws and wondered if this would help/hinder.
TIA for any advice.
Michelle
March 10, 2003 at 12:00 pm
This was removed by the editor as SPAM
March 11, 2003 at 9:51 am
We had massive problems getting DTS to work.
In the end it cam down to each server being able to see each other server by name rather than by IP address. Other problems we had along the way involved firewalls and collation settings. We have code full of COLLATE statements now as our servers require different settings. The best tool for diagnosing DTC problems is DTCPing which, if memory serves, we got from Microsoft.
Check that there are no firewalls between the servers, or that they are open for traffic from server to server. Check that each server can DTCPing to each other BY NAME. Check that collations are the same of cast if different.
Good luck. It took us months!
Steve
Steve
March 11, 2003 at 10:29 am
Thanks StephenKendrick -
We ended up calling Microsoft yesterday. Very Helpful and Very Thorough in working with me.
Turned out that our sysadmin was installing the operating systems with an image. That caused the CIDs to be exact dupes over all four SQL Servers. When we tried to run our distributed query it was trying to communicate with itself instead of the server we needed it to communicate with.
DTCPing was the tool that helped diagnose the problem. It showed the duplicated CIDs and then we were able to change them.
306843 Is the Microsoft Knowledgebase Article. If you try to search the support.microsoft.com site for DTCping, you do not receive any hits.
162001 Is the Microsoft Knowledgebase Article explaining why not to use imaging software.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vcsample98/html/_sample_mfc_guidgen.asp is a link to how you can get the GUIDGEN.exe program compiled from Visual Studio.
Hope this helps others, I have spent a week looking for solutions and was finding nothing. Microsoft originally thought there might be a lock-down in SQL 2000 for OLE-DB Loopback issues. But our traces didn't show this problem. When our server has SQL 7.0 we didn't have the problem. However when we upgraded to SQL 2000 we reinstalled the operating system (image) and that is how we got the duplicate CID.
Thanks,
Michelle
Michelle
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply