Insert into table on linked server from trigger produce error

Question

Insert into table on linked server from trigger produce error

Igor Polishchuk

SSC Veteran

Points: 246
More actions
November 26, 2008 at 6:39 am

#125233

Hi,
We have an old application that inserts data into AS400 linked server from trigger on ms sql server 7. MS SQL 7 uses 3rd part driver("HiT Software") to connect to the AS400 and everything is work OK. We need to move the database to the MS SQL 2005 and switch from existing driver to IBM OLE DB driver. Bellow insert is working fine from QA and SP but failing from the trigger.
insert into OPENQUERY(TEST_V6R1, 'SELECT TRID30,STID30,ITNO30,ITRV30,ITDS30,ITYP30,INVF30,UMST30,ITAC30,UUCA30,EGNO30,RTID30 FROM MTDDATAF.OITMRVA0# where 1=0')
values ('111','','222', '', '333', '1', 1, 'EA', '444', '555', '666', '777');
The requested operation could not be performed because OLE DB provider "IBMDA400" for linked server "TESTCRP" does not support the required transaction interface.
Can someone help me with this?
Thanks,
Igor.

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

I would recommend against doing this in a trigger anyway. The trigger takes place within the original transaction so if the trigger fails your entire transaction will roll back.

Have you tried using any IBM forums for this?

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Igor Polishchuk SSC Veteran Points: 246 More actions · Answer 2

Hi Jack,

I wouldn't do this also, but this is a very old application that uses sql 7 and our developers don't have source code for this. I checked IBM forums and couldn't find anything.

Thanks,

Igor.

Igor Polishchuk SSC Veteran Points: 246 More actions · Answer 3

I changed the driver from IBMDA400 to IBMDASQL and received bellow error. It's still working from QA and SP.

Msg 7391, Level 16, State 2, Procedure TEST_AS400V6R1, Line 13

The operation could not be performed because OLE DB provider "IBMDASQL" for linked server "TEST_V6R1SQL" was unable to begin a distributed transaction.