Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2003
    Posts
    2

    Unanswered: Linked Server Error - Msg 7391

    I have a problem that has been plaguing me for days and was hoping someone here has an idea.

    I have a server with a database called Scheduler and a linked server called Config. I'm trying to run the following code:

    BEGIN
    DECLARE @SQL VARCHAR(1000)

    CREATE TABLE #Profiles (SiteID INTEGER, Edition INTEGER)

    SET @SQL = 'SELECT HostID, HostName FROM CONFIG.Configuration.dbo.HostSettings'

    INSERT INTO #Profiles EXEC (@SQL)

    /* Test the results */
    SELECT * FROM #Profiles
    DROP TABLE #Profiles
    END

    This code fails when I create the Configuration database on the same server as the Scheduler and point the linked server to, essentially, itself. The error I get is:

    Server: Msg 7391, Level 16, State 1, Line 1
    The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.
    [OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]

    If I have the Configuration database on a different server, everything works fine. I know it sounds odd to do it this way, but that is part of the requirements I was given.

    There shouldn't be any rights issues since I'm using 'sa' to test this and not going over a network.

    I've verified that the Distributed Transaction Coordinator is installed and running correctly. The server is set to accept RPC calls. I'm using both Named Pipes and TCP/IP protocols. I've tried to login using both the local system account and an administrator account for all these services, and nothing seems to help.

    Anyone have any ideas?

    Thanks in advance.

  2. #2
    Join Date
    Sep 2002
    Location
    Brazil
    Posts
    56
    Daniel,

    I had this problem in the past. I installed the Service Pack 02 and seems to work fine.

    Try it and let me know abou the result.
    Diogo Hudson Dias
    DBA - SQL 6.5/7.0/2000
    ASBACE - ATP

  3. #3
    Join Date
    Sep 2002
    Location
    Brazil
    Posts
    56
    Forget my old post.

    I made a test now in a SQL Server 2000 SP3.

    I created a Linked server pointing to my own serv and executed this query:

    begin transaction

    select * from [myserver2].[master].[dbo].[sysobjects]

    commit transaction

    I also got this error:
    Server: Msg 7391, Level 16, State 1, Line 2
    The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.
    [OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]

    The Query works fine when i remove the BEGIN AND COMMIT TRANSACTION. Probably your query open Transactions implicity.

    Im trying to find this KB in Microsoft because i think we can't do this.

    ps.: Sorry the poor english.
    Diogo Hudson Dias
    DBA - SQL 6.5/7.0/2000
    ASBACE - ATP

  4. #4
    Join Date
    Feb 2003
    Posts
    2
    Thank you for trying. I've looked into all the server options I can think of which controls either remote operations or transactions, and nothing I do seems to help.

    For the moment, I've documented the requirement that these databases need to be on different servers (which will be the case once it's in production) but I still want to solve this.

    I'm continuing to look into this and will post whatever I find, though any help from the rest of the community would be greatly appreciated as well.

    Daniel

  5. #5
    Join Date
    Feb 2003
    Location
    Montreal, Canada
    Posts
    117
    It looks more like a security problem to me. (like the prepare phase of the distribuited query returns failure)

    Did you check to security setting for the linked server?

    Although the Sql Server service on the local instance is making the connection, it is making it on behalf of a user with certain permissions.
    Check in the security container - Linked Servers Properties. In the security tab, map local logins to remote logins (select login from the local server under "Local Server Login To Remote Server Login Mappings).
    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •