Delete and insert data in same transaction!

Hi!
I try to add records in one table from a VB6 com+ component using ADO
(provider=SQLOLEDB, SQL-server2000).
Basically what I try to do is to
1. Start a transaction
2. Delete old records in one table
3. Add new values in the same table
4. commit transaction
The problem is that I get a duplicate key when I insert my new data in
step3. If I commit the data between step 2 and 3 everything works fine. If
step 4 then fails I will end up with no data in the table which I don't want
(i mean that is what transaction is used for).
This must be a pretty common scenario so I hope there will be a solution
that will not force me to commit the transaction in the middle.
Regards
/HansHi Hans,
as you descibed the scenario should be fine, allowing the transaction
to commit without problems, perhaps there is a logical problem in your
code. Could you please post the code here. This would make error
searching much easier for us.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--|||Hans wrote:
> Hi!
> I try to add records in one table from a VB6 com+ component using ADO
> (provider=SQLOLEDB, SQL-server2000).
> Basically what I try to do is to
> 1. Start a transaction
> 2. Delete old records in one table
> 3. Add new values in the same table
> 4. commit transaction
> The problem is that I get a duplicate key when I insert my new data in
> step3. If I commit the data between step 2 and 3 everything works fine. If
> step 4 then fails I will end up with no data in the table which I don't wa
nt
> (i mean that is what transaction is used for).
> This must be a pretty common scenario so I hope there will be a solution
> that will not force me to commit the transaction in the middle.
> Regards
> /Hans
Do you mean you want to delete and then insert new row(s) with the same
key values? That may not be an optimal solution since you could
accomplish the same thing with an UPDATE. The following works for me.
If this example doesn't help then please post some code so that we can
reproduce the problem.
CREATE TABLE tbl (x INT PRIMARY KEY);
INSERT INTO tbl(x) VALUES (1);
BEGIN TRAN;
DELETE FROM tbl WHERE x=1;
INSERT INTO tbl(x) VALUES (1);
COMMIT TRAN;
SELECT x FROM tbl;
I recommend you put the DELETE/INSERT code in a stored procedure and
execute the proc from your VB code.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||> The problem is that I get a duplicate key when I insert my new data in
> step3.
This indicates that INSERTs are occurring on a different connection that the
DELETE and not within the same transaction context. You can run a SQL
Profiler trace to see the actual behavior.
Note that ADO is particularly nasty about opening additional connections
behind your back. It is important to include 'SET NOCOUNT ON' in
procs/scripts and process all results returned so that connections can be
reused.
Hope this helps.
Dan Guzman
SQL Server MVP
"Hans" <hansb@.sorry.nospam.com> wrote in message
news:OXTNpYnSGHA.2156@.tk2msftngp13.phx.gbl...
> Hi!
> I try to add records in one table from a VB6 com+ component using ADO
> (provider=SQLOLEDB, SQL-server2000).
> Basically what I try to do is to
> 1. Start a transaction
> 2. Delete old records in one table
> 3. Add new values in the same table
> 4. commit transaction
> The problem is that I get a duplicate key when I insert my new data in
> step3. If I commit the data between step 2 and 3 everything works fine. If
> step 4 then fails I will end up with no data in the table which I don't
> want
> (i mean that is what transaction is used for).
> This must be a pretty common scenario so I hope there will be a solution
> that will not force me to commit the transaction in the middle.
> Regards
> /Hans
>
>|||Hi Jens, David and Dan!
Thanks for your replies.
Here is the code. The code is used to store default values for a user. The
table have fields for which user it is (idUser), which field (idfld) and
some other fields about the default values. The code is most likely not the
most efficient (the "IN" operator is slow but we are talking about pretty
small tables here with a couple of 1000 records) but it is only executed a
couple of times/year for a normal user (and it only takes like 100
milliseconds to excecute as it is). The key in the table is idUser (user id)
and idfld (Field id) together and I'm not sure if there is a way to update
or add in one single SQL-statement and also delete records where I earlier
had defaultvalues but where the user no longer want to have default values.
Therfor I delete all defaultvalues for the current user for the current
table (I join in another table which holds the table id). For example there
may be 5 rows before the save and maybe only 3 rows left in the table after
the update.
If OS_WIN2000 Then
Set rs = CreateObject("ADODB.Recordset")
Set con = CreateObject("ADODB.Connection")
Else
Set rs = CtxCreateObject("ADODB.Recordset")
Set con = CtxCreateObject("ADODB.Connection")
End If
con.Open GetConnectionString()
'Use transaction
con.BeginTrans
rs.CursorLocation = adUseClient
rs.CursorType = adOpenStatic
rs.LockType = adLockOptimistic
'idtbl and idUser is already singlequoted
For i = LBound(userList) To UBound(userList)
sSQL = "Delete from " & TSP("vmo_base_defaultvalues") & " where iduser="
& userList(i) & " and "
sSQL = sSQL & " idfld in (select vmo_base_field.idfld from " &
TSP("vmo_base_defaultvalues") & ","
sSQL = sSQL & TSP("vmo_base_field") & " where
vmo_base_field.idfld=vmo_base_defaultvalues.idfld "
sSQL = sSQL & " and vmo_base_field.idtbl = " & idTBL & ")"
rs.Open sSQL, con
'If I commit here it works OK but I want to commit after the entire
operation is finished
For l = LBound(sArg) To UBound(sArg) Step 4
If sArg(l) <> "" And sArg(l + 1) <> "" Then
sSQL = "Insert into " & TSP("vmo_base_defaultvalues") & "
(idUser,idfld,vValue,datevalue, deftype) " & _
"values (" & _
userList(i) & "," & _
sArg(l) & "," & _
sArg(l + 1) & "," & _
sArg(l + 2) & "," & _
sArg(l + 3) & ")"
rs.Open sSQL, con
End If
Next l
Next i
con.CommitTrans
Regards
/Hans|||The main problem is that you are using recordsets when no data are returned.
Additional connections are probably acquired for the subsequent INSERT
statements and outside scope of the first transaction.
The example below shows how to use Command objects for these DML statements.
I would also suggest using parameters instead of concatenating literal
values. Parameters are more secure, eliminate the need to quote values,
escape quotes, format dates, etc.
Set con = CreateObject("ADODB.Connection")
Set cmd = CreateObject("ADODB.Command")
con.Open GetConnectionString()
cmd.ActiveConnection = con
con.BeginTrans
'idtbl and idUser is already singlequoted
For i = LBound(userList) To UBound(userList)
sSQL = "SET NOCOUNT ON Delete from " & _
TSP("vmo_base_defaultvalues") & _
" where iduser=" & _
userList(i) & " and "
sSQL = sSQL & " idfld in (select vmo_base_field.idfld from " &
TSP("vmo_base_defaultvalues") & ","
sSQL = sSQL & TSP("vmo_base_field") & _
" where vmo_base_field.idfld=vmo_base_defaultvalues.idfld "
sSQL = sSQL & " and vmo_base_field.idtbl = " & idTBL & ")"
cmd.CommandText = sSQL
cmd.Execute
For l = LBound(sArg) To UBound(sArg) Step 4
If sArg(l) <> "" And sArg(l + 1) <> "" Then
sSQL = "SET NOCOUNT ON Insert into " & _
TSP("vmo_base_defaultvalues") & _
"(idUser,idfld,vValue,datevalue, deftype) " & _
"values (" & _
userList(i) & "," & _
sArg(l) & "," & _
sArg(l + 1) & "," & _
sArg(l + 2) & "," & _
sArg(l + 3) & ")"
cmd.CommandText = sSQL
cmd.Execute
End If
Next l
Next i
con.CommitTrans
Hope this helps.
Dan Guzman
SQL Server MVP
"Hans" <hansb@.sorry.nospam.com> wrote in message
news:%23%23EoJT2SGHA.1148@.TK2MSFTNGP10.phx.gbl...
> Hi Jens, David and Dan!
> Thanks for your replies.
> Here is the code. The code is used to store default values for a user. The
> table have fields for which user it is (idUser), which field (idfld) and
> some other fields about the default values. The code is most likely not
> the
> most efficient (the "IN" operator is slow but we are talking about pretty
> small tables here with a couple of 1000 records) but it is only executed a
> couple of times/year for a normal user (and it only takes like 100
> milliseconds to excecute as it is). The key in the table is idUser (user
> id)
> and idfld (Field id) together and I'm not sure if there is a way to update
> or add in one single SQL-statement and also delete records where I earlier
> had defaultvalues but where the user no longer want to have default
> values.
> Therfor I delete all defaultvalues for the current user for the current
> table (I join in another table which holds the table id). For example
> there
> may be 5 rows before the save and maybe only 3 rows left in the table
> after
> the update.
> If OS_WIN2000 Then
> Set rs = CreateObject("ADODB.Recordset")
> Set con = CreateObject("ADODB.Connection")
> Else
> Set rs = CtxCreateObject("ADODB.Recordset")
> Set con = CtxCreateObject("ADODB.Connection")
> End If
> con.Open GetConnectionString()
> 'Use transaction
> con.BeginTrans
> rs.CursorLocation = adUseClient
> rs.CursorType = adOpenStatic
> rs.LockType = adLockOptimistic
> 'idtbl and idUser is already singlequoted
> For i = LBound(userList) To UBound(userList)
> sSQL = "Delete from " & TSP("vmo_base_defaultvalues") & " where
> iduser="
> & userList(i) & " and "
> sSQL = sSQL & " idfld in (select vmo_base_field.idfld from " &
> TSP("vmo_base_defaultvalues") & ","
> sSQL = sSQL & TSP("vmo_base_field") & " where
> vmo_base_field.idfld=vmo_base_defaultvalues.idfld "
> sSQL = sSQL & " and vmo_base_field.idtbl = " & idTBL & ")"
> rs.Open sSQL, con
> 'If I commit here it works OK but I want to commit after the entire
> operation is finished
> For l = LBound(sArg) To UBound(sArg) Step 4
> If sArg(l) <> "" And sArg(l + 1) <> "" Then
> sSQL = "Insert into " & TSP("vmo_base_defaultvalues") & "
> (idUser,idfld,vValue,datevalue, deftype) " & _
> "values (" & _
> userList(i) & "," & _
> sArg(l) & "," & _
> sArg(l + 1) & "," & _
> sArg(l + 2) & "," & _
> sArg(l + 3) & ")"
> rs.Open sSQL, con
> End If
> Next l
> Next i
> con.CommitTrans
>
> Regards
> /Hans
>|||Thanks Dan for the tip!
Yes that seems to fix the problem. The code is pretty old and written for
Oracle in the first place where I did not have any problems with the
transaction (so I guess the real problem is inside the oledb provider). Yes
you are right using parameters is much safer but at least I don't like
touching code that has been working for years, well at least for other
databases than SQL-server :-)
/Hans

Delay command execution

Hi
Here is the setting: Clients connect to SQL Server 2000 via ADO.
Here is the problem: After a client's successful login, the SQL Server 2000
should delay the processing of the client-commands by, e.g., 1 sec. How do I
tell it to the SQL Server 2000?
Thanks in advance.
Adrianat the sql server side
WAITFOR DELAY '00:00:01'
But why would you need it. You can as well have the delay at the client side
.
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||Thank you.
Well, I know this command. But where precisely do I tell the SQL Server to
delay an incoming command?
A delay at the client-side would be fine as well. But now, how do I tell it
to the client (3rd-party, no source code)?
Basically, I need to delay only the first command of a client immediately
after the login, so that I can run a "login-script" for the client on the
server.
Adrian
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:669DA5C9-52D0-4AA4-B489-2E041F14F7DE@.microsoft.com...
> at the sql server side
> WAITFOR DELAY '00:00:01'
> But why would you need it. You can as well have the delay at the client
> side.
> --
> -Omnibuzz (The SQL GC)
> http://omnibuzz-sql.blogspot.com/
>|||> Well, I know this command. But where precisely do I tell the SQL Server to delay an incom
ing
> command?
There's no such setting in SQL Server.

> A delay at the client-side would be fine as well. But now, how do I tell i
t to the client
> (3rd-party, no source code)?
You would have to talk to the 3:rd party vendor about this...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Adrian" <adrian@.iai.uni-bonn.de> wrote in message news:eYlmvehjGHA.4344@.TK2MSFTNGP05.phx.g
bl...
> Thank you.
> Well, I know this command. But where precisely do I tell the SQL Server to
delay an incoming
> command?
> A delay at the client-side would be fine as well. But now, how do I tell i
t to the client
> (3rd-party, no source code)?
> Basically, I need to delay only the first command of a client immediately
after the login, so that
> I can run a "login-script" for the client on the server.
> Adrian
> "Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
> news:669DA5C9-52D0-4AA4-B489-2E041F14F7DE@.microsoft.com...
>|||That's bad news.
But...
I monitor login-events with a trace and process the events in a trigger,
which is attached to the trace-table. I need to keep the client waiting
until the trigger finishes. Is there any other way to do this?
Adrian
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ez$y2shjGHA.4884@.TK2MSFTNGP03.phx.gbl...
> There's no such setting in SQL Server.
>
> You would have to talk to the 3:rd party vendor about this...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Adrian" <adrian@.iai.uni-bonn.de> wrote in message
> news:eYlmvehjGHA.4344@.TK2MSFTNGP05.phx.gbl...
>|||Adrian (adrian@.iai.uni-bonn.de) writes:
> That's bad news.
> But...
> I monitor login-events with a trace and process the events in a trigger,
> which is attached to the trace-table. I need to keep the client waiting
> until the trigger finishes. Is there any other way to do this?
Wait, this sounds dangerous. OK, I don't know the architecture of
this particular 3rd party tool. But most modern applications these
days opens a connection, submits a query or two and then close the
connection. Or rather, that is how the application code looks like.
Under the hood, the client API maintains a connection pool, so that
if the application reconnects soon enough, a connection will be
reused.
Nevertheless, a one-second delay on each login sounds like a bad idea
to me.
Maybe if you explain in more detail what you are trying on achieve and
why, we may come with suggestions.
Don't forget to tell which version of SQL Server you are using.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspxsql

Degrading performance. Expert help appreciated

Greetings,
In our current project, we use a combination of SQL Server 2000 and
ADO. Our database is very simple and small: just about 10 tables with
300K records at most (in just one of the tables). We need, however,
very fast responses to our queries. With that in mind, we designed and
optimized all queries and indices in such a way that each query takes
less that 20 milliseconds, as measured using the SQL profiler under a
normal load.
Each client application opens a single connection to the database and
the queries are funneled through that connection. Each query is a
individual transaction, i.e. it is fenced by Begin Tran...End Tran. We
use mostly stored procedures, which are executed via the _Command
object from ADO. In just a couple of cases, we use _Recordset.
Under a *stress* load, one client can submit 20 transactions/sec to
the server.
In this scenario, I noticed that, sometimes, many commands were taking
almost two orders of magnitude more than under the normal load. I used
SQL Profiler to monitor all Statements and SPs taking longer tha 100
msec and, to my surprise, found that, every so often, some command or
SP would take more than 1-2 seconds. What is interesting is that many
of these commands are IF @.@.TRANCOUNT > 0 COMMIT TRAN, which, I think,
ADO implicitly sends to the server. Those usually show with duration 0
(Zero) under normal load.
What I wanted from you, were some ideas on how to go about
troubleshooting this problem, by identifying the underlying cause for
such poor performance. The problem does not seem associated with a
particular command or SP. It also does not seem related to CPU
contention on the server because it is kept really low (about 20%).
Your help is greatly appreciated.
- CDThis is a multi-part message in MIME format.
--=_NextPart_000_0074_01C3EB4D.F4E4CB70
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
How necessary is the Begin Tran/End Tran? If you don't need to make a
series of updates atomic then leave this out.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
.
<crbd98@.yahoo.com> wrote in message
news:edf41e63.0402041449.724d721b@.posting.google.com...
Greetings,
In our current project, we use a combination of SQL Server 2000 and
ADO. Our database is very simple and small: just about 10 tables with
300K records at most (in just one of the tables). We need, however,
very fast responses to our queries. With that in mind, we designed and
optimized all queries and indices in such a way that each query takes
less that 20 milliseconds, as measured using the SQL profiler under a
normal load.
Each client application opens a single connection to the database and
the queries are funneled through that connection. Each query is a
individual transaction, i.e. it is fenced by Begin Tran...End Tran. We
use mostly stored procedures, which are executed via the _Command
object from ADO. In just a couple of cases, we use _Recordset.
Under a *stress* load, one client can submit 20 transactions/sec to
the server.
In this scenario, I noticed that, sometimes, many commands were taking
almost two orders of magnitude more than under the normal load. I used
SQL Profiler to monitor all Statements and SPs taking longer tha 100
msec and, to my surprise, found that, every so often, some command or
SP would take more than 1-2 seconds. What is interesting is that many
of these commands are IF @.@.TRANCOUNT > 0 COMMIT TRAN, which, I think,
ADO implicitly sends to the server. Those usually show with duration 0
(Zero) under normal load.
What I wanted from you, were some ideas on how to go about
troubleshooting this problem, by identifying the underlying cause for
such poor performance. The problem does not seem associated with a
particular command or SP. It also does not seem related to CPU
contention on the server because it is kept really low (about 20%).
Your help is greatly appreciated.
- CD
--=_NextPart_000_0074_01C3EB4D.F4E4CB70
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

How necessary is the Begin Tran/End =Tran? If you don't need to make a series of updates atomic then leave this out.
-- Tom
----Thomas A. =Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql.
=wrote in message news:edf41e=63.0402041449.724d721b@.posting.google.com...Greetings,I=n our current project, we use a combination of SQL Server 2000 andADO. =Our database is very simple and small: just about 10 tables with300K =records at most (in just one of the tables). We need, however,very fast =responses to our queries. With that in mind, we designed andoptimized all queries =and indices in such a way that each query takesless that 20 =milliseconds, as measured using the SQL profiler under anormal load.Each =client application opens a single connection to the database andthe queries =are funneled through that connection. Each query is aindividual =transaction, i.e. it is fenced by Begin Tran...End Tran. Weuse mostly stored =procedures, which are executed via the _Commandobject from ADO. In just a couple =of cases, we use _Recordset.Under a *stress* load, one client can =submit 20 transactions/sec tothe server.In this scenario, I noticed that, sometimes, many commands were takingalmost two orders of magnitude =more than under the normal load. I usedSQL Profiler to monitor all Statements =and SPs taking longer tha 100msec and, to my surprise, found that, every so =often, some command orSP would take more than 1-2 seconds. What is =interesting is that manyof these commands are IF @.@.TRANCOUNT > 0 COMMIT TRAN, =which, I think,ADO implicitly sends to the server. Those usually show with =duration 0(Zero) under normal load.What I wanted from you, were some =ideas on how to go abouttroubleshooting this problem, by identifying the =underlying cause forsuch poor performance. The problem does not seem associated =with aparticular command or SP. It also does not seem related to CPUcontention on the server because it is kept really low (about 20%).Your help is greatly appreciated.- CD

---

--=_NextPart_000_0074_01C3EB4D.F4E4CB70--|||sounds like some blocking issues.
when you run your tests, monitor blocks in SQL Server (and\or Deadlocks)
do your sprocs have NOLOCK Hints in them when appropriate ?
Or setting transaction isolation level to READ UNCOMMITTED (Where
Appropriate).
probably worth looking into.
cheers
Greg Jackson
PDX, OR|||Are you also using VB transactions? I found performance
improvement by issuing transactions only from my stored
procedures. I believe VB trans cause SQL to SET
IMPLICIT_TRANSACTIONS ON which can increase lock
contention & network round trips. I also saw
improvements by using disconnected recordsets.
Darren Fuller
SQL Server DBA MCSE
>--Original Message--
>Greetings,
>In our current project, we use a combination of SQL
Server 2000 and
>ADO. Our database is very simple and small: just about
10 tables with
>300K records at most (in just one of the tables). We
need, however,
>very fast responses to our queries. With that in mind,
we designed and
>optimized all queries and indices in such a way that
each query takes
>less that 20 milliseconds, as measured using the SQL
profiler under a
>normal load.
>Each client application opens a single connection to the
database and
>the queries are funneled through that connection. Each
query is a
>individual transaction, i.e. it is fenced by Begin
Tran...End Tran. We
>use mostly stored procedures, which are executed via the
_Command
>object from ADO. In just a couple of cases, we use
_Recordset.
>Under a *stress* load, one client can submit 20
transactions/sec to
>the server.
>In this scenario, I noticed that, sometimes, many
commands were taking
>almost two orders of magnitude more than under the
normal load. I used
>SQL Profiler to monitor all Statements and SPs taking
longer tha 100
>msec and, to my surprise, found that, every so often,
some command or
>SP would take more than 1-2 seconds. What is interesting
is that many
>of these commands are IF @.@.TRANCOUNT > 0 COMMIT TRAN,
which, I think,
>ADO implicitly sends to the server. Those usually show
with duration 0
>(Zero) under normal load.
>What I wanted from you, were some ideas on how to go
about
>troubleshooting this problem, by identifying the
underlying cause for
>such poor performance. The problem does not seem
associated with a
>particular command or SP. It also does not seem related
to CPU
>contention on the server because it is kept really low
(about 20%).
>Your help is greatly appreciated.
>- CD
>.
>

default result set semantics

When an ADO command does not exhaust the default result set and a second
command is executed, the sqloledb provider automatically spawns a new session
to execute the second command (using the default result set). My question is
since command2 was definied on the connection (and prepared = true), should
one be allowed to rebind a paramter on command2 and execute again? Doing so
results in the following error -
"Multiple-step OLD DB operator generated errors. Check each OLE DB status
value, if available. No work was done."
pseudo code example:
cmd1.execute
while not rs1.eof
obtain row value, bind into cmd2
cmd2.execute
First execute works, second iteration fails.
The solution is to shutdown cmd2 within the loop and recreate the command
for each execution.
Is this the expected behavior because of the inconsistent state for the
command (having been spawned to a new session)?
I know 2005 solves this issues w/ MARS.
Thanks.
Correct. MARS in yukon is designed to solve this.
http://msdn.microsoft.com/library/en...asp?frame=true
-oj
"Thomas Brown" <ThomasBrown@.discussions.microsoft.com> wrote in message
news:B495E631-3D88-4880-B678-6E7F6DF9749C@.microsoft.com...
> When an ADO command does not exhaust the default result set and a second
> command is executed, the sqloledb provider automatically spawns a new
> session
> to execute the second command (using the default result set). My question
> is
> since command2 was definied on the connection (and prepared = true),
> should
> one be allowed to rebind a paramter on command2 and execute again? Doing
> so
> results in the following error -
> "Multiple-step OLD DB operator generated errors. Check each OLE DB status
> value, if available. No work was done."
> pseudo code example:
> cmd1.execute
> while not rs1.eof
> obtain row value, bind into cmd2
> cmd2.execute
> First execute works, second iteration fails.
> The solution is to shutdown cmd2 within the loop and recreate the command
> for each execution.
> Is this the expected behavior because of the inconsistent state for the
> command (having been spawned to a new session)?
> I know 2005 solves this issues w/ MARS.
> Thanks.
>

default result set semantics

When an ADO command does not exhaust the default result set and a second
command is executed, the sqloledb provider automatically spawns a new session
to execute the second command (using the default result set). My question is
since command2 was definied on the connection (and prepared = true), should
one be allowed to rebind a paramter on command2 and execute again? Doing so
results in the following error -
"Multiple-step OLD DB operator generated errors. Check each OLE DB status
value, if available. No work was done."
pseudo code example:
cmd1.execute
while not rs1.eof
obtain row value, bind into cmd2
cmd2.execute
First execute works, second iteration fails.
The solution is to shutdown cmd2 within the loop and recreate the command
for each execution.
Is this the expected behavior because of the inconsistent state for the
command (having been spawned to a new session)?
I know 2005 solves this issues w/ MARS.
Thanks.Correct. MARS in yukon is designed to solve this.
http://msdn.microsoft.com/library/en-us/dnsql90/html/MARSinSQL05.asp?frame=true
--
-oj
"Thomas Brown" <ThomasBrown@.discussions.microsoft.com> wrote in message
news:B495E631-3D88-4880-B678-6E7F6DF9749C@.microsoft.com...
> When an ADO command does not exhaust the default result set and a second
> command is executed, the sqloledb provider automatically spawns a new
> session
> to execute the second command (using the default result set). My question
> is
> since command2 was definied on the connection (and prepared = true),
> should
> one be allowed to rebind a paramter on command2 and execute again? Doing
> so
> results in the following error -
> "Multiple-step OLD DB operator generated errors. Check each OLE DB status
> value, if available. No work was done."
> pseudo code example:
> cmd1.execute
> while not rs1.eof
> obtain row value, bind into cmd2
> cmd2.execute
> First execute works, second iteration fails.
> The solution is to shutdown cmd2 within the loop and recreate the command
> for each execution.
> Is this the expected behavior because of the inconsistent state for the
> command (having been spawned to a new session)?
> I know 2005 solves this issues w/ MARS.
> Thanks.
>

default result set semantics

When an ADO command does not exhaust the default result set and a second
command is executed, the sqloledb provider automatically spawns a new sessio
n
to execute the second command (using the default result set). My question is
since command2 was definied on the connection (and prepared = true), should
one be allowed to rebind a paramter on command2 and execute again? Doing so
results in the following error -
"Multiple-step OLD DB operator generated errors. Check each OLE DB status
value, if available. No work was done."
pseudo code example:
cmd1.execute
while not rs1.eof
obtain row value, bind into cmd2
cmd2.execute
First execute works, second iteration fails.
The solution is to shutdown cmd2 within the loop and recreate the command
for each execution.
Is this the expected behavior because of the inconsistent state for the
command (having been spawned to a new session)?
I know 2005 solves this issues w/ MARS.
Thanks.Correct. MARS in yukon is designed to solve this.
[url]http://msdn.microsoft.com/library/en-us/dnsql90/html/MARSinSQL05.asp?frame=true[/u
rl]
-oj
"Thomas Brown" <ThomasBrown@.discussions.microsoft.com> wrote in message
news:B495E631-3D88-4880-B678-6E7F6DF9749C@.microsoft.com...
> When an ADO command does not exhaust the default result set and a second
> command is executed, the sqloledb provider automatically spawns a new
> session
> to execute the second command (using the default result set). My question
> is
> since command2 was definied on the connection (and prepared = true),
> should
> one be allowed to rebind a paramter on command2 and execute again? Doing
> so
> results in the following error -
> "Multiple-step OLD DB operator generated errors. Check each OLE DB status
> value, if available. No work was done."
> pseudo code example:
> cmd1.execute
> while not rs1.eof
> obtain row value, bind into cmd2
> cmd2.execute
> First execute works, second iteration fails.
> The solution is to shutdown cmd2 within the loop and recreate the command
> for each execution.
> Is this the expected behavior because of the inconsistent state for the
> command (having been spawned to a new session)?
> I know 2005 solves this issues w/ MARS.
> Thanks.
>