MTS Transactions and AS400 OLEDB

Posted on 2003-02-25
Medium Priority
Last Modified: 2007-12-19

I have an MTS component which calls a program on the AS400 via IBMDA400 OLEDB. When I set the MTS component to "NoTransactions" the call works. When I set the MTS component to "RequiresTransaction", I get an error "No such interface supported 80004002"

So my question is: Does IBMDA400 OLEDB support MTS transactions? In the OLEDB TechRef it says the you can use ADO transactional functionality which suggests that AS400 OLEDB supports MTS/COM Transactions. Other IBM articles says that SQL Transactions are not supported. Niether of these answers my question.

Furthermore, I have read that the COMCAT.dll is the cause of the problem. I have checked my COMCAT.DLL and it is version 5.2195 so its fairly recent.

Any help would be appreciated.

Question by:stuartngo
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
LVL 16

Expert Comment

by:theo kouwenhoven
ID: 8033313
Never heard about this problem, I just cheked and all our PC's using COMCAT.DLL Version 5.0.
So if the dll shoult be teh problem, go back to 5.0 instead of 5.2

Let us know if it worked.


Author Comment

ID: 8055051
Actually, its doubtful that the IBMDA400 OLEDB supports MTS transactions - which is why we get the "interface not supported" error message.

BTW I'm not allowed to change the COMCAT.dll because it means changing the configuration of live servers.

Thanks anyway

ps Does anyone know how to close questions?

Expert Comment

ID: 8096795
I'm no expert but I'm currently working with some VB & AS400 related stuffs. This is what I've concluded :-

1). I can't control transactions from VB to AS400 using MTS.Therefore MTS does not handle AS400 transactions.

2). If u want to have transactional handling in VB, using the ADODB.Connection objects BeginTrans,CommitTrans & RollbackTrans does the trick.This works but u need to set the VB dll that u compiled to MTSTransactionMode : 3-UsesTransactions.

Hope this is works for u.
Appreciate ur feedback.


Author Comment

ID: 8117106
Hi shiyamala,

I tried your suggestion (2) but it didn't work - I still get the interface error. Where do you put the ADOCON.BeginTrans in the code - can you give me an example.


Accepted Solution

shiyamala earned 200 total points
ID: 8180500
Sorry for the extremely late response but firstly my version of Client Access Express is V4R5M0. Hope that ur using the same version here.
Below is some of my code for an ActiveX DLL in VB6:

Public Function AS400TranControl()

Dim ADOCON as New ADODB.Connection
Dim rstADO as  New ADODB.Recordset
Dim sConn as String
dim sSQLStmt as String

 'Uses ODBC connection

 ADOCON .ConnectionString = sConnStr
 ADOCON .BeginTrans

 sSQLStmt = "SELECT * FROM ..........

  rstADO .CursorLocation = adUseClient
  rstADO .CursorType = adOpenStatic
  rstADO .LockType = adLockBatchOptimistic
  rstADO .Source = pi_sSQLStmt
  rstADO .ActiveConnection = cn

  rstADO .Open

 Set rstADO .ActiveConnection = Nothing

 'Manipulate ur rst if u want...perform updates or add new

  Set rstADO = Nothing
  Set ADOCON = Nothing
  Exit Function
  Set rstADO = Nothing
  Set ADOCON = Nothing
End Function

ps....I haven't tested this code but....that's the way.....
      AND also remember to set MTSTransactionMode - UsesTransaction

Hope this gives u an idea....
Appreciate any feedbacks.



Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

New style of hardware planning for Microsoft Exchange server.
This article will show how Aten was able to supply easy management and control for Artear's video walls and wide range display configurations of their newsroom.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Suggested Courses

743 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