[Last Call] Learn how to a build a cloud-first strategyRegister Now


Errors on Stored Procedure with Linked Server...

Posted on 2011-04-21
Medium Priority
Last Modified: 2014-09-14
I am running MS SQL Mgt 2008 SP1.  Have a linked server in a stored procedure that I insert records into.  When I run each insert statement alone outside of the Stored Procedure including the Linked Server, the insert works fine.  When I try to execute the Stored Procedure, I get the following error:

Error...OLE DB provider "SQLNCLI10" for linked server "cis" returned message "No transaction is active.".

Any ideas what is wrong here?

Question by:Bodhi108
  • 5
  • 3
LVL 17

Expert Comment

by:Carlos Villegas
ID: 35444942
Your MSDTC service is running?
LVL 17

Expert Comment

by:Carlos Villegas
ID: 35444958
Look for it at Administrative Tools > Services:

Author Comment

ID: 35445231
Yes, checked the above services and it is running on both machines (the linked server and the non-linked server).
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.


Author Comment

ID: 35445246
By the way I can run the stored procedure on the one that was the Linked Server and have the other server as the linked server but can't go the other way.

Server A and Server B

Insert into table on Linked Server (A)
  from Server (B)

Doesn't work from SP but works if I run SQL inserts statements separately.
Insert into Linked Server (B)
  From Server (A)

SP works fine.  
Just can't run it from other server within SP...
LVL 17

Expert Comment

by:Carlos Villegas
ID: 35445278
Hello, can you check if the linked server properties are the same in both servers?
LVL 21

Expert Comment

by:Alpesh Patel
ID: 35446092
Please make sure the SP you are creating has permission to link server. MDTC is enable. etc.

Author Comment

ID: 35450559
The Linked Server Properties must not be a problem because I can run the insert statement with the Linked Server outside of the Stored Procedure.

Attached are the Services which shows MDTC seems to be running.  Not sure about permission to link server from the SP.  How would I grant permission to the link server?

Forgot to mention...
--the remote server is SQL Server 2005 and the server the SP is on is SQL Server 2008.  
--I can do a select stmt in the SP to the Linked Server but not an insert!

Accepted Solution

Bodhi108 earned 0 total points
ID: 40313053
Never received a solution so just closing this out.

Author Closing Comment

ID: 40321498
never received a solution so just closing this out.

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
Screencast - Getting to Know the Pipeline

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question