0

I'm trying to run a trigger that allows me to insert the inserted data on my local table to the linked server's table. This is what I did:

use [medb]

ALTER TRIGGER [dbo].[trigger1] ON [dbo].[tbl1]
AFTER INSERT
AS
BEGIN

    INSERT into openquery(DEV, 'tbl_remotetbl') select * from inserted
        END

but it is giving this error:

Cannot process the object "tbl_remotetbl". The OLE DB provider "MSDASQL" for linked server "DEV" indicates that either the object has no columns or the current user does not have permissions on that object.

What seems to be my problem? Note: I am using SQL Server 2008 R2

Tharif
  • 13,794
  • 9
  • 55
  • 77
marchemike
  • 3,179
  • 13
  • 53
  • 96

2 Answers2

0

Did you try running the command outside the trigger ? Did it work ? here is the syntax I'm using in my openquery:

INSERT INTO OPENQUERY(LinkedServer_Name,
'select remote_field_1,remote_field_2 from remote_Schema.remote_table')
select local_column1,local_column2
FROM local_Table

Now, with that being said, making this statement work inside a trigger, is something I couldn't do. Above statement woks perfectly when executed by it self. But once it is placed in a trigger, the entire transaction related to that trigger fails. I mean even the insert statement that fires the trigger does not go through, and the main table does not insert the data which was meant to be inserted in it.

Anas Dawood
  • 1
0

i ran into the same issue, and spent many hours trying to figure out how to make an openquery statement work inside update/insert/delete triggers, with no success....

so, here's an alternate solution, maybe this can fix your issue, this is in a scenario where i need to pass data from MSSQL to a MySQL DB.

first, create a holding table, so you can store temporary info that's inserted into a table that will only hold data that i need to pass to MySQL

create table holding_table (ID int, value2 int)

trigger will insert data to the holding table, instead of sending it directly to MySQL

ALTER TRIGGER [dbo].[temp_data_to_mysql] 
ON [dbo].[source_table] 
FOR insert
 AS
 BEGIN

    INSERT into holding_table (ID,Stock)select a, b from inserted

    END
  GO

after that, you can just create a task in the SQL server agent, so it can execute your stored procedure every N minutes.

hope it helps, im aware that this is a workaround, but after some investigation and testing, i was unable to make openquery work called within a trigger process..

Pablo Contreras
  • 558
  • 6
  • 11