SQL Server Performance Forum – Threads Archive

triggers do not work on linked servers

We have 2 SQL servers coupled via a linked server. We can update, delete, select etc. from the linked server but when we do this via a trigger is does not work. The PID gets blocking and blocks the table on which the trigger resides. The PID can not be killed. However if we execute the command in a SQL window there is no problem. The commands are calls to stored procedures on the remote machine.
Antother strange behaviour is that we can not select a row if it not exists with the 4 dots notation, it will not come back with the results. However with the openquery statements it is not a problem. I hope some-one has a solution while this is killing us.

May monitor the event with PROFILER for assessment. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Satya, SQL profiler does not show much. When I execute the following statement:
select top 10 * from rpphototestdb.rptestphoto.dbo.images Ther is no problem and I get the results back. Wheren executing: select * from rpphototestdb.rptestphoto.dbo.images
where naam = ‘2373F7F9-D0D4-44B0-B5FE-00066D8B74E0’ It hangs. The only thing I can find in SQL profiler is a OLEDB error:
‘OLE/DB Provider ‘SQLOLEDB’ IRowset::GatherStats returned 0x80004005: OLE DB provider SQLOLEDB returned 0x80004005 for OpenRowset during statistics gathering’ Further there is nothing.
Any Ideas?

Okay, here is what I did. I created a table on linked server, created another table on the linked server. Created a trigger on the first table to insert record in the second table whenever a record is inserted into the first table. Then I inserted the record using the following statement: INSERT INTO linkedserver.northwind.dbo.test1 values (1, ‘Raj’). When I executed it in QA, it inserted the record in the second table too. So I just could not replicate the problem you had. Also, I could use "NOT EXISTS" successfully using four dots notation. I don’t know why you are having those issues? Did anyone replicate the same thing? -Rajeev Lahoty

When I create an empty table, I don’t have a problem either. In this case the data comes from an existing table on the first db machine. We use the linked server to devide the load. In the table are NTEXT columns. I suspect these are giving the problem Marnix

Confirm level of service pack on SQL on both the servers.
Ensure to maintain similar level of MDAC version too. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Did you ever find a solution to this problem as we are experiencing a very similar sounding problem.

]]>