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.
Together, we can make wonders!