Error accessing iSeries from SQL Server

[arrow483]

I have an SQL server that accesses the i5 as a linked server. It worked fine until this weekend when we did an IPL and posted a number of HIPER and DB2 PTF's. I now get this message:

OLE DB provider 'IBMDA400' reported an error.
[OLE/DB provider returned message: CPF4326: Commitment definition *N not valid for open of CALLID.
]

The error seems to relate to commit processing, but this file does not use commit, nor is it even journalled. I can access from Navigator and STRSQL with no problems, but not from SQL Server OLE

Any suggestions? (I will call IBM, but you guys are usually much faster)

*** Update: I tried connecting to another LPAR that does not have the new PTF's, same error. Windows update ? ***

[littlepd]

Re: Error accessing iSeries from SQL Server

It would seem to me that your OLE DB Provider is now out of sync with the server. Try loading the latest iSeries Access for Windows service pack (at least the OLE DB Provider) and try it again.

[arrow483]

Re: Error accessing iSeries from SQL Server

Thanks. I already tried that. No change. It seems to point to scoping on commit control request from SQL. Why, I don't know - this is a simple select with no commit referenced. I know how to change these kind of ODBC settings, but I have no idea where to control settings for OLE.

[littlepd]

Re: Error accessing iSeries from SQL Server

Arrow,

The attached code snippet is designed to be run from an Excel VBA macro, but you could convert the parts that provide the output to just about anything.

Public Sub ListConnectionProperties()

Dim strVersionInfo As String
Dim strCaption As String
Dim connprop As ADODB.Property
Dim rsprop As ADODB.Property

    ' Ensure ADO connection is open
    If Not gblnIsConnected Then
        If Not OpenConnection Then GoTo exit_sub
    End If

    strVersionInfo = "ADO Version: " & gadoConnection.Version & vbCrLf
    strVersionInfo = strVersionInfo & "DBMS Name: " & gadoConnection.Properties("DBMS Name") & vbCrLf
    strVersionInfo = strVersionInfo & "DBMS Version: " & gadoConnection.Properties("DBMS Version") & vbCrLf
    strVersionInfo = strVersionInfo & "OLE DB Version: " & gadoConnection.Properties("OLE DB Version") & vbCrLf
    strVersionInfo = strVersionInfo & "Provider Name: " & gadoConnection.Properties("Provider Name") & vbCrLf
    strVersionInfo = strVersionInfo & "Provider Version: " & gadoConnection.Properties("Provider Version") & vbCrLf

    strCaption = "ADO Version information:"
    MsgBox strVersionInfo, , strCaption

    strCaption = "Connection Properties:"
    strVersionInfo = ""
    For Each connprop In gadoConnection.Properties
        strVersionInfo = strVersionInfo & connprop.Name & ": " & connprop & vbCrLf
    Next
    MsgBox strVersionInfo, , strCaption

    strCaption = "Recordset Properties:"
    strVersionInfo = ""
    Set gadoRecordset = New ADODB.Recordset
    With gadoRecordset
        .ActiveConnection = gadoConnection
        .Source = "Select * From " & Trim(gstrSchema) & ".SUBDVN"
        .Open
    End With
    For Each rsprop In gadoRecordset.Properties
        strVersionInfo = strVersionInfo & rsprop.Name & ": " & rsprop & vbCrLf
    Next
    MsgBox strVersionInfo, , strCaption
    
    gadoRecordset.Close
    Set gadoRecordset = Nothing
    
    gadoConnection.Close
    Set gadoConnection = Nothing

exit_sub:
End Sub

What this does is retrieve the parameters collections from the ADO connection and recordset objects, and displays them in a dialog box. These properties are different from one version of the OLE DB provider, and from one provider manufacturer, to the next.

Run this and take a look at the results to see if you can find a property having to do with transaction processing, and we'll go from there.