Transaction cannot have multiple recordsets with t

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Transaction cannot have multiple recordsets with t

Author

Topic

xrum
Yak Posting Veteran

87 Posts

Posted - 2012-01-05 : 13:19:43

I know this is not exactly the right forum for this, but i'm so lost i dont know where else to look for help, so i thought i'd try.

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) <-------- error happens here 
    end select

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

end if
rs.movenext
loop

rs.close()
set rs = nothing

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-05 : 13:32:37

sorry didnt understand how you get values for TIMESTAMP and REV and where you're using them. Does someReport store resultset?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

FischMan2
Yak Posting Veteran

59 Posts

Posted - 2012-01-05 : 13:34:05

It looks to me like it's probably due to you using the same MyConn for reading and also writing in your inner loop. MyConn is locked into the rs.read transaction (MyConn.BeginTrans()). Try creating a new connection (e.g.: WriteConn), which will have its own transaction. Then, use that WriteConn to do your inner loop inserts and updates.

(Do you also need to close your MyConn transaction? [end, close, commit, or whatever: e.g.: MyConn.EndTrans()]) It's been too long for me to remember.)

See if that works!

FischMan

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-01-05 : 14:02:00

The error message pretty much describes the problem. You are opening a cursor (I don't mean necessarily a T-SQL cursor) for your "Select someReport from MyTable" command. While that is open, you are trying to do the inserts/updates on the same connection. You cannot do that. Your choices are:

1. Open two connections, do the select to get the data in one connection and the update in the second connection.

2. Get all the data you need, close the recordset (so the cursor gets closed) and then do the updates/inserts.

3. I have not tried this, but this page has guidance http://support.microsoft.com/kb/180843

4. You may be able to use MARS. See here. http://www.sqlteam.com/article/multiple-active-result-sets-mars But I suspect MARS supports only data retrieval, not retrieval and updates.

If I were doing this, instead of doing the retrievals and updates and attempting to manage it via a client-side transaction, I would write a stored procedure to do it entirely in the database. That would be faster, simpler, and perhaps more reliable.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources