Hi all, we have very old VB6 applications connection to a Sybase database. Today they are running fine using the Open Client 12 drivers via ODBC connecting to a Syabse Ase 15 servers.
There is a plan on upgrading to the OpenClient 15 drivers and with that version I'm getting this error:
Run-time error '-2147467259(80004005)' Transaction cannot have multiple recordsets with this cursor type. Change the cursor type, commit the transaction, or close one of the recordsets.
As I Understand searching the interwebs the problem is in the Cursors, In the old drivers in the ODBC configuration manager there is a Performance Tab and we have the "Select Method" in cursor, but with the new drivers this tab is gone and all we got about cursors is a section in the General Tab named "Cursor Behavior", in there we have the option "Use Cursors" checked.
Here is a little code snippet where i can reproduce the problem, All help is apreciated.
Thanks.
Dim conObj As ADODB.Connection
Dim objRs As ADODB.Recordset
Dim objRs2 As ADODB.Recordset
Set conObj = New ADODB.Connection
conObj.ConnectionTimeout = 10
conObj.CommandTimeout = 5
conObj.Provider = "MSDASQL"
conObj.Open "DSN=cdbur32;UID=***;PWD=***;Database=dbsait;WSID=Test;APP=Test"
conObj.CursorLocation = adUseClient
conObj.BeginTrans
Set objRs = New ADODB.Recordset
Set objRs.ActiveConnection = conObj
objRs.Source = "select id_estatus_aplicacion from dbo.cat_sait_estatus_aplicaciones"
objRs.CursorType = ADODB.CursorTypeEnum.adOpenForwardOnly
objRs.LockType = ADODB.LockTypeEnum.adLockReadOnly
objRs.Open
objRs.MoveNext
Debug.Print objRs("id_estatus_aplicacion")
Set objRs2 = New ADODB.Recordset
Set objRs2.ActiveConnection = conObj
objRs2.Source = "select * from dbo.cat_sait_estatus_aplicaciones"
objRs2.CursorType = ADODB.CursorTypeEnum.adOpenForwardOnly
objRs2.LockType = ADODB.LockTypeEnum.adLockReadOnly
objRs2.Open 'error here
objRs.MoveNext
Debug.Print objRs("id_estatus_aplicacion")
conObj.RollbackTrans
objRs.Close
objRs2.Close
Set conObj = Nothing