SQL Server Performance

7391 unable to begin distributed transaction

Discussion in 'General Developer Questions' started by pflick, Aug 12, 2003.

  1. pflick New Member

    Hello. I would truly appreciate anyone's help with the following issue...

    I have two Windows 2000 servers, each running SQL Server. A trigger on a table in server A runs a stored procedure that updates records on server B utilizing many openquery statements. Due to a required upgrade, server B's SQL Server had to be upgraded from 7 to 2000. This, in turn, slowed performance on server A's link to server B. Therefore, a week later, server A was also upgraded from SQL Server 7 to 2000.

    Everything worked ok, until a few days later when server B was rebooted. Now, I can select/update/etc records on server B from server A ok if I do it directly (eg. select count(*) from SERVERB.DBNAME.dbo.tbla). However, it will not work if I do this within an openquery (eg. select * from openquery(SERVERB, 'select count(*) from SERVERB.DBNAME.dbo.tbla'). It returns the following error:

    Error 7391: The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction... New transaction cannot enlist in the specified transaction coordinator.

    Openrowset returns the same error.

    Here is what I have checked:
    1. ran DTCping, everything connected/responded ok.
    2. ran with BEGIN DISTRIBUTED TRAN before openquery statement.
    3. ran with SET XACT_ABORT ON
    4. ran with SET ANSI_NULLS ON
    5. ran with SET ANSI_WARNINGS_ON
    6. sp_serveroption was run setting data access on
    7. sp_addlinkedsrvlogin was run on each server with correct values

    Interesting notes:
    1. select @@servername on server B returns NULL, but select serverproperty('servername') returns the correct name.
    2. there are two remote servers on server A, one to server B and one back to itself
    3. there are two remote servers on server B, one to server A and one back to itself
    4. i cannot delete the remote servers even after running sp_droplinkedsrvlogin for each login. I get the error "still remote logins for server SERVERB"

    I must use openquery due to performance issues.
    Any help is greatly appreciated. Please let me know if you need more info. Thanks again!!!
    Pat
  2. satya Moderator

    Check whether the RPC and RPC out is enabled on the linked server.
    Make sure remote access is enabled on both servers and RPC services are running.

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

    I'd try to run the following on serverb to get @@servername to return the correct name...

    EXEC sp_dropserver 'SERVERB'
    EXEC sp_addserver 'SERVERB', 'LOCAL'

    if you can't issue sp_dropserver successfully then you may have no option to to look in the master..

    select * from master..sysxlogins sl, master..sysservers ss
    where sl.srvid = ss.srvid
    and ss.srvname = 'SERVERB'



  4. pflick New Member

    Thank you for your help. Some more comments...
    Twan - I did a drop then add on the servername, but calling @@servername still returns a NULL. Also, when I run sp_addserver, it puts the server under remote servers. Is this normal? If I remove the remote server, I cannot reference server B at all.

    satya - Yes, both are configured for RPC and RPC out.


    Thanks again!
  5. Twan New Member

    My guess is that the problem is with you remote server entries for SERVERB...

    I'd say try to delete the remote servers, even if it means manually deleting the sysxlogins entries for that server. I'm pretty sure that for this to work, @@servername shouldn't return NULL...

    Be careful though only delete the entries for that remote server...
  6. pflick New Member

    I dropped and added all of the remote servers on both server a and server b. Again, if I delete the remote server 'SERVERB' on server B then server A can't access it at all. I'm thinking that the issue is with the @@servername returning null, however I cannot test this during the day, so I'll have to wait until night to test it.

    If you have any other suggestions, I'd be grateful, otherwise it looks as if I'll have to wait to test this.

    Thank you!
  7. rushmada New Member

    After adding the remote server with the statement

    EXEC sp_addserver 'SERVERB', 'LOCAL'

    U need to shutdown or restart the server once. Once restarted then
    only it will take into effet

    I.e Select @@servername


    Rushendra
  8. pflick New Member

    Thank you all for your help. I was able to reset the servername this morning and everything is working well. Any ideas on how the servername could have been set to NULL? This history is as follows:
    1. upgraded sql server to 2000
    2. a week goes by
    3. restarted server
    4. error occurs

    Thanks again!
  9. Argyle New Member

    It usually happends if you change the computer name.

    /Argyle

Share This Page