SQL Server Performance

Transaction Coordinator issue

Discussion in 'General DBA Questions' started by thamest, Dec 5, 2003.

  1. thamest New Member

    I have just moved several SQL Server databases from a SQL Server 7.0 with NT OS to a SQL Server 7.0 with Windows 2000 OS. Each of these databases are accessed via web server. I have no problems connecting to the db when I set up the ODBC connection and all of the databases are functional.

    I moved the databases by backing them up on the current system, creating a blank database on the new server with the same db names and then moving the backups to the new server and restoring db from file forcing an overwrite.

    My problem now is that when I try to do anything on the web server, I get an error message dealing with the transaction coordinator.

    The actual message that comes up on the screen is this:

    *modMTSAppDO.GetConnection - Microsoft OLE DB Provider for SQL Server Line #: 114 error '8004d00a'

    New transaction cannot enlist in the specified transaction coordinator.


    I am stuck and haven't had much luck searching the web for assistance. Most of the assistance deals with firewall issues. I do not have a firewall between the Web server and the SQL Server. Each server has the latest upgrades and service packs.

    Any and all help is appreciated.

    Tod
  2. satya Moderator

    Ensure MSDTC is started on the SQL server.
    Any firewall is involved between the servers?
    If so open the port 135 which is used by MSDTC.

    Execute SP_HELPSERVER and check RPC OUT is showing up.
    Check option for "Remote proc trans" option.

    Bad or altered MSDTC install. This is fixed by uninstalling and reinstalling MSDTC.
    Check this KBAhttp://support.microsoft.com/defaul...port/kb/articles/q279/7/86.asp&NoWebContent=1 to reinstall MSDTC.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  3. thamest New Member

    I am still having issues. I read on Technet to download a program called DTCPing on both systems. I did so and tried it. It stated that both systems were communicating, however I am still getting errors when I attempt to work from the web based system. Below is a copy of the error message I receive.


    Description: New transaction cannot enlist in the specified transaction coordinator.

    Any and all help is greatly appreciated. I'm dead in the water until I get this resolved.

    Thanks in advance for any help.

    Tod
  4. thamest New Member

    I am also getting this error in my error log.

    *modMTSAppDO.GetConnection - Microsoft OLE DB Provider for SQL Server Line #: 114 error '8004d01c'

    MSDTC on server 'NC-37350-APP09' is unavailable.

    /Discharges/ngb22_in_progress.asp, line 12
  5. Argyle New Member

  6. thamest New Member

    I'm not sure what's going on. I have a single SQL Server that a web app server talks to. I do not have multiple SQL Servers that work together. The web server was previously working when the db's were on a SQL server running NT 4.0. We migrated the db's over to a SQL Server running Win2000. We used the same version of SQL Server 7.0. I read on Technet to recreate the problem type the following code in Query analyzer.

    USE pubs
    GO BEGIN DISTRIBUTED TRANSACTION
    UPDATE authors SET au_lname = 'McDonald' WHERE au_id = '409-56-7008'

    I did this on both servers. It worked on my old server and DID NOT work on my new server. Same error.

    Server: Msg 8501, Level 16, State 3, Line 1
    MSDTC on server 'NC-37350-APP09' is unavailable.

    Since I am running the above update within SQL Server, I don't think it is an issue of the servers communicating. I think there is something wrong with MSDTC on my server running Win2000.

    Again, any and all help is appreciated.
  7. Argyle New Member

    It doesnt have to be multiple sql servers working together. The web server has a MSDTC service as well. Is it running there?

    What role has the server named: 'NC-37350-APP09' . Is it the SQL or web server?

    /Argyle
  8. thamest New Member

    The MSDTC is running on both servers. The web server runs fine when it points to my old SQL Server. The web server doesn't have SQL server loaded. APP09 is the SQL Server. I ran the UPdate query on the sql server and received the message.

    My SQL Server doesn't work even when I attempt a distributed transaction on itself (taking the web server out of the picture).
  9. satya Moderator

  10. thamest New Member

    Tried the last post. It still doesn't work. I'm at a loss. I'm going to reload SQL Server 7.0 and see what happens.
  11. satya Moderator

  12. thamest New Member

    We have already tried to rebuild the MSDTC per the article you referenced.
  13. satya Moderator

    Oh, then it leaves none other than reinstall SQL tools.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  14. Argyle New Member

    Have you by any chance built the new server with a ghost image or similar from another server? Becasue then the MSDTC will get the same ID as that server and will not work.

    /Argyle
  15. thamest New Member

    No, the server was built from scratch without a ghost image.

    Now, I am able to perform the transaction:

    USE pubs
    GO BEGIN DISTRIBUTED TRANSACTION
    UPDATE authors SET au_lname = 'McDonald' WHERE au_id = '409-56-7008'

    BUT, it works when I run it from Query Analyzer. It states 1 row updated, however, when I go into look at the data in the table, it will not open the table. If I close Query Analyzer, the table will open, but the row is not updated. I can run the above script in Query Analyzer, then do a Select * from authors, and it shows the changes. If I close Query Analyzer and reopen Query Anaylzer and use Select * from authors, it reverts back to the old information.

    Any and all help is appreciated.

    Tod
  16. harsh_sr New Member

    is COMMIT TRANSACTION missing?
  17. satya Moderator

    True, it is the responsibility of the programmer to issue COMMIT TRANSACTION only at a point when all data referenced by the transaction is logically correct.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  18. Twan New Member


    It looks like you may have got passed this point now, but also make sure that you can execute "select @@servername" and get a valid name (i.e. not NULL)

    Cheers
    Twan
  19. thamest New Member

    I THINK I'M GOING CRAZY.

    Okay, the "Distributed Transaction" worked as long as I used the Commit Transaction at the end of the SQL Statement. Since it worked, I went ahead and reconfigured my Web server to point at the "new" SQL Server DB. Now, I'm getting the same error message as before.

    All servers are on the same domain.

    There IS NOT a firewall in between any of the servers.

    The new SQL Server was built from scratch - we didn't use a ghost image.

    The system works as long as the web server "points" to the SQL Server with NT (SMS). When I "point" it to the new server "app09", I get the following errors on my webpage.

    *modMTSAppDO.GetConnection - Microsoft OLE DB Provider for SQL Server Line #: 114 error '8004d00a'

    New transaction cannot enlist in the specified transaction coordinator.


    This is really starting to drive me crazy. The new server is configured exactly like the older server.

    Any and all help is appreciated.

    Tod
  20. Argyle New Member

    Have you followed the steps mentioned in the URL refered above?

    I'll paste them in here:

    1.
    MSDTC has to be running on both the SQL Server and the client machine/server (web/app server, workstation etc.) to be able to work.

    2.
    If the MSDTC service is running on both machines but still doesn't work then it's most likely due to a name resolution issue or a firewall. MSDTC needs to be able to do name lookups (in both directions) on the servers involved to be able to function.

    To check this try and ping the SQL server by name from the web/app server and vice versa ping the web/app server by name from the SQL server. IF it's a cluster be sure to ping the virtual SQL Server name.

    Use the DTCping.exe utility that Twan refered to:
    http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q306843

    You will notice (when using DTCping) that even if the client connects to the MSDTC service trough the virtual SQL Server name it will sometimes still resolve the physical node name of the node that currently owns the MSDTC service in the cluster. So make sure that name reolution works for the physical node names as well and not only the virtual names.

    3.
    If DNS or WINS are miss-configured or traffic is blocked in routers or something then add name and IP address entries in "lmhosts" and "hosts" files on the involved SQL server and web/app server or fix the DNS or WINS.

    4.
    If there is a firewall between the servers look at this article on what ports to open:

    INFO: Configuring Microsoft Distributed Transaction Coordinator (DTC) to Work Through a Firewall (Q250367)
    http://support.microsoft.com/default.aspx?scid=kb;EN-US;q250367

    /Argyle
  21. thamest New Member

    1. MSDTC is running on both systems.

    2. I logged into the web server (NC-37350-APP10) and pinged the SQL Server (NC-37350-APP09). The ping was successful.

    I logged into the SQL Server (NC-37350-APP09) and pinged the web server (NC-37350-APP10). The ping was successful.

    3. I'm not usre if DNS or WINS is miss-configured. The server was built just like the old server.

    4. There is no firewall between the servers.


    I'm lost. I just don't know what else to do. We have completely rebuilt the SQL Server. I don't want to rebuild my web server as it is currently operating fine as long as it points to the old SQL Server.
  22. satya Moderator

    As you'd tried all the counters referred and last resort is to contact MS Support for a solution or fix.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  23. Twan New Member

    You have checked that select @@servername returns the correct name on the SQL Servers?

    Cheers
    Twan
  24. Argyle New Member

  25. satya Moderator

    Argyle

    I believe Thamest passed that level also as specified earlier.
    --------------------------------------------------------------------------------
    I am still having issues. I read on Technet to download a program called DTCPing on both systems. I did so and tried it. It stated that both systems were communicating, however I am still getting errors when I attempt to work from the web based system. Below is a copy of the error message I receive.
    --------------------------------------------------------------------------------
    Fyi


    quote:Originally posted by Argyle

    Download the DTCping utility as well and try. It will often show a detailed error message.

    http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q306843

    /Argyle

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  26. Argyle New Member

  27. thamest New Member

    I downloaded the DTCtester tool and utilized it. It came back with the error messages. I forwarded the error messages to my Network manager. I think the error is with the configuration of the server. It has multiple Net Cards. Hopefully he will find something out and let me know. If anyone has any ideas on configuration with mulitple Net cards, please feel free to leave comments. At least now I can a testing tool that will allow me to ensure the SQL server is accepting transactions before I change everything on my web server to point to it. It's painful to reconfigure everything and realize it doesn't work and then have to change it back to the way it was.

  28. Argyle New Member

    I run into a MSDTC issue with multiple IPs. Make sure than name resoultion works and resolves to the IP you intend to use for MSDTC. If you for example have a frontend and management IP make sure the name resolves to the frontend ip. This goes for both when the servers (source and destination) ping or resolve eachothers name as well as locally on each server when it resolves it's own name.

    You might have to change the binding order of your networks to get around this.

    Also if you have muliple names on the server, for example in a cluster, all names need to be resolvable (node names, cluster names etc) from all parties involved in MSDTC.

    /Argyle
  29. thamest New Member

    Okay - here is what I know.

    There are multiple NICs on the SQL Server. They are configured as a failsafe (a single nic). We reconfigured them so it only had 1 NIC and re ran the DTC Tester. Same error. We checked the name resolution and the IP and both server work with each other. So - we loaded DTC Tester onto another web server and it worked fine.

    So - - - - That leads me to beleive the problem is NOT with the configuration of the SQL Server and DTC - - - the problem is with my current web server. My current web server however is currently working as long as it is pointing to the database server that is on NT.

    In short - current web server (app10) is point to db server (sms - NT box) and is working fine. When I point (app10) to new db server (app09 - WIN2000), no connection can be established and I get a transaction coordinator error. Loaded DTCTester on app10 to test connection with app09 - didn't work. Loaded DTCTester on another webserver to test connection with app09 - worked fine.


    Could DTC be improperly configured on app10??

    Any ideas are welcome.
  30. Argyle New Member

    Could you post the output from the DTC tester tool (both direction tests and from both the working and non working webserver)
  31. thamest New Member

    I can only use DTCTester from the Web server to the SQL Server. It creates an ODBC connection to the sql server and tests it.

    Here is the error message.

    Initializing DTC
    Beginning DTC Transaction
    Enlisting Connection in Transaction
    Error:
    SQLSTATE=25S12, Native error=-021474467259, msg=#%92[Microsoft][ODBC SQL Server Driver]
    Distributed transaction error#%92
    Error:
    SQLSTATE=24000,Native erro=0,msg-[Microsoft][ODBC SQL Server Driver]Invalid cursor stte
    Typical Errors in DTC Output When
    a.Fireewall Has Ports Close
    -OR-
    b. Bad WINS/DNS entries
    -OR-
    c. Misconfigured network
    -OR-
    d. Misconfigured SQL Server machine that has multiple netcards.
    Aborting DTC Transaction
    Releasing DTC Interface Pointers
    Successfully Released pTransaction Pointer


    When I use another web server and point to the same sql server. it simply asks if I want to commit the transaction.

    Tod
  32. thamest New Member

    [<img src='/community/emoticons/emotion-2.gif' alt=':D' />]IT'S WORKING[<img src='/community/emoticons/emotion-2.gif' alt=':D' />]<br /><br />Thanks to each and everyone who assisted in this endeavor. Satya and Argyle - thank you very much. <br /><br />I finally had to reload DTC on the webserver. The DB server was working fine.<br /><br />Thank you all.
  33. Argyle New Member

    Glad that it got solved <img src='/community/emoticons/emotion-1.gif' alt=':)' />
  34. kcnagaraju New Member

    Hi,
    I am having the same problem like you. If you had any breakthrough on this, please help me. Appreciate your help.

    Thx.
    Nagu.


    quote:Originally posted by thamest

    I downloaded the DTCtester tool and utilized it. It came back with the error messages. I forwarded the error messages to my Network manager. I think the error is with the configuration of the server. It has multiple Net Cards. Hopefully he will find something out and let me know. If anyone has any ideas on configuration with mulitple Net cards, please feel free to leave comments. At least now I can a testing tool that will allow me to ensure the SQL server is accepting transactions before I change everything on my web server to point to it. It's painful to reconfigure everything and realize it doesn't work and then have to change it back to the way it was.


  35. Argyle New Member

    What does the DTCtester tool say?
  36. bradyp New Member

    I noticed that only one DTC resource can be specified per cluster.



    You need to stop and restart DTC and then SQL Service also. (Might have to reboot and ensure that DTC starts before SQL does).

    817064 HOW TO: Enable Network DTC Access in Windows Server 2003

    http://support.microsoft.com/?id=817064



    On Win 2003 you need to enable the DTC. Network DTC access is turned off by default.

    You can enable DTC by doing the following:

    - Go to the Component Services manager (under Start->Control Panel->Administrative tools).

    - Once the Component Services manager is running select:

    Component Services – Computers - My Computer

    and right click on the “My Computer” and select Properties.

    - Select the “MSDTC” tab in the property sheet. Press the “Security Configuration” button.

    - Check “NetworkDtcAccess” and any of the other checkboxes which apply.

    ______________________________________________



    If this doesn#%92t work then nothing else came up in my search so I would suggest opening an incident with PSS. Robin has done this before, but the documentation is up onhttp://mscommunity/Support/Document Library/How to open a Microsoft Premier Support Incident.doc

Share This Page