Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Jyo
Starting Member
3 Posts |
 Posted - 2012-02-21 : 16:59:42
|
| Hello,I need some help with one of the issue I am facing while using linked server. I have a TS box where I am the admin and I am firing a SP (in the form of job) onto another server which I added as 'Linked Server'. I am able to add and connect to linked server, query the tables that are there and also insert records. However, if I use a stored proc ith 'Begin Tran' and 'Commit Tran' in that, it throws the below error:-OLE DB provider "SQLNCLI" for linked server "<servername>" returned message "No transaction is active.". I see that only when we apply the begin and commit tran, it doesnt allow to insert records. But if we do a plain insert, it does. I restarted the distributor transaction coordinator as well. No luck. Can anyone please let me know how to resolve this with using Begin and Commit tran. Is there any setting available so I can proceed?Thanks in advance for your help!Regards,Jyothi |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-02-21 : 17:19:52
|
| you should be using distributed transaction for that------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
Jyo
Starting Member
3 Posts |
Posted - 2012-02-22 : 13:49:59
|
Thanks for the reply. I however added the Distributed Transaction in my Stored Proc but I received the below error:The operation could not be performed because OLE DB provider "SQLNCLI10" for linked server "<servername>" was unable to begin a distributed transaction.Please can you let me know if we need to do additional settings as well.Thanks & Regards,quote:
Originally posted by visakh16
you should be using distributed transaction for that------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
Jyothi |
|
|
|
|
|
|
Issue with Linked Server