Batch processing of Stored Procedure

Hi,
I am trying to batch processing stored procedures with SQL 2000, MDAC
2.8.
All settings are default. SQLSetStmtAttr is called with
SQL_ATTR_PARAMSET_SIZE set to the number array elements. I am using
ODBC CALL escape sequence.
I noticed from SQL Profiler trace that the stored procedure is
executed as many
times as the elements in the array (e.g., in separate batches).
If I change the stored procedure call to a vanilla INSERT then all of
the INSERTs are performed in one batch.
Is there any extra settings to be done to execute the procedure in a
true batch execution mode rather than a simulated batch processing.
In the past someone complained about the same issue but the only
suggestion was to concatenate all stored procedure call strings into
one big string and then execute it in one batch. But then there is no
way to return the array of results or errors.
Any hints will be appreciated. Thanks.
KongThis is by design. Each stored procedure is executed within its own scope fo
r
each call.
-oj
http://www.rac4sql.net
"Kong Li" <likong@.email.com> wrote in message
news:9e121089.0403112016.372b4871@.posting.google.com...
> Hi,
> I am trying to batch processing stored procedures with SQL 2000, MDAC
> 2.8.
> All settings are default. SQLSetStmtAttr is called with
> SQL_ATTR_PARAMSET_SIZE set to the number array elements. I am using
> ODBC CALL escape sequence.
> I noticed from SQL Profiler trace that the stored procedure is
> executed as many
> times as the elements in the array (e.g., in separate batches).
> If I change the stored procedure call to a vanilla INSERT then all of
> the INSERTs are performed in one batch.
> Is there any extra settings to be done to execute the procedure in a
> true batch execution mode rather than a simulated batch processing.
> In the past someone complained about the same issue but the only
> suggestion was to concatenate all stored procedure call strings into
> one big string and then execute it in one batch. But then there is no
> way to return the array of results or errors.
> Any hints will be appreciated. Thanks.
> Kong|||Hi, oj,
Thanks for the reply. But according to MSDN
http://msdn.microsoft.com/library/d...br />
2msz.asp
It is possible to batch stored procedure calls.
I understand Microsoft manual page is not always correct :-) Just
want to make sure that it is indeed "by design" before I resort to
other methods.
Thanks again.
Kong
"oj" <nospam_ojngo@.home.com> wrote in message news:<egJEgLACEHA.684@.tk2msftngp13.phx.gbl>..
.
> This is by design. Each stored procedure is executed within its own scope
for
> each call.
> --
> -oj
> http://www.rac4sql.net
>
> "Kong Li" <likong@.email.com> wrote in message
> news:9e121089.0403112016.372b4871@.posting.google.com...|||Kong,
This statement implies that you do not have any control.
"The Microsoft SQL ServerT ODBC driver automatically batches stored
procedure calls to the server when appropriate."
I'll check to see if there is any and post back when I have an answer.
-oj
http://www.rac4sql.net
"Kong Li" <likong@.email.com> wrote in message
news:9e121089.0403121052.4137690b@.posting.google.com...
> Hi, oj,
> Thanks for the reply. But according to MSDN
>
http://msdn.microsoft.com/library/d..._6_035_2msz.asp[
color=darkred]
> It is possible to batch stored procedure calls.
> I understand Microsoft manual page is not always correct :-) Just
> want to make sure that it is indeed "by design" before I resort to
> other methods.
> Thanks again.
> Kong
>
> "oj" <nospam_ojngo@.home.com> wrote in message[/color]
news:<egJEgLACEHA.684@.tk2msftngp13.phx.gbl>...
scope for