linked server in trigger

Question

linked server in trigger

Hendrianno Irawansky

SSC Enthusiast

Points: 183
More actions
August 25, 2010 at 12:40 am

#146695

i have trigger in a table, when i insert data to the table, trigger execute insert query to the different server,DB,and table.
but the trigger give me an error, whereas if the syntax in trigger i run in windows query, data smoothly
insert into target server,DB,and table..
does linked server can not execute via trigger??
any idea,please???
error:
OLE DB provider "SQLNCLI" for linked server "10.1.0.13" returned message "No transaction is active.".
Msg 7391, Level 16, State 2, Procedure TrgInsertTblExpedition, Line 25
The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "10.1.0.13" was unable to begin a distributed transaction.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

i believe the error you are seeing is because the service MSDTC (Distributed Transaction Coordinator)

must be running on both servers in order to support the cross instance transaction; you'll need to simply make the service started, and automatically start in order to get it to work.

Distributed Transaction Coordinator
Coordinates transactions that span multiple resource managers, such as databases, message queues, and file systems. If this service is stopped, these transactions will fail. If this service is disabled, any services that explicitly depend on it will fail to start.

separate from that, i would try to avoid doing this in a trigger , simply because if the transaction fails for any reason(DTC service not up,network connectivity,server itself is down) the trigger rolls back and you lose the data that was being inserted; I usually create a job instead which inserts the data that is not in the remote server yet; that way if the job fails, a subsequent pass of the same job can pickup where the last pass failed.

you can put the data in a local table, and then have a job insert that to the remote server as well instead; when the job is complete, it deletes the local table contents so you don't have to compare large tables between servers.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Hendrianno Irawansky SSC Enthusiast Points: 183 More actions · Answer 2

Mr. Lowell

i did, i have setting DTC on both server

-i have network DTC access checked

-allow remote client,administrator checked

-allow inbound checked

it's drive me crazy...

btw, thx for your idea,,

but i need quick data to insert, thats why i use trigger..

if i use job, of course i should setting schedule-->daily frequency -->occurs every = 2 or 3 minutes

hmm.. however i'll try it..

thx...

Andrew G SSChampion Points: 12809 More actions · Answer 3

Could also be the security settings on the linked server. Try getting the trigger to run under a specified user context by using:

EXECUTE AS LOGIN = 'blah'

UPDATE table (cause trigger to fire)

Hendrianno Irawansky SSC Enthusiast Points: 183 More actions · Answer 4

like this??

ALTER TRIGGER [TrgInsertTblExpedition] ON [dbo].[T_StockGudang]

FOR INSERT

AS

declare @tes(20)

select @tes = tes from inserted

begin

EXECUTE AS LOGIN = 'sa'

insert into [10.1.0.13].gudang.dbo.TblExpedition (tes)

VALUES(@tes)

end

it's not work, fox..