SQL Server Performance

MSDTC issue

Discussion in 'General DBA Questions' started by the worm, Feb 4, 2003.

  1. the worm New Member

    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?
  2. satya Moderator

    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
  3. bradmcgehee New Member

    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
  4. the worm New Member

    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.
  5. bradmcgehee New Member

    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
  6. the worm New Member

    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.....
  7. Argyle New Member

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

    /Argyle
  8. the worm New Member

    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.....
  9. bradmcgehee New Member

    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
  10. Argyle New Member

    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
  11. bradmcgehee New Member

Share This Page