Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP General > Error: Cannot create new connection because in manual or distributed transaction mode

Reply
Thread Tools

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

 
 
CJM
Guest
Posts: n/a
 
      04-02-2004
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



 
Reply With Quote
 
 
 
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      04-02-2004
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.


 
Reply With Quote
 
 
 
 
CJM
Guest
Posts: n/a
 
      04-02-2004
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]" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> 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.
>
>



 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
SQLOLEDB was unable to begin a distributed transaction na ASP .Net 0 12-28-2006 11:29 PM
Win2003 SP1 error: "New transaction cannot enlist in the specified transaction coordinator" Vencz Istv?n ASP General 2 05-02-2005 05:53 AM
Cannot create new connection because in manual or distributed transaction mode. CJM ASP General 0 02-01-2005 10:21 AM
Distributed Transaction Error =?Utf-8?B?SnVsaWFuIFNhbno=?= ASP .Net 0 11-22-2004 05:49 PM
Distributed Transaction Driver Paolo Java 1 11-03-2004 11:28 PM



Advertisments