• You are here:  
  • Home
  • Q&A
  • Q&A
  • PowerBuilder
  • Connection is busy with results for another command

Subscribe via RSS
Toggle Submenu
  1. Categories
  2. Recent
  3. Tags
View Replies (3)

Resolved Connection is busy with results for another command

  1. Issue
0
Votes
Undo
  1. Tim Bruce
    Tim Bruce
  2. PowerBuilder
  3. Friday, 22 December 2023 12:55 PM UTC

Sql Server 2019

PowerBuilder 2019 R3 build 2728

app.ini connection (server X'd out)

DBMS="ODBC"
DBParm = "ConnectString='DSN=XXXXX',ConnectOption='SQL_INTEGRATED_SECURITY,SQL_IS_ON', MsgTerse='Yes',DisableBind=1,DelimitIdentifier='No',TrimSpaces=1"

 

I'm getting the following error when trying to retrieve a dataset 

[Microsoft][ODBC Driver 17 for SQL Server]Connection is busy with results for another command

 

I've tried adding the following to my DBParm, but it didn't work

,ProviderString='MARS Connection=TRUE'

 

Thanks 

 

  1. ODBC
  2. PowerBuilder
  3. SQL Server
Refresh PBL questions?
Re: Opening at right end of tabbed window
Accepted Answer
Tim Bruce
Tim Bruce Accepted Answer Pending Moderation
  1. Friday, 22 December 2023 17:45 PM UTC
  2. PowerBuilder
  3. # Permalink

Hey guys,

I ended up using a datawindow to retrieve the values i needed and looping through that instead of fetching the cursor rec each time though the loop.  That works.

 

Thanks for your assistance

Comment
Chris Pollach @Appeon
  1. Chris Pollach @Appeon
  2. Friday, 22 December 2023 19:03 PM UTC
Hi Tim;

Thank you for that feedback & great news!

Yes, I suspect that it was the FETCH Loop holding a Result Set open. Then any other DML command in that loop would have created the MARS issue.

Regards .. Chris
  1. Helpful
There are no comments made yet.
Responses (3)
John Fauss
John Fauss Accepted Answer Pending Moderation
  1. Friday, 22 December 2023 14:18 PM UTC
  2. PowerBuilder
  3. # 1

Hi, Tim -

What is SQLCA.Autocommit setting?

You may wish to temporarily enable database trace (SQLCA.DBMS="ODB TRACE") and review the trace information to see if there is, by chance, an open transaction that has not been committed.

Best regards, John

Comment
Load more comments
Tim Bruce
  1. Tim Bruce
  2. Friday, 22 December 2023 15:03 PM UTC
ok I added it below and I'm still getting the error. I ran a trace is there anything I should look for? I'm hesitant to post the trace here as it contains some sensitive data



DBMS="ODBC"

AutoCommit = 'False'

DBParm = "ConnectString='DSN=XXXXX',ProviderString='MARS Connection=False',ConnectOption='SQL_INTEGRATED_SECURITY,SQL_IS_ON', MsgTerse='Yes',DisableBind=1,DelimitIdentifier='No',TrimSpaces=1"

  1. Helpful
John Fauss
  1. John Fauss
  2. Friday, 22 December 2023 16:25 PM UTC
I don't need or want to see the trace results. It would appear from the message you are receiving that a database transaction may have been left uncompleted. Cannot say with certainty, but that's what I suggest you look for.

See if there is a Begin Transaction without a corresponding Commit or Rollback. If any stored procedures and/or triggers are being run, look at their source code to see if they perform any transaction management and if so, see if there is any way they could be omitting a Commit or Rollback.
  1. Helpful
Tim Bruce
  1. Tim Bruce
  2. Friday, 22 December 2023 16:54 PM UTC
ok I'll have a look. Thanks John
  1. Helpful
There are no comments made yet.
Chris Pollach @Appeon
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Friday, 22 December 2023 13:38 PM UTC
  2. PowerBuilder
  3. # 2

Hi Tim;

  The MARS feature is normally ON as set by SS (not PB) so the DBParm setting " "MARS Connection=TRUE" is actually the SS DB default. MARS of course is Multiple Active Result Sets which means your App has to manage multiple threads of data headed to your PB App. Which of course, the average PB App is not designed to handle. If not handled / coded properly, you will get SQL errors as you did. So you probably need to turn MARS off instead.

   https://community.appeon.com/index.php/qna/q-a/pb2019r3-msoledbsql-and-mars

HTH

Regards ... Chris 

Comment
Tim Bruce
  1. Tim Bruce
  2. Friday, 22 December 2023 13:50 PM UTC
Thanks Chris,



I added



,ProviderString='MARS Connection=False' to my DBParm string and I still get the error







DBParm = "ConnectString='DSN=XXXXX',ConnectOption='SQL_INTEGRATED_SECURITY,SQL_IS_ON', MsgTerse='Yes',DisableBind=1,DelimitIdentifier='No',TrimSpaces=1,ProviderString='MARS Connection=False'"



  1. Helpful
Chris Pollach @Appeon
  1. Chris Pollach @Appeon
  2. Friday, 22 December 2023 15:07 PM UTC
Correct
  1. Helpful
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.