Stored procedure not synchronous

[DeanReedy]

I have a stored procedure and I would like it run synchronously. I runs asynchronously and I cannot trap when it completes.

The following is the code I am using:
Dim objCmd As New ADODB.Command
objCmd.CommandTimeout = 180
objCmd.CommandText = "Test_Load" 'Name of stored procedure
objCmd.CommandType = adCmdStoredProc
objCmd.ActiveConnection = adoConn
Dim objRs As ADODB.Recordset
Set objRs = objCmd.Execute


I tried:
While objCmd.State = adStateExecuting
DoEvents
Debug.Print "Executing!"
Wend

than I tried:
While objRs.State = adStateExecuting
DoEvents
Debug.Print "Executing!"
Wend


Note: This SP loads three text files into MS SQL 2000 using DTS and it loads all three files successfully, but it returns control to VB after the first files is loaded.

Any ideas?

 

[genomon]

Not real sure here, but doesn't DoEvents cause it to run asynchronously? Have you tried removing it?

 

[DougP]

maybe if you explain what you are after we can help
So you want the result from the last file in..?
Set objRs = objCmd.Execute


DougP, MCP, A+

 

[genomon]

Yup what Doug said......
Also here is what I was referring to earlier (from Access Help):
The DoEvents function returns an Integer representing the number of open
forms in stand-alone versions of Visual Basic, such as Visual Basic,
Professional Edition. DoEvents returns zero in all other applications.
DoEvents passes control to the operating system. Control is returned after
the operating system has finished processing the events in its queue and
all keys in the SendKeys queue have been sent.
DoEvents is most useful for simple things like allowing a user to cancel
a process after it has started, for example a search for a file. For
long-running processes, yielding the processor is better accomplished
by using a Timer or delegating the task to an ActiveX EXE component.
In the latter case, the task can continue completely independent of your
application, and the operating system takes case of multitasking and time
slicing.
Caution Any time you temporarily yield the processor within an event
procedure, make sure the procedure is not executed again from a different
part of your code before the first call returns; this could cause
unpredictable results. In addition, do not use DoEvents if other
applications could possibly interact with your procedure in unforeseen
ways during the time you have yielded control.

 

[ArtieChoke]

I suspect your DTS procedure is running the 3 file transfers in parallel. Note: DOEVENTS will NOT cause a stored procedure to become asynchronous - they are totally unrelated. Unless you specifically set up your connection to run asynchronously, the default is to return once the process is complete. That's why you won't get anything out of those loops, the database has already returned control back to the program after the execute method completes.

"I think we're all Bozos on this bus!" - Firesign Theatre