Re: Command object and batching stored procedure calls

Michael Viking wrote:
I'm using SQL Server. For example, the books online do not list a
semi-colon as a delimiter in the help for the 'CommandText' property
of the ADO Command object. For example here:
http://msdn2.microsoft.com/en-us/library/ms675958(VS.85).aspx. What
should I be reading if not that?

First off, you are reading the ADO documentation: you should be reading the
SQL Server documentation.

As is said on the page you cited, the string assigned to the CommandText
property should be "any other type of command statement recognized by the
provider", i.e., a properly formatted statement that will be properly parsed
and executed by the target database. In SQL Server, a semicolon is the
standard statement delimiter. If you are using SQL 2005, and you have
installed BOL, see this
page:ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/84a1510d-7cbe-4bd1-a328-b873ee1bbe5f.htm.
If you were using a different database, the delimiter might be different.
Some databases, such as Jet (Access), don't support batched statements at
all.


22 separate statements is the most I've been able to get in one
CommandText, but I've seen as few as 15 be the maximum. It doesn't
seem to be related exactly to the length of CommandText, either,
because I've tried cutting them off by string length. As I've said,
it appears to be magic formula. I'd appreciate it if you could point
me to the Books Online where it's documented how many can be sent via
a Command object, or what the maximum length is or something.

I've never looked because it's nothing I have ever considered doing.
However, on this page
(ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/13e95046-0e76-4604-b561-d1a74dd824d7.htm),
you can see that the maximum batch size is "65,536 * Network Packet Size".
It does not sound from your description that you are getting anywhere near
this limit. What error are you getting that makes you think you've exceeded
a limit?

See below about telling ADO that you are not expecting records to be
returned by your batch's execution. Maybe that is related to your symptom.


Perhaps ADO isn't the best tool...If I need to run a stored procedure
on the DB every time a line of data comes in over a port, what do you
recommend? Naively it's safe and easy to use a command object and
execute the stored procedure every time a line comes in. Easy to
understand, program and it's safe. Performance: dismal.

One way to improve performance using the Command object is to specify
adExecuteNoRecords in the <options> argument when calling the Execute
method. If you do not specify that, ADO, by default, creates a recordset
object behind the scenes to receive any resultsets returned from the
execution. You need to tell ADO that you are not getting any records back.

Obvious
answer: queue them up somehow and batch process them. I'm posting to
get ideas...What's the best tool?

This is not a situation I've ever encountered; however ...

You might consider writing the data to a csv file, which can be periodically
imported into SQL via bcp, DTS or SSIS (... what version of SQL are you
using?), then a T-SQL process can loop through the data, using it to run the
stored procedure.

Another option is to go to a level below ADO and use OLE DB directly. See
http://msdn2.microsoft.com/en-us/library/ms722784(VS.85).aspx

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"


.

Relevant Pages

  • Re: Bill Vaughns "ADO Command Strategies" from 10/2000
    ... Middle tier components are now pre-compiled so the one-time cost of building a Command object is miniscule when compared to using Refresh to get the server to return metadata to ADO so it can build them. ... The ADO Command object's Parameters collection manages input, output, ...
    (microsoft.public.data.ado)
  • RE: Cursors with ADO
    ... Take a look at the Parameters collection in the ADO Command object. ... I would start with the BOL and look up the ADO Parameter object. ... > With cmd1 ...
    (microsoft.public.vb.database.ado)
  • Re: Problems with ADO Interop and dBASE file
    ... All the hits I found on this error relate to people using ADO.NET, not ADO, so hopefully someone can help me. ... recs, ExecuteOptionEnum.adExecuteNoRecords); ... // YEAR must be escaped because apparently it's a reserved word in dBASE. ... I'd use Profiler to see the exact command that was being passed and diagnose problems from there. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: "Insert Into tbl1 Values(" & dynamicParams & ")" ?
    ... I am familiar with Command ... that I am using com ADO in a Non ... Command object that I wasn't familiar with before I start ... >> I would like to make this dynamic rather than hardcoding ...
    (microsoft.public.dotnet.languages.vb)
  • Re: ADOCommand and grave accent problem
    ... problem - Paolo has spotted the solution. ... a parameterised command I do not know. ... >> when using an ADO Command, and not when using an ADO Connection object. ...
    (borland.public.delphi.database.ado)