PowerBuilder's connection to SQL Server

[Former Member]

I am converting a PowerBuilder classic app (PB ver, 12.6) from a Sybase ASE database to a SQL Server database. My question is about Multiple Active Result Sets (MARS) in a SQL Server connection, from a PowerBuilder classic app. SQLCA.DBMS = "ADO"

I am new to PowerBuilder.

I am experienced with ADO.NET

The first exception I ran into was "A transaction that was started in a MARS batch is still active at the end of the batch. The transaction is rolled back.". This was the result from Execute Immediate "Begin Transaction" Using SqlCa. This lead me to explicitly specify "MultipleActiveResultSets=false" in SqlCa.DBParm

So with MARS disabled I reran the app. The begin transaction statement was successful, but another error came up from somewhere (I think the DataWindow Framework). The error is "There is already an open DataReader associated with this Command which must be closed first.".

I have seen this open DataReader error before, in my ADO.NET experience.

These two errors, one with MARS enabled, and one with MARS disabled, leads me to think that the PowerBuilder classic framework is expecting the MARS feature of a SQL Server connection to be enabled.

Am I right that MARS must be enabled on a SQL Server connection from a PB classic app?

[cpollach]

Hi David;

The MARS feature AFAIK is only supported by the SQLServer Native Client driver. A quick check on this aspect would be to change your transaction object, as follows:

• From: SQLCA.DBMS = "ADO"
• To: SQLCA.DBMS = "SNC"

HTH

Regards .. Chris

[CobyKako]

Hi Chris,

You're probably right because MARS was only mentioned for the SNC interface in the PowerBuilder online help.
Indeed, by reading the Connecting to Your Database manual, you can see at the "SQL Server 2005 features" section:

Multiple Active Result Sets
The SNC interface supports Multiple Active Result Sets (MARS), which enable applications to have multiple default result sets open and to interleave reading from them. Applications can also execute statements such as INSERT, UPDATE, and DELETE and stored procedure calls while default result sets are open.

Regards,
Jacob