"No transaction is active | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

"No transaction is active

I have requriment of update data in a linkedserver SQL Server from a Trigger.
How Ever, I am getting follwing error when I tried it.
Update can be done from simple query and it is failing only in Trigger. OLE DB provider "SQLNCLI" for linked server SOURCESERVER returned message "No transaction is active.".
Msg 7391, Level 16, State 2, Procedure TR_U_DimClient_sanj, Line 32
The operation could not be performed because OLE DB provider "SQLNCLI" for linked server SOURCESERVER was unable to begin a distributed transaction. I have set XACT_ABORT ON and REOMTE PROCEDURE ON also MSDTC is also running. HOSTS file is also updated.
both servers named servers. Is that can be the problem
—————————————-
Contributing Editor, Writer & Forums Moderator
http://www.SQL-Server-Performance.Com Visit my Blog at
http://dineshasanka.spaces.live.com/
Hi Dinesh,
I had read few times back that if we run any query inside transaction involving linked server, then it fails with above error message,
so workaround is to run those operations on linked servers outside any transactions.
So you need to make sure that those queries are not running in any transaction. I think if you run them inside trigger than it will involve transaction and that’s the reason it will fail. Also in 2005 you can access virtual tables inside Stored procedures, so no need to write triggers. I Hope this info helps you in some manner.
KBAhttp://support.microsoft.com/kb/306212 fyi. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.
it says
Check the return value of the system function @@SERVERNAME on both servers. Verify whether the
return value matches the computer name of each server. If it does not match, you have to
rename the server. but @@SERVERNAME is not equal to machine name becos of named instance —————————————-
Contributing Editor, Writer & Forums Moderator
http://www.SQL-Server-Performance.Com Visit my Blog at
http://dineshasanka.spaces.live.com/
]]>