Error trying to run query across servers

  • Good day,

    I posted this in T-SQL, and got some help, but I had already tried all that - so, I am hoping for more help.

    I will do my best to explain the situation, but please feel free to ask more questions.

    I have a server that used to make a call to another server for data.  Now, the data resides on a NEW box on a new domain.  Ever since this happened, the SP does not work, giving the following:

    [OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]

    OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a].

    Msg 7391, Level 16, State 1, Procedure dtc_spTest_cory, Line 16

    The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.

    So, here is the setup:

    Domain_1/Server_A - SQL Server 2000 with Windows Server 2000 (fully patched and SP'ed)

    Domain_2/Server_B - same set up

    Server_A has a SP that creates a temp table, then insert the data into the temp table, calling another SP on the linked server.  See below:

    alter

    PROCEDURE [dbo].[dtc_spTest]

    AS

    BEGIN

    Create

    TABLE #tmpPractice

    (

    uniqueID

    INT,

    Name VARCHAR

    (75),

    StateAbbrev VARCHAR

    (4),

    StartDate

    DATETIME,

    ExpectedStudies

    FLOAT,

    Priority

    SMALLINT,

    Status

    SMALLINT

    )

    INSERT INTO #tmpPractice

    EXEC

    <linkedServer>.<DB>.dbo.<SP> '8/31/06', 1,1,1,1

    END

    Running simply the EXEC statement from Server_A works to Server_B, but inside the SP, I get the above error...Please help!

    I have verifed that Network DTC is enabled, the @@ServerName is set correct, Component Services is set as required, ect, all google searches have been tried...

    TIA,

    Cory

    -- Cory

  • Cory,

    I had THE SAME problem when connecting by Openrowset to several servers from any of my SQL 2000 servers. Connection to some servers was successful and when connecting to other servers  I got this message. Everything was working fine as Select, but as soon as I was inserting the results into the Temp Table it was the distributed transaction right away and as i say, it was working with some servers but not with others if run from SQL 2000.

    There were a lot of discussions on this site about this problem and the common advice was to set correct security in DTC (Distributed Transaction Coordinator) in Component Services applet. I did all that and it did not help. My queries are working well from SQL Server 2005 connecting to all servers, so I run the job from SQL 2005.

    Regards,Yelena Varsha

  • I have tried this on nearly every computer...and what has seemed interesting is that when the machines are on the same subnet and or domain, this works...however, the cross domain / subnet is not working.  I am beginning to think it has something to do with that.  Especially since no one here seems to have an answer...I have never been let down before.

    -- Cory

  • I have a question for you, inside the SP call, is there a BEGIN TRAN statement?

    Mark

  • inside the actual SP, I have tried it both ways.  The current version does not have that statement, I have tried adding it, the begin trans and commit, I have tried "SET xact_abort ON", "SET REMOTE_PROC_TRANSACTIONS ON", and I think every combination. 

    I have gone thru everything on this link: http://support.microsoft.com/?kbid=839279

    Please, I will try just about everything.  As I have said before, I still think this is not really an issue with the code, more the fact that the servers are on different subnets and or domains.  I am trying to eliminate all possibilities.

     

    -- Cory

  • I have this same problem. Any resolution to this yet? It may have something to do with triggers. Triggers are part of the transaction.


    Ronald San Juan | SQL DBA
    ID 710124 ~ Code "Northwind"

  • Is the Distributed Transaction Coordinator service running?

    Mark

  • Yes, stated in the post above, network DTC is up and running. 

    -- Cory

  • Cory,

    Your posting is from 6/6/2006...did you ever resolve the issue?

    -SQLBill

  • I was trying to recall that just today.  I cannot recall if it got fixed, or we worked around it.  Either way, right now, it is no longer an issue.  I assume we worked around the problem, otherwise I would have posted my solution.

    Sorry

    -- Cory

  • No problem. I was just going to ask if you had checked to see if:

    1. there was a firewall between the domains.

    2. if the SP was being run by a login that also had permissions on the database on the other server.

    -SQLBill

  • I saw three related/similar posts in this forum/site. None of which have a finite solution.


    Ronald San Juan | SQL DBA
    ID 710124 ~ Code "Northwind"

  • Thanks SQLBill,

    There was no corporate firewall between these domains, I was never really able to find out if windows 2003 had some sort of firewall or not that could have been an issue.

    The SP was run by a login that existed on both server with permissions.

    -- Cory

  • http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=5&messageid=263197

    and

    http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=5&messageid=293095

    have similar situations.

     

    The first is very tricky as the statement

    Set @Var = Exec ServerName.DB.DBO.proc

    works while

    Insert TableName

    Exec ServerName.DB.DBO.proc

    doesn't.

     


    Ronald San Juan | SQL DBA
    ID 710124 ~ Code "Northwind"

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply