April 20, 2009 at 10:36 am
Hi All,
I have a Stored Procedure which has below statement.
exec MyServer_linked.TestDB.dbo.USP_Core_Log_AddToAuditLog @system='HR Data Load', @eventString='updateTblContractorUser', @objectID=@peopleKey,
When I run my SP, i get the below error message.
OLE DB provider "SQLNCLI" for linked server "MyServer_linked" returned message "No transaction is active.".
Msg 7391, Level 16, State 2, Procedure Usp_HRLoad_User_tblContractorUser, Line 98
The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "MyServer_linked" was unable to begin a distributed transaction.
Note: SP was working fine 30 mins back and suddenly started giving issues in the linked server query. Please help me to fix this issue
Regards,
Suresh Arumugam
April 20, 2009 at 10:52 am
Did you add a BEGIN TRANSACTION somewhere?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
April 20, 2009 at 11:23 am
As Jeff was saying if you are using Transactions you will have to enabled MSDTC on the servers...
Thanks.
Mohit.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
April 20, 2009 at 11:43 am
Working with distributed transactions isn't always easy...
I suppose you're logging something to the remote server, so the real question is: do you really need a distributed transaction to put a log entry? It depends. I usually work with SQL Server linked to AS/400 and the provider fails quite often: if it fails I have to reboot the machine to kill all the orphaned processes, that is not a choice for a mission-critical application.
If you think you don't need the transaction, I would suggest calling the remote procedure after the COMMIT statement.
Regards
Gianluca
-- Gianluca Sartori
April 21, 2009 at 5:16 am
Hi All,
Thanks for your time & the replies.
I have tried restarting both the linked servers and their MS Distributed Transaction Coordinator services. It has fixed the issues and now I could see my linked server query running.
Thanks,
Suresh
Regards,
Suresh Arumugam
April 21, 2009 at 9:09 am
I am happy that you got it working - thanks for the feedback.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply