Post reply

MSDTC problem

  • February 2, 2008 at 9:50 pm

    #127659

    Hi all,

    I have set up two server and also created linked server between server1 to server2

    I could select from there just fine but when i try to run tirgger on table server1 which updates in server2 it gives me error following:

    OLE DB provider "SQLNCLI" for linked server "Server2" return message "No Transaction is active"

    MSG 7391, level 16, state 2, Procedure servertrigger, line 34

    the operation could not be performed because OLE DB provider "SQLNCLI" for linked sever "SErver2" was unbale to begin a distributed transaction.

    I have configured on both server exactly same:

    with all the option:

    MSDTC > Security Configuration:

    Network DTC Access > TRUE

    Allow Remote Clients > TRUE

    Allow Remote Administration > TRUE

    Allow Inbound > TRUE

    Allow Outbound > TRUE

    Mutual Authentication Required > TRUE

    Enable Transaction Internet Protocol (TIP) Transactions > TRUE

    Enable XA Transactions > TRUE

    Account: NT Authority\NetworkService

    on both server..

    On server2 i tried remote proc trans to on and off but it did not help.

    What am i missing please Help!!!!!!!

  • February 3, 2008 at 7:23 am

    #774577

    Please check for the following:

    1. The DTC service is running on both servers.

    2. SET XACT_ABORT ON is run.

    3. If any of the servers are on a cluster, the DTC on the cluster must have its own IP address.

    4. For a transaction originating from Server1 and linking to Server2, Server2 can not update anything on Server1.

    5. Check the SQL Server names - @@SERVERNAME must return the correct value.

    SQL = Scarcely Qualifies as a Language

  • February 3, 2008 at 7:53 am

    #774579

    I have checked everything and it is correct!!

  • February 3, 2008 at 9:24 am

    #774586

    I could also see this error in Event Viewer :

    Failed to initialize the needed name objects. Error Specifics: d:t\com\complus\dtc\dtc\msdtcprx\src\dtcinit.cpp:457, Pid: 1372

    No Callstack,

    CmdLine: "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\sqlservr.exe" -sMSSQLSERVER

    What does this mean:????

    Thanks

  • February 4, 2008 at 1:50 am

    #774660

    Change Mutual Authentication Required to No Authentication Required.

    Add SET XACT_ABORT ON that's it.

    MCP ID# 1115468 Ceritified Since 1998
    MCP, MCSE, MCP+I, MCSE+I, MCSA,
    MCDBA SQL7.0 SQL 2000, MCTS SQL 2005

  • February 4, 2008 at 5:28 pm

    #774952

    Hi Thanks,

    But that did not work either.. I have MSDTC running on both services. and also have set no authentication required.

    I tried reinstalling again as well.

    Thanks

    Paresh

  • February 4, 2008 at 5:55 pm

    #774956

    hi my one server is on SP1 and other is on RTM does it matter!! Also. does both SQL server needs to be run under local account? also MSDTC on both server run under NetworkService

    Thanks

    PAt

  • February 5, 2008 at 12:53 am

    #775003

    Can you execute distribution transaction and paste here message which you've got?

    MCP ID# 1115468 Ceritified Since 1998
    MCP, MCSE, MCP+I, MCSE+I, MCSA,
    MCDBA SQL7.0 SQL 2000, MCTS SQL 2005

  • February 6, 2008 at 6:33 am

    #775551

    Have you checked to make sure that Allow Remote connections to this server was enabled, and Require distributed transactions was checked off on server to server communication?

    Right click the server, and go to connections and you will see the options.

    The DTC Service can run under network service that is fine.

    that's not an issue.

  • July 16, 2008 at 2:53 am

    #843608

    Hi...

    Please check the windows event log , and you can see the error message about MSDTC . sometimes it would give you some suggests .

    or you can try to reinstall the msdtc:

    on the command line :

    1. net stop msdtc

    2. msdtc -uninstall

    and then restart your PC

    after restarted.

    3. msdtc -install

    reconfig the msdtc .

  • July 17, 2008 at 5:57 pm

    #844731

    I had almost similar issues when running linked servers against multiple domains. Same domain worked fine. I couldnt execute a remote StoredProc to get space info ( as well as other SPs to collect Remote Server's info) into my local table. So I ended up creating a table on that remote server, executing remote Stored Proc into that 'remote' table and then simply running :

    insert [LocalServer].DBA_DB.dbo.[#ServerDriveSpaceInfo]

    select drive,MBFree,MBTotal,PercentFree

    from ['+@RemoteServer+'].master.dbo.ServerDriveSpaceInfo

  • July 22, 2008 at 4:58 pm

    #846744

    I don't know if this is your problem, but read the following:

    http://support.microsoft.com/kb/329332

    I've had this issue a few times.

Viewing 12 posts - 1 through 11 (of 11 total)

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