Sybase ODBC - ASE 12.5 ODBC bug?

Author

ASE 12.5 ODBC bug?

PhilipL

2005-09-16, 1:23 pm

We have code that uses 11.5 sybase client. When we upgrade
to 12.5 it seems that newer ODBC configuration does not
support multiple SQL transactions at one time. (inconsistent
record counts among others). Is there a known bug? Is there
a workaround?
Thanks.

Paul

2005-09-16, 1:23 pm

Hi Philip

Please be more specific and detailed - what exact version, and provide
sample of the problems. If you use Cursor mode on the driver it should
support more than one active stmt (transaction). However it is not clear
exactly what is going on. Also if you installed the ga of that driver you
might consider upgrading, but there are now 2 different Sybase ASE drivers,
an OEM version and Sybase version.

Here is a chart to help explain the differences:

For ODBC:

ODBC Driver Product Differences
OEM
ODBC Driver Name: Sybase ASE ODBC Driver
Latest Version String :
(Format) 12.5.1/P-EBF12378 ESD #7/05.00.0056 (B0040,
U0026)
Filename: syodase.dll
Directory: %SYBASE%\ODBC

SYBASE
ODBC Driver Name: ASE ODBC Driver
Latest Version String: 12.5.1.465 (OCS ESD#08 EBF 12531)
Filename: sybdrvodb.dll
Directory: %SYBASE%\DataAccess\
ODBC\dll

OLEDB

OLE DB Provider Product Differences
OEM
OLE DB Provider Name (Long) Sybase ASE OLE DB Provider
OLE DB Provider Name (Short) Sybase.ASEOLEDBProvider
Latest Version String (Format) 12.5.1/P-EBF12531 ESD #8/02.70.0050
Filename sydaase.dll
Directory %SYBASE%\OLEDB

SYBASE
OLEDB Provider Name (Long): ASE OLE DB Provider
OLEDB Provider Name (Short): ASEOLEDB
Latest Version String: 12.5.1.466 (OCS ESD#08 EBF 12531)
Filename: sybdrvoledb.dll
Directory: %SYBASE%\DataAccess\
OLEDB\dll

pv

<PhilipL> wrote in message news:432af6db.65e7.1681692777@sybase.com...
> We have code that uses 11.5 sybase client. When we upgrade
> to 12.5 it seems that newer ODBC configuration does not
> support multiple SQL transactions at one time. (inconsistent
> record counts among others). Is there a known bug? Is there
> a workaround?
> Thanks.

PhilipL

2005-09-16, 1:23 pm

Thanks. I hope this explains it:

-2147467259 Transaction cannot have multiple recordsets with
this cursor type. Change the cursor type, commit the
transaction, or close one of the recordsets.
-2147467259 Unspecified error

> Hi Philip
>
> Please be more specific and detailed - what exact version,
> and provide sample of the problems. If you use Cursor
> mode on the driver it should support more than one active
> stmt (transaction). However it is not clear exactly what
> is going on. Also if you installed the ga of that driver
> you might consider upgrading, but there are now 2
> different Sybase ASE drivers, an OEM version and Sybase
> version.
>
> Here is a chart to help explain the differences:
>
>
> For ODBC:
>
> ODBC Driver Product Differences
> OEM
> ODBC Driver Name: Sybase ASE ODBC Driver
> Latest Version String :
> (Format) 12.5.1/P-EBF12378 ESD
> #7/05.00.0056 (B0040, U0026)
> Filename: syodase.dll
> Directory: %SYBASE%\ODBC
>
>
> SYBASE
> ODBC Driver Name: ASE ODBC Driver
> Latest Version String: 12.5.1.465 (OCS ESD#08 EBF
> 12531) Filename: sybdrvodb.dll
> Directory:
> %SYBASE%\DataAccess\
ODBC\dll
>
>
>
> OLEDB
>
> OLE DB Provider Product Differences
> OEM
> OLE DB Provider Name (Long) Sybase ASE OLE DB Provider
> OLE DB Provider Name (Short) Sybase.ASEOLEDBProvider
> Latest Version String (Format) 12.5.1/P-EBF12531 ESD
> #8/02.70.0050 Filename sydaase.dll
> Directory %SYBASE%\OLEDB
>
>
> SYBASE
> OLEDB Provider Name (Long): ASE OLE DB Provider
> OLEDB Provider Name (Short): ASEOLEDB
> Latest Version String: 12.5.1.466 (OCS ESD#08 EBF
> 12531) Filename: sybdrvoledb.dll
> Directory: %SYBASE%\DataAccess\
OLEDB\dll
>
> pv
>
>
> <PhilipL> wrote in message
> record counts among others). Is there a known bug? Is
>
>

Paul

2005-09-19, 1:24 pm

Hi Philip -

What are the details on teh application? Is it VB? using ADO? I see that
happen sometimes with use of DTC and stored procedures using parameters.
The OEM driver does not support sprocs with parameters in the DTC
environment using ASE/DTM. Is this what is happening? Or is it something
else.

-pv

<PhilipL> wrote in message news:432b0014.7187.1681692777@sybase.com...[color=darkred]
> Thanks. I hope this explains it:
>
> -2147467259 Transaction cannot have multiple recordsets with
> this cursor type. Change the cursor type, commit the
> transaction, or close one of the recordsets.
> -2147467259 Unspecified error
>

CNemo

2005-11-21, 8:23 pm

Hi I have the same issue!

I use sp with parameters and it worked fine under OLE DB Provider. But my
manager forced me switch to ODBC.

And i started to get this message.

I've fount that Connection Begintrans is the problem.
When changed to Connection execute "begin tran" everything started work.

Paul

2005-11-21, 8:23 pm

Hi,

If necessary, you should report this to Sybase Technical Support

Connection.BeginTrans should issue the "begin tran" statement. Another
thing I am thinking about is this - are you all using SelectMethod = Direct?
This means no ASE cursors are used on select statements. This is because
without cursors the driver only supports one active Statement per
connection. I do not know if this is the situation you have since there is
no sample code posted. However, there is another Performance option,
Connection Cache, which defaults to 1. If you increase this it will enable
the driver to issue a new ASE connection if another active statement is
created, up to whatever that setting is set to.

-Paul

"CNemo" <mikeevteev@yahoo.com> wrote in message
news:438220fa$1@foru
ms-1-dub...
> Hi I have the same issue!
>
> I use sp with parameters and it worked fine under OLE DB Provider. But my
> manager forced me switch to ODBC.
>
> And i started to get this message.
>
> I've fount that Connection Begintrans is the problem.
> When changed to Connection execute "begin tran" everything started work.
>
>

Oliverio Díaz

2006-01-19, 3:23 am

I have a similar problem with begintrans.
When I issue a BeginTrans at the connection object I get the error
-2147467259. The exact description I get is in spanish "Las
transacciones no pueden tener múltiples conjuntos de registros con este
tipo de cursor. Cambie el tipo de cursor, confirme la transacción o
cierre uno de los conjuntos de registros." which translates to something
like "transactions can't have multiple record sets with this type of
cursor. Change the type of cursor, confirm he transaction or close one
of the record sets"

I tried the C.Execute "Begin tran" method initially, but recently
discovered that it was not working. Somehow the next call to ASE was
done in another connection that didn't knew about the transaction.

I checked the select method, Connection Cache, etc. Everything is ok.

This is the connection string:
Provider=MSDASQL. 1;Mode=ReadWrite;Ext
ended
Properties=" DSN=INTEGRA;UID=oliv
erio;PWD=password;Wo
rkstationId=ALDEBARA
N;ApplicationName=In
tegra;DB=integra"

This is the code:
C.BeginTrans
cjd_ssn = CAJA.InsertarTmp(SEG, tcIngVentas, VLEfectivo, tr_monto(1),
tr_monto(2), CCur(txtTotal))
If cjd_ssn = 0 Then
GoTo ROLLBACK
End If
....
....
C.CommitTrans
exit sub
ROLLBACK:
C.RollBackTrans

The error is in the call to the object CAJA. Internally the object
calls a stored procedure that writes to the table and returns the a
number as an output parameter.

Here is the call to the stored procedure:

Set coQuery = New ADODB.Command
coQuery.CommandType = adCmdStoredProc
coQuery.CommandText = ProcedimientoAlmacen
ado

' Here I create all the parameters
If Not fArmarParametros(Pro
cedimientoAlmacenado
, coQuery,
DebugDelExec) Then
Set coQuery = Nothing
GoTo SalirError
End If

' Here is the error
coQuery.ActiveConnection = mvarconnection

' Abrir el resultset
coQuery.Execute (FilasAfectadas)

The error is in the sentence where I set the active connection.

This is a big problem for me because I was trusting VB and ODBC to be
capable of controlling transactions. Now I have to change all my VB
objects to make them write everything to temporary tables and create a
new stored procedure to issue the begin trans command, do all the work
and commit the transaction.

Thanks in advance,
Oliverio

Paul wrote:
> Hi,
>
> If necessary, you should report this to Sybase Technical Support
>
> Connection.BeginTrans should issue the "begin tran" statement. Another
> thing I am thinking about is this - are you all using SelectMethod = Direct?
> This means no ASE cursors are used on select statements. This is because
> without cursors the driver only supports one active Statement per
> connection. I do not know if this is the situation you have since there is
> no sample code posted. However, there is another Performance option,
> Connection Cache, which defaults to 1. If you increase this it will enable
> the driver to issue a new ASE connection if another active statement is
> created, up to whatever that setting is set to.
>
> -Paul
>
>
> "CNemo" <mikeevteev@yahoo.com> wrote in message
> news:438220fa$1@foru
ms-1-dub...
>
>

Paul

2006-01-19, 11:23 am

Hi Oliverio,

Could you try removing the Mode property and see what happens? Also have
you tried SelectMethod = 0?

-Paul

"Oliverio Díaz" <oliveriod@gmail.com> wrote in message
news:43cf0256@forums
-1-dub...
> I have a similar problem with begintrans.
> When I issue a BeginTrans at the connection object I get the error
> -2147467259. The exact description I get is in spanish "Las
> transacciones no pueden tener múltiples conjuntos de registros con este
> tipo de cursor. Cambie el tipo de cursor, confirme la transacción o
> cierre uno de los conjuntos de registros." which translates to something
> like "transactions can't have multiple record sets with this type of
> cursor. Change the type of cursor, confirm he transaction or close one
> of the record sets"
>
> I tried the C.Execute "Begin tran" method initially, but recently
> discovered that it was not working. Somehow the next call to ASE was
> done in another connection that didn't knew about the transaction.
>
> I checked the select method, Connection Cache, etc. Everything is ok.
>
> This is the connection string:
> Provider=MSDASQL. 1;Mode=ReadWrite;Ext
ended
>
Properties=" DSN=INTEGRA;UID=oliv
erio;PWD=password;Wo
rkstationId=ALDEBARA
N;Ap
plicationName=Integr
a;DB=integra"[color=darkred]
>
> This is the code:
> C.BeginTrans
> cjd_ssn = CAJA.InsertarTmp(SEG, tcIngVentas, VLEfectivo, tr_monto(1),
> tr_monto(2), CCur(txtTotal))
> If cjd_ssn = 0 Then
> GoTo ROLLBACK
> End If
> ...
> ...
> C.CommitTrans
> exit sub
> ROLLBACK:
> C.RollBackTrans
>
>
> The error is in the call to the object CAJA. Internally the object
> calls a stored procedure that writes to the table and returns the a
> number as an output parameter.
>
> Here is the call to the stored procedure:
>
> Set coQuery = New ADODB.Command
> coQuery.CommandType = adCmdStoredProc
> coQuery.CommandText = ProcedimientoAlmacen
ado
>
> ' Here I create all the parameters
> If Not fArmarParametros(Pro
cedimientoAlmacenado
, coQuery,
> DebugDelExec) Then
> Set coQuery = Nothing
> GoTo SalirError
> End If
>
> ' Here is the error
> coQuery.ActiveConnection = mvarconnection
>
> ' Abrir el resultset
> coQuery.Execute (FilasAfectadas)
>
> The error is in the sentence where I set the active connection.
>
> This is a big problem for me because I was trusting VB and ODBC to be
> capable of controlling transactions. Now I have to change all my VB
> objects to make them write everything to temporary tables and create a
> new stored procedure to issue the begin trans command, do all the work
> and commit the transaction.
>
> Thanks in advance,
> Oliverio
>
>
> Paul wrote:
Direct?[color=darkred]
because[color=darkre
d]
is[color=darkred]
enable[color=darkred
]
my[color=darkred]
work.[color=darkred]

Oliverio Díaz

2006-01-20, 3:23 am

Hi Paul,

I removed the Mode property, but the results were the same.
When I changed SelectMethod = 0 at the definition of the DSN then I got
a this message:

Number: [-2147467259]
NativeError: [ 0]
[DataDirect][ODBC Sybase Wire Protocol driver]Sybase does not allow more
than one active statement when retrieving results without a cursor.

The help file from Sybase says that "A setting of 1 limits the data
source to one active statement." Is it a bug in the documentation?

Just in case it matters, my SYODASE.DLL file is version 4.20.00.67.

Oliverio

Paul wrote:
> Hi Oliverio,
>
> Could you try removing the Mode property and see what happens? Also have
> you tried SelectMethod = 0?
>
> -Paul
>
> "Oliverio Díaz" <oliveriod@gmail.com> wrote in message
> news:43cf0256@forums
-1-dub...
> Properties=" DSN=INTEGRA;UID=oliv
erio;PWD=password;Wo
rkstationId=ALDEBARA
N;Ap
> plicationName=Integr
a;DB=integra"
> Direct?
> because
> is
> enable
> my
> work.
>
>

Paul

2006-01-20, 9:23 am

Hi Oliverio,

You might want to upgrade that driver to the current in SDK 12.5.1. ESD #11
(5.00.0096)

At this point if the problem persists you need to log a tech support case
with Sybase

The error is not making sense since it looks like only one active stmt!

The TDS protocol limits the use of one active stmt per connection (that is
if you send request you cannot send another until you have
received and handled the response from the server)

-Paul

"Oliverio Díaz" <oliveriod@gmail.com> wrote in message
news:43d057c0$1@foru
ms-1-dub...[color=darkred]
> Hi Paul,
>
> I removed the Mode property, but the results were the same.
> When I changed SelectMethod = 0 at the definition of the DSN then I got
> a this message:
>
> Number: [-2147467259]
> NativeError: [ 0]
> [DataDirect][ODBC Sybase Wire Protocol driver]Sybase does not allow more
> than one active statement when retrieving results without a cursor.
>
> The help file from Sybase says that "A setting of 1 limits the data
> source to one active statement." Is it a bug in the documentation?
>
> Just in case it matters, my SYODASE.DLL file is version 4.20.00.67.
>
> Oliverio
>
>
>
>
>
> Paul wrote:
have[color=darkred]
something[color=dark
red]
Properties=" DSN=INTEGRA;UID=oliv
erio;PWD=password;Wo
rkstationId=ALDEBARA
N;Ap[color=darkred]
Another[color=darkre
d]
there[color=darkred]

is[color=darkred]
But[color=darkred]

Paul

2006-01-20, 1:23 pm

Hi Oliverio,

SelectMethod=0 should work because it uses ASE Cursors on select stmts and
allows more then one active stmt on the connection.
However I have a similar app and it seems to work fine.

In your code there is

C.BeginTrans - I assume C is a connection object

then you have coQuery.ActiveConnection = mvarconnection

How is this connection defined? Maybe if you could post a simplified
project we could look at it or maybe open a tech Support case at Sybase.
Something like this should work as long as you are working off the same
connection.

Do you know how to generate TDS traces with the RIBO utility? It can be
found in the %SYBASE%\jutils-2_0 directory and includes
documentation - it requires java to use. It will trace out the TDS packets
exchanged between client and server and might help troubleshoot this
problem.

-Paul

"Oliverio Díaz" <oliveriod@gmail.com> wrote in message
news:43d057c0$1@foru
ms-1-dub...[color=darkred]
> Hi Paul,
>
> I removed the Mode property, but the results were the same.
> When I changed SelectMethod = 0 at the definition of the DSN then I got
> a this message:
>
> Number: [-2147467259]
> NativeError: [ 0]
> [DataDirect][ODBC Sybase Wire Protocol driver]Sybase does not allow more
> than one active statement when retrieving results without a cursor.
>
> The help file from Sybase says that "A setting of 1 limits the data
> source to one active statement." Is it a bug in the documentation?
>
> Just in case it matters, my SYODASE.DLL file is version 4.20.00.67.
>
> Oliverio
>
>
>
>
>
> Paul wrote:
have[color=darkred]
something[color=dark
red]
Properties=" DSN=INTEGRA;UID=oliv
erio;PWD=password;Wo
rkstationId=ALDEBARA
N;Ap[color=darkred]
Another[color=darkre
d]
there[color=darkred]

is[color=darkred]
But[color=darkred]

Oliverio Díaz

2006-01-26, 4:53 pm

Hi Paul.

It's not easy to make a simplified project.

In this example C is an ActiveX DLL (code_conexion) that I use to manage
everything related to the RDBMS.
The method C.BeginTrans does executes the statement
mvarconnection.BeginTrans.

This is how I open the connection:

If mvarconnection Is Nothing Then
Set mvarconnection = New ADODB.Connection

With mvarconnection
CadenaConexion = "DSN=" + Servidor + ";UID=" + Usuario +
";PWD=" + Clave
If BaseDatos <> "" Then
CadenaConexion = CadenaConexion + ";Initial Catalog=" +
BaseDatos + ";"
End If
CadenaConexion = CadenaConexion + ";WorkstationId=" +
pNombreDePC
CadenaConexion = CadenaConexion + ";ApplicationName=" +
mvarAplicacion
.ConnectionString = CadenaConexion
.CursorLocation = adUseServer

.Open
End With
end if

I only create one code_conexion in the application and only open the
connection ONCE at the beginning.
Before the change to SelectMethod = 0 when I executed sp_who on the server
I could see more than one conection from the same instance of the
application.

Regarding RIBO, I tried it a while ago. If necessary I can get the traces
that it generates.

Oliverio.

"Paul" <Paul@sybase.com> escribió en el mensaje
news:43d129c1$1@foru
ms-2-dub...
> Hi Oliverio,
>
> SelectMethod=0 should work because it uses ASE Cursors on select stmts and
> allows more then one active stmt on the connection.
> However I have a similar app and it seems to work fine.
>
> In your code there is
>
> C.BeginTrans - I assume C is a connection object
>
> then you have coQuery.ActiveConnection = mvarconnection
>
> How is this connection defined? Maybe if you could post a simplified
> project we could look at it or maybe open a tech Support case at Sybase.
> Something like this should work as long as you are working off the same
> connection.
>
> Do you know how to generate TDS traces with the RIBO utility? It can be
> found in the %SYBASE%\jutils-2_0 directory and includes
> documentation - it requires java to use. It will trace out the TDS
> packets
> exchanged between client and server and might help troubleshoot this
> problem.
>
> -Paul
>
> "Oliverio Díaz" <oliveriod@gmail.com> wrote in message
> news:43d057c0$1@foru
ms-1-dub...
> have
> something
> Properties=" DSN=INTEGRA;UID=oliv
erio;PWD=password;Wo
rkstationId=ALDEBARA
N;Ap
> Another
> there
> is
> But
>
>

Sponsored Links