Hi all,

I'm having a little problem with MS SQL Server and there native OLE DB 
provider (I'm using ADO).

If I open a recordset as follows...

RecSet.Open('SELECT * FROM blah', Con, adOpenKeyset, adLockPessimistic, 
adCmdText);

This works fine.  Then if I try and run an action query I get the error 
message "Object Is Open".

Example...

Cmd.Execute('UPDATE blah2 SET x = 2 WHERE y = 4', adExecuteNoRecords);

I know you can do this if you allow ADO to create new connections when it 
needs them but I don't want this.  I want to use a central connection 
because then transaction will span both the recordset open and the action 
query.

I checked out MS's web-site and they say its a problem but they don't seem 
to have fixed it.

Anybody out there figured out a way to work round this?

Cheers.

Keith, there are other ways to control transactions and their scope other 
than the BeginTrans/EndTrans methods of the connection object.  You don't 
say what environment you are using ADO under, but for example this code 
could be put in a COM object running under MTS(or COM+) with the 
appropriate transaction settings.  MTS can control transactions across 
multiple connections.  The same idea applies to an ASP page, using the 
@TRANSACTION directive.

Exxcuse me if I misunderstand, but why do you need the read wrapped in a 
transaction?  If something goes wrong and you roll back the read, nothing 
happens...

hth
Phil
>----------------------------------------------------
> Hi all,

> I'm having a little problem with MS SQL Server and there native OLE DB 
p> rovider (I'm using ADO).

> If I open a recordset as follows...

> RecSet.Open('SELECT * FROM blah', Con, adOpenKeyset, adLockPessimistic, 
a> dCmdText);

> This works fine.  Then if I try and run an action query I get the error 
m> essage "Object Is Open".

> Example...

> Cmd.Execute('UPDATE blah2 SET x = 2 WHERE y = 4', adExecuteNoRecords);

> I know you can do this if you allow ADO to create new connections when 
it 
n> eeds them but I don't want this.  I want to use a central connection 
b> ecause then transaction will span both the recordset open and the 
action 
q> uery.

> I checked out MS's web-site and they say its a problem but they don't 
seem 
t> o have fixed it.

> Anybody out there figured out a way to work round this?

> Cheers.

Hi Phil,

sorry, the code is running in a Delphi application.  As for the "Read", 
I'm returning the Recordset with locking so I can make changes and call 
Recordset.Update to update the data source.

I'd be interested in how to keep transaction concurrency across multiple 
connections.  How does that work (do you have to use MTS)?

> Keith, there are other ways to control transactions and their scope 
other 
t> han the BeginTrans/EndTrans methods of the connection object.  You 
don't 
s> ay what environment you are using ADO under, but for example this code 
c> ould be put in a COM object running under MTS(or COM+) with the 
a> ppropriate transaction settings.  MTS can control transactions across 
m> ultiple connections.  The same idea applies to an ASP page, using the 
@> TRANSACTION directive.

> Exxcuse me if I misunderstand, but why do you need the read wrapped in a 
t> ransaction?  If something goes wrong and you roll back the read, 
nothing 
h> appens...

> hth
P> hil
>> ----------------------------------------------------
>>  Hi all,

> > I'm having a little problem with MS SQL Server and there native OLE DB 
p> > rovider (I'm using ADO).

> > If I open a recordset as follows...

> > RecSet.Open('SELECT * FROM blah', Con, adOpenKeyset, 
adLockPessimistic, 
a> > dCmdText);

> > This works fine.  Then if I try and run an action query I get the 
error 
m> > essage "Object Is Open".

> > Example...

> > Cmd.Execute('UPDATE blah2 SET x = 2 WHERE y = 4', adExecuteNoRecords);

> > I know you can do this if you allow ADO to create new connections when 
i> t 
n> > eeds them but I don't want this.  I want to use a central connection 
b> > ecause then transaction will span both the recordset open and the 
a> ction 
q> > uery.

> > I checked out MS's web-site and they say its a problem but they don't 
s> eem 
t> > o have fixed it.

> > Anybody out there figured out a way to work round this?

> > Cheers.

Keith, MTS automatically handles concurrency across multiple connections.  
You don't really have to do anything other than tell it whether to 
complete or abort the whole transaction.  Here's a url that explains in 
general what it can do:
http://msdn.microsoft.com/library/default.asp?url=/library/en-
us/dnmts/html/msdn_mtsintro.asp

However, it may not be trivial to convert your existing code to run under 
MTS - generally MTS objects (which must be ActiveX DLLs) are state-less 
between calls, so the re-working may be more hassle than the gains from 
transaction control.

Re the recordset, couldn't you change your updates to use SQL UPDATE 
statements and run them using the Execute method of the Connection object? 
then you can explicitly control which connection you are using.  What db 
do you have behind your ADO?

rgds
Phil
>------------------------------------------------
> Hi Phil,

> sorry, the code is running in a Delphi application.  As for the "Read", 
I> 'm returning the Recordset with locking so I can make changes and call 
R> ecordset.Update to update the data source.

> I'd be interested in how to keep transaction concurrency across multiple 
c> onnections.  How does that work (do you have to use MTS)?

> > Keith, there are other ways to control transactions and their scope 
o> ther 
t> > han the BeginTrans/EndTrans methods of the connection object.  You 
d> on't 
s> > ay what environment you are using ADO under, but for example this 
code 
c> > ould be put in a COM object running under MTS(or COM+) with the 
a> > ppropriate transaction settings.  MTS can control transactions across 
m> > ultiple connections.  The same idea applies to an ASP page, using the 
@> > TRANSACTION directive.

> > Exxcuse me if I misunderstand, but why do you need the read wrapped in 
a 
t> > ransaction?  If something goes wrong and you roll back the read, 
n> othing 
h> > appens...

> > hth
P> > hil
>> > ----------------------------------------------------
>> >  Hi all,

> > > I'm having a little problem with MS SQL Server and there native OLE 
DB 
p> > > rovider (I'm using ADO).

> > > If I open a recordset as follows...

> > > RecSet.Open('SELECT * FROM blah', Con, adOpenKeyset, 
a> dLockPessimistic, 
a> > > dCmdText);

> > > This works fine.  Then if I try and run an action query I get the 
e> rror 
m> > > essage "Object Is Open".

> > > Example...

> > > Cmd.Execute('UPDATE blah2 SET x = 2 WHERE y = 4', 
adExecuteNoRecords);

> > > I know you can do this if you allow ADO to create new connections 
when 
i> > t 
n> > > eeds them but I don't want this.  I want to use a central 
connection 
b> > > ecause then transaction will span both the recordset open and the 
a> > ction 
q> > > uery.

> > > I checked out MS's web-site and they say its a problem but they 
don't 
s> > eem 
t> > > o have fixed it.

> > > Anybody out there figured out a way to work round this?

> > > Cheers.