Post reply

Can't figure out MS DTC error

  • December 12, 2014 at 5:27 pm

    #397775

    I am working directly in SSMS and have a Linked Server that has been working for me with OPENQUERY and also with remote execution of stored procedures. For example, the calling the sproc below successfully returns a valid recordset.

    EXEC [11.169.101.05].MyDB.MySchema.MySproc

    But when I try to go a step further and insert that recordset into a table in the the local db with this code:

    INSERT INTO LocalDb.LocalSchema.LocalTable

    EXEC [11.169.101.05].MyDB.MySchema.MySproc

    I get the following error: Import of Microsoft Distributed Transaction Coordinator (MS DTC) transaction failed: 0x8004d00e(XACT_E_NOTRANSACTION).

    I can't find much information on the error and can't figure out why I can return records but can't write to the DB.

    I did find this resource MSDTC Troubleshooting, which provided a short snippet of code for testing that also fails with the same error. What's interesting is this one fails on a simple query that is wrapped in a transaction...no writing involved.

    begin distributed tran

    select * from [11.169.101.05].master.sys.sysprocesses

    commit tran

    If I run it without the transaction, it will pull back the records successfully

    select * from [11.169.101.05].master.sys.sysprocesses

    -- works fine....no clue why...I'm baffled!!!

    Can anybody help me troubleshoot or advise on how to figure this out?

    Many thanks,

    Alex

  • December 12, 2014 at 10:21 pm

    #1764595

    Quick question, what is the output of this query?

    😎

    EXEC sp_configure 'show advanced options',1;

    RECONFIGURE;

    EXEC sp_configure 'Ad Hoc Distributed Queries';

    EXEC sp_configure 'show advanced options',0;

    RECONFIGURE;

  • December 13, 2014 at 12:11 am

    #1764596

    The config and run_value for Ad Hoc queries = 1 on both servers.

  • December 13, 2014 at 3:11 am

    #1764599

    And what about option SET XACT_ABORT ON did you set this on for the transaction?

    "XACT_ABORT must be set ON for data modification statements in an implicit or explicit transaction against most OLE DB providers, including SQL Server. The only case where this option is not required is if the provider supports nested transactions. "

    http://msdn.microsoft.com/en-us/library/ms188792.aspx

  • December 15, 2014 at 4:37 am

    #1764687

    Hi,

    Consider using OPENROWSET.

    SELECT * INTO LocalDb.LocalSchema.LocalTable FROM OPENROWSET('SQLNCLI', 'Server=localhost;Trusted_Connection=yes;',

    'EXEC [11.169.101.05].MyDB.MySchema.MySproc')

    Hope it Helps...!!

    Regards

    Shafat Husain

    Regards,
    Shafat Husain
    🙂
    And Your Lord Never Forgets...!! (64:19 -- Quran)

Viewing 5 posts - 1 through 4 (of 4 total)

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