• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1028
  • Last Modified:

Getting Error When Inserting Data in Linked DB2 server from a Linked Server

Hi,

I created a update trigger on a sql table  in my sql server to insert records in a  linked server DB2 table.   Insert Query works fines when I run it separately. However it doesn't work when I run it in the trigger.
However I get an error below

'The operation could not be performed because OLE DB provider "IBMDASQL" for linked server "AS400" was unable to begin a distributed transaction.'
0
mebster82
Asked:
mebster82
  • 2
  • 2
1 Solution
 
Steve WalesSenior Database AdministratorCommented:
By default a distributed transaction (updates/inserts/deletes on both sides) don't work over a linked server.

I haven't had to work with DB2 but I found it to be true with Oracle.

I did find this: http://blogs.msdn.com/b/sqlforum/archive/2010/12/20/faq-how-do-i-configure-sql-server-to-enable-distributed-transactions-via-linked-server.aspx

However I do not know if that will work with DB2 - I haven't touched DB2 since the early 90's.
0
 
mebster82Author Commented:
I can insert records fine when I execute the stored procedure in the regular query window However,  it give me an error when I am calling the stored procedure in a trigger.
0
 
Steve WalesSenior Database AdministratorCommented:
We're a little outside my area of expertise now, but I did find this after a search:

http://www.sqlvillage.com/Articles/Distributed%20Transaction%20Issue%20for%20Linked%20Server%20in%20SQL%20Server%202008.asp

There were several other article I found where it mentioned correct configuration of the Distributed Transaction Coordinator.

Have you verified that it's set up correctly?

There may also be transaction implications when running it in a trigger as opposed to doing an insert in SSMS.
0
 
Kent OlsenData Warehouse Architect / DBACommented:
Make sure that the stored procedure is in the corrected schema.

Also check the ownership of the stored procedure.

Check to make sure that the stored procedure is accessing the tables that you think it should.  That is, qualify the table names.
0
 
mebster82Author Commented:
You are right. Distibuted transaction such as insert won't be supported in linked AS400 environmentl.  Work around is to create a AS400 stored procedure and call that  As400 stored procedure inside the trigger.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now