Capturing Results of Stored Proc calls Using a Linked Server

SQL Add comments

All, When working on a Stored Procedure using a Linked Server, the following error was occurring when trying to capture the results of another stored proc within the same procedure i.e.

 

-- CREATE A TEMP TABLE TO STORE THE RESULTS FROM THE REMOTE STORED PROC CREATE TABLE

#tmpTableName( columnName1 INT ,columnName2 NVARCHAR(10) ,columnName3 FLOAT ,columnName4 FLOAT ,columnName5 NVARCHAR(256) ,columnName6 INT ) 

INSERT #tmpTableName EXEC LINKED_SERVER.DATABASE_NAME.OWNER.sspStoredProcName @argument1, @argument2

We were able to call the remote stored procedure own it's own fine

EXEC LINKED_SERVER.DATABASE_NAME.OWNER.sspStoredProcName @argument1, @argument2

but when trying to capture the results the below error occurred.


[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, Line 10 The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction. 

Thanks to Marc Dowie for his help with this on the Server Side - the resolution was to ensure theat DTS \ DTC was enabled, running and configured correctly: If you follow the steps below - this should sort this issue for the above error!
Steps to enable network DTC access:

  1. Click Start, point to Control Panel, and then click Add or Remove Programs.

  2. Click Add/Remove Windows Components.

  3. Select Application Server, and then click Details.

  4. Select Enable network DTC access, and then click OK.

  5. Click Next.

  6. Click Finish.

  7. Restart the computer.

If you are running Windows Server 2003 Service Pack 1 (SP1), you must follow these additional steps:

  1. In Control Panel, open "Component services".

  2. Expand Component Services, expand Computers, right-click My Computer, and then click Properties.

  3. On the MSDTC tab, click Secuity Configuration under Transaction Configuration, click to select the Network DTC Access check box under Secuity Settings, and then click to select the following check boxes under Transaction Manager Communication:

    • Allow Inbound

    • Allow Outbound


  4. You cannot select Mutual Authentication Required. Therefore, click to select one of the following check boxes:

    • Incoming Caller Authentication Required

    • No Authentication Required


    Note For more information about these options, click the following article number to view the article in the Microsoft Knowledge Base: 899191 (http://support.microsoft.com/kb/899191/ ).
    New functionality in the Distributed Transaction Coordinator service in Windows Server 2003 Service Pack 1 and in Windows XP Service Pack 2

  5. Make sure that the Logon Account is set to NTAUTHORITYNetworkService.

  6. Click OK. A message box explains that the MS DTC Service will be stopped and restarted, and that all dependent services will also be stopped and restarted. Click Yes.
    Note If this is a Majority Node Set (MNS) cluster, do not use the MNS resource as the storage device for MS DTC. MS DTC requires a storage resource such as a physical disk.

  7. Restart the computer.

 

Bookmark and Share

0 responses to “Capturing Results of Stored Proc calls Using a Linked Server”

Leave a Reply

Leave this field empty:

Powered by Mango Blog. Design and Icons by N.Design Studio