473,326 Members | 2,013 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

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

CJM
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

Jul 19 '05 #1
2 12846
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.
Jul 19 '05 #2
CJM
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]" <re******@NOyahoo.SPAMcom> wrote in message
news:%2****************@TK2MSFTNGP11.phx.gbl...
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.

Jul 19 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Wayno | last post by:
My php logs are coming up empty. I have done all I can think of, and all that made sense to me. Can someone take a look at my php.ini please and tell me what you think may be the problem. I...
7
by: Prashanth | last post by:
Hi, We are facing some issues in COM+ deployment. We get system error "Unable to enlist in a distributed transaction" . It has a very inconsistant behaviour. On refreshing the screens couple of...
0
by: Sam Commar | last post by:
We are using a Standard Microsoft Product called Solomon which has a Web interface. We have installed this in more than 50 sites but we are getting the following error after being in the Web...
0
by: faktujaa | last post by:
Hi, I receive the above error in my code when in debug mode but im not using serviced components instead im making use of sql server transaction. In short, I have defined a transaction class that...
1
by: Zri Man | last post by:
The Error in play is CLI Driver] SQL30020N Execution failed because of a Distributed Protocol Error that will affect the successful execution of subsequent commands and SQL statements: Reason Code...
0
by: gshawn3 | last post by:
Hi, I am having a hard time creating a Trigger to update an Oracle database. I am using a SQL Server 2005 Express database on a Win XP Pro SP2 desktop, linked to an Oracle 10g database on a...
0
by: cristo | last post by:
I, After reading all what I could find I have some question about transaction and connection pooling. Let`s look at an example : Dim _transOpt As TransactionOptions...
2
by: Martin Z | last post by:
I'm using the TableAdapterHelper to set the connection and transaction properties on all the commands of all my typed table adapters.... I've checked at the time of the error and all the commands...
10
by: happyse27 | last post by:
Hi All, I got this apache errors(see section A1 and A2 below) when I used a html(see section b below) to activate acctman.pl(see section c below). Section D below is part of the configuration...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.