?
Solved

Export data with an insert trigger to a linked server

Posted on 2016-11-17
20
Medium Priority
?
89 Views
Last Modified: 2016-12-28
Hi,

I have the following query I use to update an Informix database which is a linked server on my Sql server:
INSERT OPENQUERY(ProntoTestServer, 'Select item_description, tara_extended_descr, tara_supplier_item_code, tara_brand, tara_uom, tara_qty_ordered, tara_supplier, tara_sub_line from TestDatabase:Quotes0')
 select item_description, tara_extended_descr, tara_supplier_item_code, tara_brand, tara_uom, tara_qty_ordered, tara_supplier,                         tara_sub_line from test.dbo.quotes2

This query works, but I need to automate this process for every time a new record is added to the quotes2 table. How can I do this by using an insert trigger?
0
Comment
Question by:cErasmus
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 8
  • 4
20 Comments
 
LVL 51

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 1000 total points
ID: 41891169
Just create the necessary trigger and use the inserted table (is where the new rows are). Like this will work even for massive inserts:
 
CREATE TRIGGER trgInsertInformix ON dbo.quotes2  
AFTER INSERT 
AS
INSERT OPENQUERY(ProntoTestServer, 'Select item_description, tara_extended_descr, tara_supplier_item_code, tara_brand, tara_uom, tara_qty_ordered, tara_supplier, tara_sub_line from TestDatabase:Quotes0')
SELECT item_description, tara_extended_descr, tara_supplier_item_code, tara_brand, tara_uom, tara_qty_ordered, tara_supplier, tara_sub_line 
FROM inserted
GO

Open in new window

0
 
LVL 29

Assisted Solution

by:Pawan Kumar
Pawan Kumar earned 1000 total points
ID: 41891170
Try...

CREATE TRIGGER [dbo].[TriggerName] ON [dbo].[TableName] AFTER INSERT
AS 
BEGIN
    SET NOCOUNT ON
	
    IF EXISTS (SELECT TOP 1 1 FROM INSERTED)
    BEGIN
--Perform your operation here..

INSERT OPENQUERY(ProntoTestServer, 'Select item_description, tara_extended_descr, tara_supplier_item_code, tara_brand, tara_uom, tara_qty_ordered, tara_supplier, tara_sub_line from TestDatabase:Quotes0')  select item_description, tara_extended_descr, tara_supplier_item_code, tara_brand, tara_uom, tara_qty_ordered, tara_supplier,                        
 tara_sub_line from Inserted.

		
    END
	
END

Open in new window



Hope it helps !!
0
 
LVL 1

Author Comment

by:cErasmus
ID: 41891269
Hi Guys,

Thanks for the assistance.

Vitor, I have created the trigger as per your solution, but once I import data into the quotes2 table it is not automatically triggered to export the data, is there somewhere I can check what error was caused by the trigger?
0
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 
LVL 29

Expert Comment

by:Pawan Kumar
ID: 41891276
Insert a single row and then chk.
0
 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 41891280
but once I import data into the quotes2 table it is not automatically triggered to export the data
How did you import the data?
0
 
LVL 1

Author Comment

by:cErasmus
ID: 41891286
I have a SSIS package that imports the data
0
 
LVL 1

Author Comment

by:cErasmus
ID: 41891291
But in my final solution the quotes2 table's data will be generated from an excel spreadsheet....
0
 
LVL 29

Expert Comment

by:Pawan Kumar
ID: 41891293
Trigger works row by row . Insert one row manually and the try.
0
 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 41891297
Trigger works row by row . Insert one row manually and the try.
That's not true.
0
 
LVL 51

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 1000 total points
ID: 41891301
I have a SSIS package that imports the data
That's using bcp or BULK INSERT command? Because if it does then triggers aren't fired during the process.
0
 
LVL 1

Author Comment

by:cErasmus
ID: 41891311
I have tested by inserting a row manually and I get this error:
MSDTC is not available on this server.

In SSIS I use a dataflow task with an OLE DB Source and a OLE DB Destination. Vidor, if you said "That's using bcp or BULK INSERT command". So for SSIS packages import of data will not fire triggers?
0
 
LVL 51

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 1000 total points
ID: 41891314
No, a Data Flow Task does not use bulk insert. For that you have Bulk Insert Task.

MSDTC is not available on this server.
You'll need MSDTC since it's the component that allows you to run distributed transactions in remote servers.
0
 
LVL 1

Author Comment

by:cErasmus
ID: 41891343
Hi Vitor,

I have started the MSDTC service and I get the following error:

The operation could not be preformed because OLE DB provider "Ifxoledbc" for linked server "ProntoTest" was unable to begin a distributed transaction.
0
 
LVL 51

Accepted Solution

by:
Vitor Montalvão earned 1000 total points
ID: 41891353
Did you Enabled XA transactions in the MSDTC?
0
 
LVL 1

Author Comment

by:cErasmus
ID: 41891380
Hi Vitor, thanks that did the trick!
0
 
LVL 1

Author Closing Comment

by:cErasmus
ID: 41891383
THanks for the help, you guys rock!
0
 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 41891411
I really do not agree with the points split since Pawan's comment was mostly a copy from mine first comment after he edited it when he saw what's missing (moderators can easily detect this). Also he was trying to get you to the wrong path. If I weren't here you'ld still around this issue.
Cheers
0
 
LVL 29

Expert Comment

by:Pawan Kumar
ID: 41891422
No points please for this. Not contributed enough.

@vitor - did not agree with you. My code is very different from you.
0
 
LVL 1

Author Comment

by:cErasmus
ID: 41891436
Sorry about this Vitor, can the moderators please award all points to Vitor Montalvão.
0
 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 41891442
My code is very different from you.
Only thing different is this useless part "IF EXISTS (SELECT TOP 1 1 FROM INSERTED)". It's useless because it's an insert trigger so at least a row will exist in INSERTED.

Sorry about this Vitor, can the moderators please award all points to Vitor Montalvão
Not really your fault but I'm tired to see questions that I've solved hijacked by the same guy. You can fill a request for a moderator in your main question by clicking in the three dots button (...).

Cheers
0

Featured Post

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question