I'm have a linked server set up on my MSSQL server to a MySQL server. I can update the MySQL tables all day long in Query Analyzer. However using the same openquery function in a update trigger on one of my MSSQL tables it causes an error when I try to update a record.
The error I get is:
"Another user has modified the contents of this table or view; the database row you are modifying no longer exists in the database. Database error: '[Microsoft][ODBC SQL Server Driver][SQL Server]MSDTC on server 'MyServerName' is unavailable."
Here is my Trigger:
CREATE TRIGGER bpk_update ON [dbo].[bpks]
FOR UPDATE
AS
UPDATE openquery(ITSC_Store_Search,'SELECT BPK_NAME FROM stores_new WHERE Autonumber=16') SET BPK_NAME = 'MYNEWVALUE'
GO
I'm in a clustered environment and made sure that MSDTC is started on my SQL servers. What am I doing wrong? Please Help!