distributed query inside of a trigger

Question

Post reply

distributed query inside of a trigger

Fred Wahlquist

SSC Eights!

Points: 874
More actions
December 20, 2004 at 11:56 am

#60577

My end goal is to be able to transfer data from our test station to a central server. I created a linked server on the test station to the central server (HF-DB1) using Enterprise Manager. This query works fine to copy rows from the test station to the central.
insert into "HF-DB1.HF.LOCAL".KATECentral.dbo.systemevents
select * from systemevents
I used this identical query inside a trigger as follows. The trigger was successfully created.
[for the moment ignore the fact that this query would be written differently using the inserted table. I figured it should work just to demonstrate a distributed query inside a trigger.]
CREATE TRIGGER TRIG_CopySystemEventsToCentral
ON SystemEvents
AFTER INSERT
AS
insert into "HF-DB1.HF.LOCAL".KATECentral.dbo.systemevents
select * from systemevents
GO
As a test I inserted one row into the systemevents table and got the following error message.
Server: Msg 7391, Level 16, State 1, Procedure TRIG_CopySystemEventsToCentral, Line 5
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a].
Question: Does anyone know what I need to do to be able to execute a distributed query from withing a trigger?
Thanks for any help you can offer.
Fred

Viewing 10 posts - 1 through 9 (of 9 total)

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

sa24 SSCrazy Eights Points: 8027 More actions · Answer 1

Check the article below and verify MSDTC to be running. Also try testing using dtcping.exe

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q306843

Fred Wahlquist SSC Eights! Points: 874 More actions · Answer 2

I confirmed that the MS DTC service is running on both machines.

I downloaded the troubleshooting doc you mentioned. I ran dtcping in both directions and all messages indicated success. However, it took much longer for dtcping to run than I would have thought ( 30 seconds or more ).

Any other avenues to chase?

Thanks again.

Fred

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 3

Search BOL for "Distributed Queries and Distributed Transactions" - there's a lot of good info there on the subject.

I'm dubious though, if it's such a good idea to have distributed queries inside triggers. Gut feeling tells this could be more trouble than good.

/Kenneth

M. Laurie Smith SSC Enthusiast Points: 165 More actions · Answer 4

inserting into a remote table from a trigger can be a very bad idea. It ties the local insert with the availability of another server. I don't think I have ever seen an application, where this sort of limitation would be appropriate. We would use the trigger to write to a local table, and then set up DTS or replication to implement the distributed portion.

noeld SSC Guru Points: 96590 More actions · Answer 5

To start, let me make clear that I follow mlsmith advice and that, is the RIGHT way to do it but for the fun of it

I beleive that the piece that is missing on your statement is:

insert into "HF-DB1.HF.LOCAL".KATECentral.dbo.systemevents (fld1,fld2,...)

select fld1,fld2,... from systemevents

It is a BAD practice to rely in implicit Field list order you should always type that EXPLICITLY

HTH

* Noel

Fred Wahlquist SSC Eights! Points: 874 More actions · Answer 6

Thanks to all who have replied thus far. I am abandoning the idea of a distributed query inside a trigger. I figured it was probably not the best method but wanted to try it out to learn something.

Our test systems will have MSDE on them and the documentation says transactional replication isn't supported on MSDE which is really the only viable form of replication for this situation, as I understand it. We really need to just push the data from the test system to the central system.

noeld SSC Guru Points: 96590 More actions · Answer 7

do you really need immediate update of the central system?

if not, Merge replication, which by the way is supported by MSDE, is the tool of option but if the it is too intrusive and you want more control over it you can ecapsulate the distributed query in an sp and use a job to periodically execute it. On the other hand if you need something immediate you may try a message queue (to compensate for possible disconected clients) but it will require probably a lot af changes to your app.

HTH

* Noel

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 8

Is this 'central system' a production system? If it is, then don't try to connect test with production in any sort of way! Test and Prod never ever should have any dependencies between them.

If it's not like this, then just disregard my fears

/Kenneth

Fred Wahlquist SSC Eights! Points: 874 More actions · Answer 9

The central system is dedicated to support the test systems so that isn't an issue (ie. it's not used for any production related tasks).

We've decided to go with a stored procedure to transfer data to the central after a test is finished. If anything is needed "real time" we'll go with MSMQ messages.

Thanks to everyone for their help. I'm moving now.