Nested Cursors with MS SQL Server 2000 using OLE-DB

Hi

I'm using PB7 Build (10135) with SQL Server 2000 (SP2). (MDAC 2.7 Installed)
In powerbuilder script when I declare two nested cursors the inner one give
this error
"Cannot Create ne connection because in manual or distributed transaction
mode"
after the Open Cursor statement.

I connect using Microsoft OLEDB.

How can I solve this?

Thanx


0
Mohammad
5/22/2002 2:09:37 PM
sybase.powerbuilder.database 9855 articles. 1 followers. Follow

4 Replies
788 Views

Similar Articles

[PageSpeed] 53

Try using two different connetions for the two cursors.  Some databases won't
allow you to do a select against the database on a connection that already
had another result set pending on it.

On Wed, 22 May 2002 17:09:37 +0300,
 in powersoft.public.powerbuilder.database
Mohammad Alhabibi <m.alhabibi@omegaegypt.com> wrote: 
>Hi
>
>I'm using PB7 Build (10135) with SQL Server 2000 (SP2). (MDAC 2.7 Installed)
>In powerbuilder script when I declare two nested cursors the inner one give
>this error
>"Cannot Create ne connection because in manual or distributed transaction
>mode"
>after the Open Cursor statement.
>
>I connect using Microsoft OLEDB.
>
>How can I solve this?
>
>Thanx
>
>

Bruce Armstrong [TeamSybase]
http://www.teamsybase.com
http://www.needhim.org

---== Posted via the PFCGuide Web Newsreader ==---
http://www.pfcguide.com/_newsgroups/group_list.asp
0
Bruce
5/22/2002 6:27:23 PM
OK
But the same condition executes well on ODBC or Native connections?
Is there any configuration option to solve this problem?


"Bruce Armstrong [TeamSybase]" <NOCANSPAM_bruce.armstrong@teamsybase.com>
wrote in message news:q2zGR5bACHA.301@forums.sybase.com...
> Try using two different connetions for the two cursors.  Some databases
won't
> allow you to do a select against the database on a connection that already
> had another result set pending on it.
>
> On Wed, 22 May 2002 17:09:37 +0300,
>  in powersoft.public.powerbuilder.database
> Mohammad Alhabibi <m.alhabibi@omegaegypt.com> wrote:
> >Hi
> >
> >I'm using PB7 Build (10135) with SQL Server 2000 (SP2). (MDAC 2.7
Installed)
> >In powerbuilder script when I declare two nested cursors the inner one
give
> >this error
> >"Cannot Create ne connection because in manual or distributed transaction
> >mode"
> >after the Open Cursor statement.
> >
> >I connect using Microsoft OLEDB.
> >
> >How can I solve this?
> >
> >Thanx
> >
> >
>
> Bruce Armstrong [TeamSybase]
> http://www.teamsybase.com
> http://www.needhim.org
>
> ---== Posted via the PFCGuide Web Newsreader ==---
> http://www.pfcguide.com/_newsgroups/group_list.asp


0
Mohammad
5/23/2002 7:52:36 AM
Dear sir,
It doesn't work
the two nested cursors are dynamic, plesae have a look at the script
/// Script Begin
String    ls_OuterSQL, ls_InnerSQL, ls_GroupID
Long   ll_Count
TRANSACTION  l_Transaction
DynamicStagingArea  l_SQLSA

l_Transaction   = Create Transaction            // The Second Transaction
Object
l_SQLSA   = Create DynamicStagingArea    // The Second DynamicStagingArea
Object, I don't know if this step is necessary?

l_Transaction.DBMS  = SQLCA.DBMS
l_Transaction.AutoCommit = SQLCA.AutoCommit
l_Transaction.DBParm  = SQLCA.DBParm
Connect using l_Transaction;

ls_OuterSQL =  " SELECT Group_ID FROM  tableName"

DECLARE lcur_Outer DYNAMIC CURSOR FOR SQLSA ;    // I don't know how to
associate it to the second transaction object l_Transaction
PREPARE SQLSA FROM :ls_OuterSQL ;
OPEN DYNAMIC lcur_Outer ;
FETCH lcur_Outer INTO :ls_GroupID ;
DO WHILE sqlca.sqlcode = 0
 ls_InnerSQL= "SELECT COUNT(*) FROM Table2Name WHERE GroupID = "+ ls_GroupID
 DECLARE lcur_Inner DYNAMIC CURSOR FOR l_SQLSA; // Gives an Error "Cannot
Create new connection because in manual or distributed transaction mode"
 PREPARE l_SQLSA FROM :ls_InnerSQL using l_Transaction;
 OPEN lcur_Inner ;
 FETCH lcur_Inner INTO :ll_Count ;
 // .........
 CLOSE lcur_Inner ;
 FETCH lcur_Outer INTO :ls_ChildID , :ls_ChildName , :li_StockIndex ;
LOOP
CLOSE lcur_Outer ;

Disconnect using l_Transaction;
Destroy l_SQLSA
Destroy l_Transaction

/// Script End

so What shoul I do?

"Bruce Armstrong [TeamSybase]" <NOCANSPAM_bruce.armstrong@teamsybase.com>
wrote in message news:q2zGR5bACHA.301@forums.sybase.com...
> Try using two different connetions for the two cursors.  Some databases
won't
> allow you to do a select against the database on a connection that already
> had another result set pending on it.
>
> On Wed, 22 May 2002 17:09:37 +0300,
>  in powersoft.public.powerbuilder.database
> Mohammad Alhabibi <m.alhabibi@omegaegypt.com> wrote:
> >Hi
> >
> >I'm using PB7 Build (10135) with SQL Server 2000 (SP2). (MDAC 2.7
Installed)
> >In powerbuilder script when I declare two nested cursors the inner one
give
> >this error
> >"Cannot Create ne connection because in manual or distributed transaction
mode"
> >after the Open Cursor statement.
> >
> >I connect using Microsoft OLEDB.
> >
> >How can I solve this?
> >
> >Thanx
> >
> >
>
> Bruce Armstrong [TeamSybase]
> http://www.teamsybase.com
> http://www.needhim.org
>
> ---== Posted via the PFCGuide Web Newsreader ==---
> http://www.pfcguide.com/_newsgroups/group_list.asp


0
Mohammad
5/23/2002 10:50:49 AM
Take a look at this:

http://support.microsoft.com/default.aspx?scid=kb;EN-US;q272358

What you could do, and it's probably the better approach given you're working
with PB anyway, is retrieve the first recordset into a datastore and then
loop through it.  That way that first result set is complete and trying to
open the second one will work.  In fact, depending on what your doing with
the data, you might want to use a datastore for both.

On Thu, 23 May 2002 13:50:49 +0300,
 in powersoft.public.powerbuilder.database
Mohammad Alhabibi <m.alhabibi@omegaegypt.com> wrote: 
>Dear sir,
>It doesn't work
>the two nested cursors are dynamic, plesae have a look at the script
>/// Script Begin
>String    ls_OuterSQL, ls_InnerSQL, ls_GroupID
>Long   ll_Count
>TRANSACTION  l_Transaction
>DynamicStagingArea  l_SQLSA
>
>l_Transaction   = Create Transaction            // The Second Transaction
>Object
>l_SQLSA   = Create DynamicStagingArea    // The Second DynamicStagingArea
>Object, I don't know if this step is necessary?
>
>l_Transaction.DBMS  = SQLCA.DBMS
>l_Transaction.AutoCommit = SQLCA.AutoCommit
>l_Transaction.DBParm  = SQLCA.DBParm
>Connect using l_Transaction;
>
>ls_OuterSQL =  " SELECT Group_ID FROM  tableName"
>
>DECLARE lcur_Outer DYNAMIC CURSOR FOR SQLSA ;    // I don't know how to
>associate it to the second transaction object l_Transaction
>PREPARE SQLSA FROM :ls_OuterSQL ;
>OPEN DYNAMIC lcur_Outer ;
>FETCH lcur_Outer INTO :ls_GroupID ;
>DO WHILE sqlca.sqlcode = 0
> ls_InnerSQL= "SELECT COUNT(*) FROM Table2Name WHERE GroupID = "+ ls_GroupID
> DECLARE lcur_Inner DYNAMIC CURSOR FOR l_SQLSA; // Gives an Error "Cannot
>Create new connection because in manual or distributed transaction mode"
> PREPARE l_SQLSA FROM :ls_InnerSQL using l_Transaction;
> OPEN lcur_Inner ;
> FETCH lcur_Inner INTO :ll_Count ;
> // .........
> CLOSE lcur_Inner ;
> FETCH lcur_Outer INTO :ls_ChildID , :ls_ChildName , :li_StockIndex ;
>LOOP
>CLOSE lcur_Outer ;
>
>Disconnect using l_Transaction;
>Destroy l_SQLSA
>Destroy l_Transaction
>
>/// Script End
>
>so What shoul I do?
>
>"Bruce Armstrong [TeamSybase]" <NOCANSPAM_bruce.armstrong@teamsybase.com>
>wrote in message news:q2zGR5bACHA.301@forums.sybase.com...
>> Try using two different connetions for the two cursors.  Some databases
>won't
>> allow you to do a select against the database on a connection that already
>> had another result set pending on it.
>>
>> On Wed, 22 May 2002 17:09:37 +0300,
>>  in powersoft.public.powerbuilder.database
>> Mohammad Alhabibi <m.alhabibi@omegaegypt.com> wrote:
>> >Hi
>> >
>> >I'm using PB7 Build (10135) with SQL Server 2000 (SP2). (MDAC 2.7
>Installed)
>> >In powerbuilder script when I declare two nested cursors the inner one
>give
>> >this error
>> >"Cannot Create ne connection because in manual or distributed transaction
>mode"
>> >after the Open Cursor statement.
>> >
>> >I connect using Microsoft OLEDB.
>> >
>> >How can I solve this?
>> >
>> >Thanx
>> >
>> >
>>
>> Bruce Armstrong [TeamSybase]
>> http://www.teamsybase.com
>> http://www.needhim.org
>>
>> ---== Posted via the PFCGuide Web Newsreader ==---
>> http://www.pfcguide.com/_newsgroups/group_list.asp
>
>

Bruce Armstrong [TeamSybase]
http://www.teamsybase.com
http://www.needhim.org

---== Posted via the PFCGuide Web Newsreader ==---
http://www.pfcguide.com/_newsgroups/group_list.asp
0
Bruce
5/23/2002 4:22:18 PM
Reply:

Web resources about - Nested Cursors with MS SQL Server 2000 using OLE-DB - sybase.powerbuilder.database

Cursor (computers) - Wikipedia, the free encyclopedia
In most command-line interfaces or text editors , the text cursor or caret navigation , is an underscore , a solid rectangle, or a vertical line, ...

iPad Tip : the cursor - Flickr - Photo Sharing!
Like the iPhone, tap on the screen for the cursor to move to that spot. Fine-tune by holding your finger on the cursor, waiting for the magnifying ...

Curse of the cursor: US and Israel credited with shooting Iran down in Flame
Curse of the cursor: US and Israel credited with shooting Iran down in Flame

Setting cursor on address line
Community Platform of DuckDuckGo - Translate DuckDuckGo, use our XMPP or make your own user page!

This Clever Amnesty Campaign Imprisons Your Cursor Within a Banner Ad - Adweek
Sometimes an ad idea just doesn't get the reach it deserves, and this is certainly one of those times.

Video: Kitten meets cursor
Your moment of zen, courtesy of AMERICAblog reader Marianne and her kitten.

Jailbreak Only: SwipeShiftCaret – Swipe To Move Your Cursor A Single Space
A jailbreak tweak called SwipeShiftCaret allows jailbreakers to move their cursor a single space, either left or right, by simply swiping at ...

MacBook, MacBook Pro: Cursor reacts unexpectedly
Does the cursor on your Mac notebook move while you're typing? Do you notice that sometimes the cursor arrow doesn't appear when you use the ...

Apple awarded dynamic 'hybrid cursor' utility patent
The U.S. Patent and Trademark Office on Tuesday granted Apple a utility patent for the dynamically changing mouse cursor seen in previous Mac ...

Google, Aalto University, And Cursor To Focus on South-Eastern Finland
... create quite the startup scene in the suburbs of Helsinki, and now the two organizations are working with financial support of Google and Cursor ...

Resources last updated: 1/8/2016 2:01:12 AM