Re: execute stored procedure asynchronously

JTL wrote:
I'm trying to asynchronously execute a stored procedure against SQL
Server from a VB6 client application and can't seem to make it work.
The error I'm getting is: "Operation cannot be performed while
connecting asynchronously". If I remove the "adAsyncConnect" option
from the connection string, the procedure runs fine-

Here is my sample db connection code:

sConnString = "Provider=SQLOLEDB;Data Source=myServer;Initial
Catalog=myDatabase"
conn.Open sConnString, "username", "password", adAsyncConnect

cmd.CommandType = adCmdStoredProc
cmd.CommandText = "spMyStoredProcedure"
cmd.Parameters.Append cmd.CreateParameter("param1", adVarChar,
adParamInput, 36, param1)
cmd.Parameters.Append cmd.CreateParameter("param2", adSmallInt,
adParamInput, , param2)
cmd.Execute


Did you instantiate your conn object using the WithEvents keyword? The
conn object needs to be a module-level variable.

"Asynchronous" means subsequent statements are executed without waiting
for the asynchronous statement to complete. Which means that you are
attempting to cass that Execute method before the connection is
connected. You might want to consider executing that command in the
connection's ConnectComplete event ...

Wait a minute ... you want to _execute the procedure_ asynchronously,
not _connect_ asynchronously! You need to remove the adSyncconnect
constant and use the adAsyncExecute option in the call to Execute to
make it execute asynchronously. Since it appears the proc is not
returning any records, you should also use adExecutenoRecords so ADO
does not waste CPU and resources creating an empty recordset object:

cmd.Execute ,,adAsyncExecute + adExecutenoRecords


Bob Barrows

PS. The ADO documentation can be found here:
http://msdn2.microsoft.com/en-us/library/ms675532.aspx
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.


.

Relevant Pages

  • [Full-Disclosure] Advanced usage of system() function.
    ... and call its arguments as a command for shell. ... as we can see we still didnt get what we want (typing exit ... Connection closed by foreign host. ... think what we want to execute. ...
    (Full-Disclosure)
  • Advanced usage of system() function.
    ... and call its arguments as a command for shell. ... as we can see we still didnt get what we want (typing exit we are ... Connection closed by foreign host. ... think what we want to execute. ...
    (Bugtraq)
  • Re: ADODB Command memory leak
    ... _ConnectionPtr conn = NULL; ... // Execute statement. ... > My logic is such that the connection to the database is not maintained ... Create/Append command parameters ...
    (microsoft.public.data.ado)
  • Re: Script Controlled Dynamic Task Execution
    ... My package had three connections to three ... and each had a transformation for itself. ... After posting, I just created three packages for each connection, and my ... >> need to execute one transformation on a text file, ...
    (microsoft.public.sqlserver.dts)
  • Re: execute stored procedure asynchronously
    ... Here is my sample db connection code: ... Did you instantiate your conn object using the WithEvents keyword? ... attempting to cass that Execute method before the connection is ... constant and use the adAsyncExecute option in the call to Execute to ...
    (microsoft.public.data.ado)