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 |
|
chuck.robertson
Starting Member
2 Posts |
 Posted - 2009-10-22 : 15:08:16
|
| I am fairly new to Linked Servers, but have been successful in adding my linked server (SQL Server with Informix). I have a trigger on SQL Server that is attempting to update a table within Informix. The trigger utilizes the EXECUTE statement so that I can use parameters in building the query.EXECUTE ('UPDATE EFIN SET efin_stat = ? WHERE EFIN = ?', 'D', '643163') AT rh1;When the trigger fires I get the following error:Msg 7390, Level 16, State 2, Procedure trInsertEFINRegr, Line 65The requested operation could not be performed because OLE DB provider "MSDASQL" for linked server "rh1" does not support the required transaction interface.What I don't understand is I can call the same exact statement from a Query window and it functions fine. Is there something I don't know about triggers that is causing this to not work? I also tried moving it into a stored procedure but it results it the same error.I also tried changing the EXECUTE statement to the following, and received the same error so I'm guessing it doesn't like the EXECUTE call in the trigger?EXECUTE ('UPDATE EFIN SET efin_stat = ''D'' WHERE EFIN = 643163') AT rh1;Can anyone give me some ideas to try? I have run out of ideas.Thank you. |
|
|
chuck.robertson
Starting Member
2 Posts |
Posted - 2009-10-22 : 18:39:17
|
| I found another way around this issue. Instead of having the trigger fire after the fact, I have made changes to the program that inserted directly into the table to call my new stored procedure instead. This seems to have alleviated the issue with the EXECUTE call, and now everything functions the way I anticipated.Thanks. |
 |
|
|
|
|
|
Trigger & Linked Server Question