Triggers
-
I don't know if I'm posting this in the correct dicussion or not, but here goes. I have a SQL 2000 database that gets data inserted. When an insert occurs I want to trigger an action to a non SQL database (right now Access but probably DB2 later).
I set up a Linked server for the the Access database and wrote an insert trigger. I'm getting a message back 'Microsoft.Jet.OLEDB.4.0 does not support the required transaction interface'. Can I trigger an action to an Access database or any non SQL database? Am I taking the right approach?
Terrie
-
First question that has to be asked : Does this all have to be in the same transaction-scope ? (on-line/realtime). Keep in mind, if the trigger fails, your sql-transaction also fails. If your trigger gets stuck, your database also gets under presure !
if not : build an asynchronus michanism. e.g. a sqlagent job that can be launched using an alert (that can be fired from the insert trigger) or scheduled every x minutes. That job could then execute any program you want to, to perform your wanted actions. This way you'll have an on-line/near time solution.
Regarding your jet-message : are us using the latest version/sp of the Jet-engine ? You'll have to download and implement it by yourself, because it nolonger is in the mdac-updates
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Yes the transactions are real-time, therefore a trigger. Estimates are about 3000 inserts per day. I don't have a choice about the Jet-engine. But this will probably end up in DB2. My question is more generic. Can I trigger an action in a non-Sql database from Sql Server?
Terrie
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply