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 |
|
dmctiernan04
Starting Member
2 Posts |
 Posted - 2009-02-23 : 20:59:06
|
| Hi All,I have been racking my brains on this issue for a few days now. I hope someone can help.I have set up a linked server in SQL Server 2005 to a MS Access file using Jet 4.0 . My goal is to automatically insert a single row of data into the an Access table from my SQL Server 2005 database. I created a SP and it works for the insert operation however I want this SP to be executed whenever I insert a row of data into my SQL Server table. I created a trigger to execute the SP when data is inserted into this SQL Server table. However when the trigger fires I always get an error. See as follows:Msg 7390, Level 16, State 2, Procedure trTimewareInsert, Line 17The requested operation could not be performed because OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "TIMEWARE" does not support the required transaction interface.I have tried inserting the SP's script directly into the trigger as follows:INSERT OPENQUERY([TIMEWARE], 'SELECT JobNumber, JobDescription FROM [Jobs]')VALUES(@JobNumber,@JobDescription)This also produces the same error.I hope someone can shed some light on this for me. It sounds like the trigger's code is being wrapped in a transaction which is unsupported in Jet 4.0/Access. If this is the case, I truly hope someone can offer some advise on a possible workaround. Thanks in advance,Declan.p.s. - sorry for the long post. |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-02-23 : 21:02:02
|
| There is no automatic process for this one. You need to create SSIS package like (IF data Exists-update otherwise -Insert) and then schedule with jobs . |
 |
|
|
dmctiernan04
Starting Member
2 Posts |
Posted - 2009-02-23 : 21:16:12
|
| Thanks sodeep for the prompt reply. I had a feeling it wouldn't be as straight forward as I had first anticipated. I will look into creating an SSIS package as you mentioned. |
|
|
|
|
|
|
Linked Server (MS Access 2003)