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

x
?
Solved

Errors on Stored Procedure with Linked Server...

Posted on 2011-04-21
9
Medium Priority
?
122 Views
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?

Thanks!
0
Comment
Question by:Bodhi108
  • 5
  • 3
9 Comments
 
LVL 17

Expert Comment

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

Expert Comment

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

Author Comment

by:Bodhi108
ID: 35445231
Yes, checked the above services and it is running on both machines (the linked server and the non-linked server).
0
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

by:Bodhi108
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...
0
 
LVL 17

Expert Comment

by:Carlos Villegas
ID: 35445278
Hello, can you check if the linked server properties are the same in both servers?
0
 
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.
0
 

Author Comment

by:Bodhi108
ID: 35450559
yv989c:
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.

Patel:
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!
Services.docx
0
 

Accepted Solution

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

Author Closing Comment

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

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