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 |
|
lordzoster
Starting Member
29 Posts |
 Posted - 2009-01-14 : 10:56:36
|
| Hallo,is it possible to run query against linked server from within triggers?I have a piece of t-sql running very very perfectly during tests: it fills some variables with values coming from a query against an ORACLE OLEDB linked server, and then uses those variables to do something.When i put it inside an AFTER trigger, into a MSSQL2000, i get an error like this: "cannot start a distributed transaction [OLE/DB Provider 'MSDAORA' ITransactionJoin::JoinTransaction returned 0x8004d01b]"If i replace the linked server query with fake values, the trigger runs smoothly.Hence the question... |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-01-14 : 11:03:59
|
Stop the madness!Why would you want to call a linked server in a trigger?If the linked server doesn't respond, your application is toast!All inserts/updates/deletes to that table will fail if linked server is down or not reachable.Shoot the person designing this. Have a job or any other out-of-process tool call the linked server and do the job for you.
E 12°55'05.63"N 56°04'39.26" |
 |
|
|
lordzoster
Starting Member
29 Posts |
Posted - 2009-01-14 : 11:15:58
|
quote:
Shoot the person designing this
actually i'm considering that option...but there's another part of me disagreeing from being (self-)shooted :)Let's say that this is the simplest design for what i need (DOH!), and that the linked server *must* always respond (highly critical application) even because it is in the local network and in the same room: if it doesn't respond, the enterprise is down and there's no need to run the trigger.Anyway thanks for the hint, i'm reconsidering the other way, which i structured over service broker and other reliable ways. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-01-14 : 12:36:12
|
| so what you need is to process something whenever you get the values from oracle linked server? |
|
|
|
|
|
|
query a linked server from within trigger