SQL Server Performance Forum – Threads Archive

MSDTC issue

hello, we are getting an error running a query that references a linked server. it says "MSDTC unavailable". under system management it shows the MSDTC service running. microsofts website is not very helpful, stating basically what the error message states. how do we make MSDTC available if its already supposedly running?

Use SQL Server Service Manager to verify that the MSDTC service has been started on the server. For more information, refer to BOL for MSDTC documentation. Satya SKJ

A query ran on a linked server may or may not need MSDTC to run. It depends on how the transaction is built. Without seeing the query, I am assuming that there is some reference to a distributed transaction and because MSDTC is not running you are getting the error. Or, maybe I am wrong. The only way to find out is to look at the problem query and see if it is part of a distributed transaction. If it is, the get MSDTC running. But if it is not, then the error you are getting may be misleading and another problem exists. From where I am, I can’t suggest much more because I don’t have enough data. You can tell if MSDTC is running by looking under the Services utility. If it is not running, you can turn it on from there. Also, you may want to set MSDTC to run automatically from within Enterprise Manager. ——————
Brad M. McGehee
Webmaster
SQL-Server-Performance.Com

here is the query:
USE database1
go
SET IMPLICIT_TRANSACTIONS ON
go
IF EXISTS (SELECT ‘X’ FROM SYSOBJECTS WHERE TYPE = ‘V’ AND NAME =
‘PS_ND_HR_SA_VW’) DROP VIEW PS_ND_HR_SA_VW
go
CREATE VIEW PS_ND_HR_SA_VW (EMPLID, NAME, LAST_NAME_SRCH,
FIRST_NAME_SRCH, NATIONAL_ID, BIRTHDATE, CITY, STATE, ADDRESS1,
COUNTRY, SEX) AS SELECT A.EMPLID , A.NAME , A.LAST_NAME_SRCH ,
A.FIRST_NAME_SRCH , B.NATIONAL_ID , A.BIRTHDATE , A.CITY , A.STATE ,
A.ADDRESS1 , A.COUNTRY , A.SEX FROM server2.database2.dbo.PS_PERSONAL_DATA
A , server2.database2.dbo.PS_PERS_NID B WHERE A.EMPLID = B.EMPLID AND
B.PRIMARY_NID = ‘Y’ UNION SELECT C.EMPLID , C.NAME , C.LAST_NAME_SRCH
, C.FIRST_NAME_SRCH , D.NATIONAL_ID , C.BIRTHDATE , C.CITY , C.STATE
, C.ADDRESS1 , C.COUNTRY , C.SEX FROM PS_PERSONAL_DATA C , PS_PERS_NID
D WHERE C.EMPLID = D.EMPLID AND D.PRIMARY_NID = ‘Y’
go
COMMIT
go it seems to work sometimes through query analyzer but sometimes it will lock it up. we have verified that MSDTC is running.

This is not a distributed transaction, so MSTDC should not be required. Why are you using the SET IMPLICIT_TRANSACTIONS ON option? Try removing it, along with the COMMIT and see what happens. Also, if you use SET IMPLICIT_TRANSACTIONS ON, you should also use SET IMPLICIT_TRANSACTIONS OFF when you are done. But in this query, you shouldn’t need to use this option. ——————
Brad M. McGehee
Webmaster
SQL-Server-Performance.Com

i agree with you 100%, and i actually told our guys here that you should not need that. we still get the error thought even with the implicit transactions and the commit gone. the only reason we think its msdtc is because its in the error message. we have adjusted the timeout parameters and it could be as simple as that. why would the error say "error 8501 MSDTC not available" or something to that effect if it doesn’t involve MSDTC?? could security and rights be an issue here? thank you for your help. once i find a definitive answer i’ll post it up so the resolution is here for all to see. hopefully it’s not something to obvious or easy…..

MSDTC has to be running on both servers involved in distributed transaction. Check that that is the case. /Argyle

yes, msdtc is running on both servers. i’ll try to get some row counts and see if the size of the query could be an issue…..

I have seen SQL Server, and other software, produce error messages that are misleading or wrong, so I don’t always take them at face value. You mentioned the possibility of a security issue. I think you should investigate this as I have also had security-related issues with linked servers, and they are troublesome to track down. ——————
Brad M. McGehee
Webmaster
SQL-Server-Performance.Com

Maybe there is a firewall between the servers? MSDTC uses dynamic ports unless you specify otherwise which often is a problem if there is a firewall in the way. /Argyle

Another thing hit me. Is this a distributed partitioned view? If so, they have their own rules, and you may want to review them in the Books Online. Also seehttp://www.sql-server-performance.com/federated_databases.asp ——————
Brad M. McGehee
Webmaster
SQL-Server-Performance.Com

]]>