Synchronizing stored procedures

[Ralph Cramden]

I'm writing a VB6 app which calls several stored procedures on my SQL
Server DB.

The first stored procedure must complete its inserts before the second
stored procedure can query the modified table for its results. My
problem is that
the second stored procedure occasionally returns a different result
set, acting as if the first stored procedure didn't complete (or
didn't run).

So how do I ensure (or test/poll?) that the first stored procedure
runs to completion before returning control back to my VB app?

Thanks in advance,

Ralph

[Ross Presser]

Re: Synchronizing stored procedures

cramden15@hotma il.com (Ralph Cramden) wrote in
news:e2259dcd.0 411091616.14e2f 190@posting.goo gle.com:
[color=blue]
> I'm writing a VB6 app which calls several stored procedures on my SQL
> Server DB.
>
> The first stored procedure must complete its inserts before the second
> stored procedure can query the modified table for its results. My
> problem is that
> the second stored procedure occasionally returns a different result
> set, acting as if the first stored procedure didn't complete (or
> didn't run).
>
> So how do I ensure (or test/poll?) that the first stored procedure
> runs to completion before returning control back to my VB app?
>
> Thanks in advance,
>
> Ralph
>[/color]

Might it make sense to combine your procedures? Or perhaps create a third
stored procedure that calls the first then the second in sequence?

[Erland Sommarskog]

Re: Synchronizing stored procedures

[posted and mailed, please reply in news]

Ralph Cramden (cramden15@hotm ail.com) writes:[color=blue]
> I'm writing a VB6 app which calls several stored procedures on my SQL
> Server DB.
>
> The first stored procedure must complete its inserts before the second
> stored procedure can query the modified table for its results. My
> problem is that
> the second stored procedure occasionally returns a different result
> set, acting as if the first stored procedure didn't complete (or
> didn't run).
>
> So how do I ensure (or test/poll?) that the first stored procedure
> runs to completion before returning control back to my VB app?[/color]

Do you call the procedures from different threads or asynchronously? That
would be the only way for the second procedure before the first have
completed. In both cases, you would need synchronization in the client
code.

I suspect that your real problem is something else. That is, you are calling
the two procedures sequentially from the same thread, but there is some
flaw of logic, causing the incorrect result. It could also be that the
first procedure runs into an error condition, which you fail to handle
properly in the application code.

That is, if you are calling stored procedures in the plain vanilla way,
you don't have to worry about synchronization , since all calls to SQL
Server are synchronous.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Ralph Cramden]

Re: Synchronizing stored procedures

My fear is that I'm calling the stored procedures asynchronously
and I don't want to.

The Project properties dialog box shows that the app is a standard
EXE project type (threading model is grayed out (so I'm assuming
a single thread)).

The SQL server though is a multiple CPU(4) platform would that
make a difference?

How would I synchronize the client code?



Erland Sommarskog <esquel@sommars kog.se> wrote in message news:<Xns959D93 46C8EF1Yazorman @127.0.0.1>...[color=blue]
> [posted and mailed, please reply in news][/color]
[color=blue]
> Do you call the procedures from different threads or asynchronously? That
> would be the only way for the second procedure before the first have
> completed. In both cases, you would need synchronization in the client
> code.
>
> I suspect that your real problem is something else. That is, you are calling
> the two procedures sequentially from the same thread, but there is some
> flaw of logic, causing the incorrect result. It could also be that the
> first procedure runs into an error condition, which you fail to handle
> properly in the application code.
>
> That is, if you are calling stored procedures in the plain vanilla way,
> you don't have to worry about synchronization , since all calls to SQL
> Server are synchronous.[/color]

[Erland Sommarskog]

Re: Synchronizing stored procedures

Ralph Cramden (cramden15@hotm ail.com) writes:[color=blue]
> My fear is that I'm calling the stored procedures asynchronously
> and I don't want to.[/color]

I don't want to scorn you, but if you don't know if you are writing
a synchronous application or not, you are a very confused VB programmer.
(And if you really want certainty, you would have to ask in a Visual
Basic newsgroup; that's not really an SQL Server question.)
[color=blue]
> The SQL server though is a multiple CPU(4) platform would that
> make a difference?[/color]

That has nothing to do with it.
[color=blue]
> How would I synchronize the client code?[/color]

It does not sound like you would need to. If your code looks like this:

Set cmd = new ADODB.Command
cmd.ActiveConne ction = cnn
cmd.CommandType = adCmdStoredProc
cmd.CommandText = my_first_proc
cmd.Execute
Set cmd = Nothing
Set cmd = new ADODB.Command
cmd.ActiveConne ction = cnn
cmd.CommandType = adCmdStoredProc
cmd.CommandText = my_second_proc
cmd.Execute
Set cmd = Nothing

There is nothing to synchronize. cmd.Execute blocks until SQL Server
has completed execution.

If you are using separate threads, or the synchronous capabilities of
ADO and issue the commands on two separate connection, you have indeed
quite a few interesting problems. Then again, that's not a path you
should not take if you don't know what you are doing.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Ryan]

Re: Synchronizing stored procedures

A DTS package would do this for you really easily.

[Erland Sommarskog]

Re: Synchronizing stored procedures

Ryan (ryanofford@hot mail.com) writes:[color=blue]
> A DTS package would do this for you really easily.[/color]

Eh? I don't know much about DTS, but there is actually one thing I've
used DTS for, and that is to test the multi-threading capabilities of a
Perl module for SQL Server access. Because in DTS, things really happens
in parallel. What implications that has for what DTS is used for, I don't
really know, but I have noticed that you can arrange tasks, so that task B
does not start before task A has completed, but you can also arrange for
A and B to be in parallel if you like.

Now, whether this has any relevance for Ralph's question, I don't know,
but my guess it has not.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Ryan]

Re: Synchronizing stored procedures

Erland,

My understanding of the problem was that there was a need to run two
SP's. The second SP needs to run after the first has completed. Doing
this in a series of stored procedures or an SP that runs both could
force the execution plan to determine that it's better to run the second
SP before the first (hence the problem).

I was suggesting the DTS as an option as you can make a stored procedure
'wait' until the sucess of a prior SP. In it's most simplistic terms,
this should solve the problem. It is really good at running things in
parallel as well, but this may work in this case.

I would have tried creating a small DTS package that runs the second SP
on success of the first task. This would take a matter of minutes to do
and would not require changing the SP's.

I may well be wrong, but given that it would take a short time to set up
and test, I felt it might be worth a look. Hopefully I have not missed
the point.

Ryan

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Erland Sommarskog]

Re: Synchronizing stored procedures

Ryan (anonymous@devd ex.com) writes:[color=blue]
> My understanding of the problem was that there was a need to run two
> SP's. The second SP needs to run after the first has completed. Doing
> this in a series of stored procedures or an SP that runs both could
> force the execution plan to determine that it's better to run the second
> SP before the first (hence the problem).
>
> I was suggesting the DTS as an option as you can make a stored procedure
> 'wait' until the sucess of a prior SP. In it's most simplistic terms,
> this should solve the problem. It is really good at running things in
> parallel as well, but this may work in this case.[/color]

That's correct, that DTS gives you a synchronization mechanism. (Hey,
that is as much I know about DTS.)

However, you can achieve the same with:

Set cmd = new ADODB.Command
cmd.ActiveConne ction = cnn
cmd.CommandType = adCmdStoredProc
cmd.CommandText = my_first_proc
cmd.Execute
Set cmd = Nothing
Set cmd = new ADODB.Command
cmd.ActiveConne ction = cnn
cmd.CommandType = adCmdStoredProc
cmd.CommandText = my_second_proc
cmd.Execute
Set cmd = Nothing

Throwing in DTS in a VB app to solve something which works out of the
box in VB appears to be a huge overkill to me.

(And if your VB app is multi-threaded or you use asynchronous calls,
DTS would still be a huge overkill, give that implmentation of
thread-synchronization or handling of asynchronous calls is not really
rocket science, even if it goes beyond what a plain-vanilla does.)


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Ryan]

Re: Synchronizing stored procedures

Fair enough :-)

Erland Sommarskog <esquel@sommars kog.se> wrote in message news:<Xns959EC2 7A5EC36Yazorman @127.0.0.1>...[color=blue]
> Ryan (anonymous@devd ex.com) writes:[color=green]
> > My understanding of the problem was that there was a need to run two
> > SP's. The second SP needs to run after the first has completed. Doing
> > this in a series of stored procedures or an SP that runs both could
> > force the execution plan to determine that it's better to run the second
> > SP before the first (hence the problem).
> >
> > I was suggesting the DTS as an option as you can make a stored procedure
> > 'wait' until the sucess of a prior SP. In it's most simplistic terms,
> > this should solve the problem. It is really good at running things in
> > parallel as well, but this may work in this case.[/color]
>
> That's correct, that DTS gives you a synchronization mechanism. (Hey,
> that is as much I know about DTS.)
>
> However, you can achieve the same with:
>
> Set cmd = new ADODB.Command
> cmd.ActiveConne ction = cnn
> cmd.CommandType = adCmdStoredProc
> cmd.CommandText = my_first_proc
> cmd.Execute
> Set cmd = Nothing
> Set cmd = new ADODB.Command
> cmd.ActiveConne ction = cnn
> cmd.CommandType = adCmdStoredProc
> cmd.CommandText = my_second_proc
> cmd.Execute
> Set cmd = Nothing
>
> Throwing in DTS in a VB app to solve something which works out of the
> box in VB appears to be a huge overkill to me.
>
> (And if your VB app is multi-threaded or you use asynchronous calls,
> DTS would still be a huge overkill, give that implmentation of
> thread-synchronization or handling of asynchronous calls is not really
> rocket science, even if it goes beyond what a plain-vanilla does.)[/color]