August 12, 2008 at 3:40 am
Hi there, i need to append record to a dbf table from a sql table's insert trigger. I had tried 2 ways to make things work however, i m stuck and could not get it right.
1) I code link server in the insert trigger like this,
Create TRIGGER iTR_Insert_SQLTable ON SQLTable
AFTER INSERT
AS
BEGIN
--Do other job first
...
...
EXEC sp_addlinkedserver
@server = 'MyDBF',
@srvproduct = 'xyzDBF',
@provider = 'VFPOLEDB.1',
@datasrc = 'D:\Temp\VFP\data\',
@location = '', @catalog = ''--values to insert
Insert into MyDBF...country (Code, CountryName)
values (ltrim(rtrim(@Code)), ltrim(rtrim(@cName)) )
END
PROBLEM:
The trigger return error after in tried to insert a record to my sql table
The procedure 'sys.sp_addlinkedserver' cannot be executed within a transaction.
2) I create a stored procedure to make a linked server and then i change my trigger look like this
Alter TRIGGER iTR_Insert_SQLTable ON SQLTable
AFTER INSERT
AS
BEGIN
--Do other job first
...
...
Insert into MyDBF...country (Code, CountryName)
values (ltrim(rtrim(@Code)), ltrim(rtrim(@cName)) )
END
PROBLEM:
The trigger return error after in tried to insert a record to my sql table
The requested operation could not be performed because OLE DB provider "VFPOLEDB.1" for linked server "MyDBF" does not support the required transaction interface.
.
Can any pro advice where did I do wrong?
Thank you.
February 28, 2009 at 1:28 am
HI There
Have you found a way to fix this yet? I am trying the same thing and running into a wall.Seems like you cannot a linkedserver via a trigger.
ANyone had success on this?
Regards
Neil
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply