We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Errors on Stored Procedure with Linked Server...

Bodhi108
Bodhi108 asked
on
Medium Priority
140 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!
Comment
Watch Question

Carlos VillegasFull Stack .NET Developer

Commented:
Your MSDTC service is running?
Carlos VillegasFull Stack .NET Developer

Commented:
Look for it at Administrative Tools > Services:
 Services

Author

Commented:
Yes, checked the above services and it is running on both machines (the linked server and the non-linked server).

Author

Commented:
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...
Carlos VillegasFull Stack .NET Developer

Commented:
Hello, can you check if the linked server properties are the same in both servers?
Alpesh PatelSolution Architect at TCS (SAFe®Agilist, TOGAF® Foundation, PSM I®, MCP, MCSD)
CERTIFIED EXPERT

Commented:
Please make sure the SP you are creating has permission to link server. MDTC is enable. etc.

Author

Commented:
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
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
never received a solution so just closing this out.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.