Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
flyrplnz
Starting Member
6 Posts |
 Posted - 2005-11-11 : 14:56:46
|
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 ASUPDATE openquery(ITSC_Store_Search,'SELECT BPK_NAME FROM stores_new WHERE Autonumber=16') SET BPK_NAME = 'MYNEWVALUE'GOI'm in a clustered environment and made sure that MSDTC is started on my SQL servers. What am I doing wrong? Please Help! |
|
|
coolerbob
Aged Yak Warrior
841 Posts |
Posted - 2005-11-11 : 15:05:23
|
| hmmm, never done triggers before, never had the need.but u say u have a linked server. instead of using openquery, have you tried the "mylinkedserver.mydatabase.dbo.mytable" syntax instead?what are you trying to accomplish? |
 |
|
|
flyrplnz
Starting Member
6 Posts |
Posted - 2005-11-11 : 16:04:08
|
| I need to auto update a field in another departments database when we update ours. Unfortunately we are on different DB platforms. |
|
|
|
coolerbob
Aged Yak Warrior
841 Posts |
Posted - 2005-11-12 : 10:21:53
|
| does it have to be done instantly or can you have a scheduled task that does it every hour/day? |
|
|
|
flyrplnz
Starting Member
6 Posts |
Posted - 2005-11-14 : 08:41:18
|
| I'd rather it be intantly. However I thought about doing a nightly synch or something if this won't work. I have a feeling that MySQL does not support DTC and that is my problem. What I wish is I could have the trigger try to do the update without verifying that it took on the MySQL database (no DTC) and if it failed well just ignore the error and go on about business. Then also set up a nightly sync to catch everything. I really don't won't my database dependant on the MySQL database to update my own records. What I'm doing it just a favor for the other DB.What I need is like a "on error resume next" in my trigger but I don't know how to do that in Transact-SQL. |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-11-14 : 08:43:57
|
| >>What I need is like a "on error resume next" in my trigger but I don't know how to do that in Transact-SQL.See if these are helpfulhttp://www.sommarskog.se/error-handling-I.htmlhttp://www.sommarskog.se/error-handling-II.htmlMadhivananFailing to plan is Planning to fail |
|
|
|
flyrplnz
Starting Member
6 Posts |
Posted - 2005-11-14 : 12:30:00
|
| I've tried SET XACT_ABORT OFF but I still get the same error message and it will not allow me to change a record value in my own db table. |
|
|
|
|
|
|
MSDTC unavailable when using openquery in trigger