Solved

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

Posted on 2013-01-18
5
970 Views
Last Modified: 2013-02-08
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
Comment
Question by:mebster82
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 22

Accepted Solution

by:
Steve Wales earned 500 total points
ID: 38794839
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
 

Author Comment

by:mebster82
ID: 38802212
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
 
LVL 22

Expert Comment

by:Steve Wales
ID: 38802297
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
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 38802738
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
 

Author Closing Comment

by:mebster82
ID: 38869474
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

Veeam gives away 10 full conference passes

Veeam is a VMworld 2017 US & Europe Platinum Sponsor. Enter the raffle to get the full conference pass. Pass includes the admission to all general and breakout sessions, VMware Hands-On Labs, Solutions Exchange, exclusive giveaways and the great VMworld Customer Appreciation Part

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

630 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question