Transaction cannot have multiple recordsets with this cursor type.

[xrum]

I am getting the following error in my classic asp application:

Microsoft OLE DB Provider for ODBC Drivers error '80004005'

Transaction cannot have multiple recordsets with this cursor type. Change the cursor type, commit the transaction, or close one of the recordsets.

i am working on migrating the code from Oracle to SQL Server 2008, and this is an issue that i keep seeing here and there, all through out the application.

can't seem to find any fixes for it.

this particular case in this block of code: (i changed the selects to make them shorter)

Set MyConn = Server.CreateObject("ADODB.Connection")
Call OpenORPSConnect(MyConn)
ql = "Select username from mytable"
set rs = MyConn.Execute(sql)
if not rs.EOF then username = rs(0)
if username = "" then username = theUser
rs.close()
set rs = nothing

MyConn.BeginTrans()

sql = "Select someReport from MyTable"
set rs = MyConn.Execute(sql)

do while not rs.EOF
TIMESTAMP = rs("TIMESTAMP")
rev = rs("REV")

select case whatChange
case "Target date"
sql = "Insert into " & caJustTable & _
" (TEXT, TIMESTAMP, CURRENTFLAG)" & _
" Values ( Text& "','" & COPY_TS & "', 'Y')""
MyConn.Execute(sql)
end select

sql = "update table, set this to that"
MyConn.Execute(sql) <-------- error happens here sometimes....

end if
rs.movenext
loop

rs.close()
set rs = nothing

 

[gmmastros]

You have myConn.BeginTrans, but I don't see a commit trans anywhere.


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[xrum]

its all the way on the bottom of the file, it's now shown in the code...

 

[gmmastros]

I have some experience with ASP, and a lot of experience with SQL Server. Looking at this code, I can't help but think that it would be a lot easier to write and would execute faster if this functionality was implemented as a SQL Server stored procedure.

Of course, there would be some work to do in order to accomplish this.

As a quick attempt at fixing this problem, I would encourage you to begin the transaction AFTER the select.

set rs = nothing


sql = "Select someReport from MyTable"
set rs = MyConn.Execute(sql)

[!]MyConn.BeginTrans()[/!] ' Begin trans moved down a couple lines.

do while not rs.EOF

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[xrum]

after moving the BeginTrans, i get the following error:


Microsoft OLE DB Provider for ODBC Drivers error '80004005'

Transaction cannot start while in firehose mode.

 

[gmmastros]

try changing this:

set rs = MyConn.Execute(sql)

to this:

Set rs = MyConn.Open(sql)

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[BigRed1212]

Mr. Mastros,

My suggestion to the OP when this question was posted on another forum was that since there wasn't any error checking, a commit, or rollback code anywhere that I could see, that perhaps dispensing with the transaction framework altogether would be the way to go.

I would be curious as to what you think of that. Since you have already suggested a stored procedure I'm guessing you might agree.

Mike

 

[gmmastros]

Mike,

I suggested a stored procedure because of the nature of the code. From what I can tell, this code retrieves data, loops through a recordset and performs an update for each row. I cannot pretend to know all the details of those code, but I suspect that this entire block can be replaced with a single insert statement or perhaps an insert and an update. By removing all of the back and forth network chatter, this process would be many times faster. The ASP code would probably be reduced to a single execute statement.

I wouldn't necessarily remove the transaction though. I will admit to being "lazy" with some of my own code. Not all of my insert, updates, and deletes contain transactions. However, there are 2 situations where transactions are a huge benefit.

1. If you have multiple statements that perform inserts, updates, and/or deletes, it is often necessary to have transaction to prevent 1/2 of the data being committed and the other 1/2 lost. This is horrible for data consistency.

2. Transactions can speed up large queries. Specifically, if you have a query that inserts, updates, or deletes a lot of data (I'm talking about millions of rows), transactions will make a noticeable speed improvement.

If this were my code to maintain, I would implement the entire process in a stored procedure. That stored procedure would have transaction handling. I would remove the transaction handling from the ASP code and implement error handling in the ASP code.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[BigRed1212]

Okay. That makes sense. Let the SQL do it's thing natively and cough politely if it has a problem, then let the ASP sit an listen patintly doing one thing if all happens in good form and another if it hears a polite cough.

Thanks.