Error: Cannot create new connection because in manual or distributed transaction mode

Discussion in 'ASP General' started by CJM, Apr 2, 2004.

  1. CJM

    CJM Guest

    I have page that starts a transaction and runs several StoredProcs before
    committing or rollingback. An initial SP create a header records, and then
    the code goes into a loop and runs 4 other SP's once per iteration.

    I'd had it working previous, but now I have changed one of the SPs and the
    order that they all run in...

    Structure of Code:

    oConn.BeginTrans

    sSQL = "Exec etc"
    oConn.Execute sSQL

    Do While <some condition>

    sSQL = "Exec etc"
    oConn.Execute sSQL

    sSQL = "Exec etc"
    rs = oConn.Execute (sSQL)

    sSQL = "Exec etc"
    oConn.Execute sSQL <---- fails here

    sSQL = "Exec etc"
    oConn.Execute sSQL

    Loop

    oConn.CommitTrans



    I have no idea what this error means, nor why it is occuring now. The most
    significant changes I have made have been on the two preceding SPs, and note
    that the 2nd one, now returns a value.

    Any ideas?

    Chris
    CJM, Apr 2, 2004
    #1
    1. Advertising

  2. CJM wrote:
    > I have page that starts a transaction and runs several StoredProcs
    > before committing or rollingback. An initial SP create a header
    > records, and then the code goes into a loop and runs 4 other SP's
    > once per iteration.
    >

    Is there any chance that you can put all of this logic into a single stored
    procedure? It seems to me that in addition to having better control over the
    transaction handling, using a single call to a stored procedure will be much
    more efficient than calling a procedure multiple times in a loop. I've done
    things like sending a delimited string of data to a procedure as a single
    parameter where it is parsed and processed to avoid making multiple calls to
    the database. Is something like this possible? If not, you might wish to
    consider inserting the data that needs to be processed into a work table,
    and then calling a single procedure to handle it.

    Bob Barrows

    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.
    Bob Barrows [MVP], Apr 2, 2004
    #2
    1. Advertising

  3. CJM

    CJM Guest

    Thanks, Bob.

    Is there a chance I can put into one procedure? An outside chance perhaps!
    lol

    If I had a better grasp of T-SQL, I would probably do that. It is probably
    not that different, but I'm vastly more experienced with ASP/VBScript, so I
    have stuck with what I know.

    However, this is one of those ridiculous rush jobs so I dont have any time
    to invest in this. [We identified that a db was needed in Aug '03, the new
    service started Jan '04, DB to support new service was requested in Mar
    '04!]

    I've found a workaround - using a seperate connection for the one SP that
    returns a value. Hardly ideal, but it buys me some time.

    However, I'm making a list of post-project tasks; I'll add combining SPs to
    it.

    "Bob Barrows [MVP]" <> wrote in message
    news:%...
    > CJM wrote:
    > > I have page that starts a transaction and runs several StoredProcs
    > > before committing or rollingback. An initial SP create a header
    > > records, and then the code goes into a loop and runs 4 other SP's
    > > once per iteration.
    > >

    > Is there any chance that you can put all of this logic into a single

    stored
    > procedure? It seems to me that in addition to having better control over

    the
    > transaction handling, using a single call to a stored procedure will be

    much
    > more efficient than calling a procedure multiple times in a loop. I've

    done
    > things like sending a delimited string of data to a procedure as a single
    > parameter where it is parsed and processed to avoid making multiple calls

    to
    > the database. Is something like this possible? If not, you might wish to
    > consider inserting the data that needs to be processed into a work table,
    > and then calling a single procedure to handle it.
    >
    > Bob Barrows
    >
    > --
    > Microsoft MVP -- ASP/ASP.NET
    > Please reply to the newsgroup. The email account listed in my From
    > header is my spam trap, so I don't check it very often. You will get a
    > quicker response by posting to the newsgroup.
    >
    >
    CJM, Apr 2, 2004
    #3
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. =?Utf-8?B?SnVsaWFuIFNhbno=?=

    Distributed Transaction Error

    =?Utf-8?B?SnVsaWFuIFNhbno=?=, Nov 22, 2004, in forum: ASP .Net
    Replies:
    0
    Views:
    435
    =?Utf-8?B?SnVsaWFuIFNhbno=?=
    Nov 22, 2004
  2. Paolo
    Replies:
    1
    Views:
    364
    Lee Fesperman
    Nov 3, 2004
  3. na
    Replies:
    0
    Views:
    497
  4. CJM
    Replies:
    0
    Views:
    318
  5. Vencz Istv?n
    Replies:
    2
    Views:
    273
Loading...

Share This Page