Transactions - SQL Server Forums

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 2000 Forums

SQL Server Development (2000)

Transactions

Author

Topic

SQLError
Yak Posting Veteran

63 Posts

Posted - 2004-05-12 : 19:50:00

I tried doing the following in my code to test transactions:

'-------------------------

oConn.BeginTrans

Set oRs = oConn.Execute(sMyQuery)

Set ors = oConn.Execute(sMyQuery2)

oConn.CommitTrans

---------------------------

I would get an error message:

"Transaction cannot have multiple recordsets with this cursor type ... "

I put a oRs.close() after the first Set ors = ... statement and I believed it
worked. Now Im not sure if I solved the problem correctly.

Anyone know?

Is there another way to solve this.

I read about how you can't have 2 recordsets for a BeginTrans. The rest is greek to me
at this point.

PS. do you use a global connection or do you open a connection and
try to close it as soon as you can?

thanks

SQLError
Yak Posting Veteran

63 Posts

Posted - 2004-05-12 : 19:54:56

Crap, I guess you cant reuse the record set variable after it is
close hu?

MichaelP
Jedi Yak

2489 Posts

Posted - 2004-05-12 : 20:03:08

How about executing both queries inside of a stored proc?

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

SQLError
Yak Posting Veteran

63 Posts

Posted - 2004-05-12 : 20:09:11

Im not sure how to do this and I don't have much time to learn it.
Is there some way to get past this by code within vb?

SQLServerDBA_Dan
Aged Yak Warrior

752 Posts

Posted - 2004-05-12 : 21:31:53

quote:
Originally posted by SQLError

Im not sure how to do this and I don't have much time to learn it.
Is there some way to get past this by code within vb?

Just create the stored proc like this:

create stored proc <proc name> <variables> as
<sMyQuery>
<sMyQuery2>
GO

Then call the proc from vb. VB6 stored proc call with no proc params looks like:

'replace usp_runmyquery with the stored proc name that you used.
strSQL = "exec usp_runmyquery"
rs.Open strSQL, gConn

Daniel
SQL Server DBA

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2004-05-13 : 04:02:52

That still won't work. The problem here is that a transaction can have one and *only one* firehose cursor open at a time. A firehose cursor is a forward-only cursor with a read-only lock. That is the default type of cursor returned by the Connection.Execute() function - as an ADO recordset (the mode is adOpenForwardOnly and the LockType is adLockReadOnly). Try this:


Dim rs1 as new ADODB.Recordset
Dim rs2 as new ADODB.Recordset

oConn.BeginTrans
rs1.Open strQuery1, oConn, adOpenStatic, adLockReadOnly
rs2.Open strQuery2, oConn, adOpenStatic, adLockReadOnly
oConn.CommitTrans

While firehose cursors are great for simple readonly procedures as they are fast and use very little resources, they can complicate issues such as transactions and connection pooling since a connection can have only one firehose cursor open at any time.

OS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources