run stored procedure asynchronously *Resolved*

[TysonLPrice]

I made a post yesterday called "Application Busy" asking about how to handle a stored procedure that runs for up to twenty minutes locking up my application. Someone I was talking to about it mentioned I could run stored procedure asynchronously from an ActiveX exe and another said just run it run asynchronously.

I Googled on it and it looks like it has potential but everything I saw was about .NET.

Can I use the method in 6.0? Does any one have a sample project or could post some code to get me started?

[Devion]

ActiveX Exe... or DLL... As long as you execute the procedure/sub/function which is late binded it kinda multithreads.

Dim obj as Object
set obj = CreateObject("MyApp.MyClass")
obj.InitApplication

Something like that; In the class inside the DLL or EXE just activate a timer and when the timer activates just disable it again but initiate the sub/function that does the 20 minute run.

[TysonLPrice]

ActiveX Exe... or DLL... As long as you execute the procedure/sub/function which is late binded it kinda multithreads.

Dim obj as Object
set obj = CreateObject("MyApp.MyClass")
obj.InitApplication

Something like that; In the class inside the DLL or EXE just activate a timer and when the timer activates just disable it again but initiate the sub/function that does the 20 minute run.

That really doesn't tell me anything. Could you dummy it up a bit for me. Do you have an example that works?

[Devion]

Make an ActiveX DLL Project...

- Give it a projectname...
- Name the class..
- Add a Form (Very easy) or Timer class.
- The class needs to have a sub or function called 'InitApplication' if you want to follow the code above.
- The InitApplication function or sub calls the timer on either the form or the timer class and activates it.
- Disable the timer before you start the real code
- The timer then when it triggers will start your real code that takes 20 minutes.

The reason why you have to do this is because the timer is self-driven and not called from your main code which will continue after initApplication with it's own code.

Hope that dummies it up a bit

[yrwyddfa]

You might like to look at using asynchronous COM+. You'd need to specify the interface in a typelib, but that shouldn't be too much hassle.

[brucevde]

You can execute ADO commands asynchronously.

How are you executing the query now?

Specify adAsyncExecute in the Options agrument of the Connection.Execute or Command.Execute method. Something like the following might work. I don't have any long running queries to be able to test properly.

Private WithEvents db As ADODB.Connection
 
Private Sub Command1_Click()
 
db.Open "provider=sqloledb;data source=trixie;initial catalog=northwind;integrated security=sspi"
 
db.Execute "SprocName", , adCmdStoredProc Or adAsyncExecute
 
End Sub
 
Private Sub db_ExecuteComplete(ByVal RecordsAffected As Long, ByVal pError As ADODB.Error, _
    adStatus As ADODB.EventStatusEnum, ByVal pCommand As ADODB.Command, _
    ByVal pRecordset As ADODB.Recordset, ByVal pConnection As ADODB.Connection)
 
     'code to execute when procedure has completed.
 
End Sub

[TysonLPrice]

I'm using this currently:

sConnString = "Server=COMMONRPTSQL;Database=TEAMCMI;Driver=SQL Server;Trusted_Connection=Yes;DSN=COMMONRPTSQL"

sSql = "exec rptAON"

With Cmd
.CommandType = adCmdText
.CommandText = sSql
.ActiveConnection = conn
.CommandTimeout = 0
DoEvents
Set rsAONReport = .Execute
DoEvents
End With

I tried plugging your code into a test form and I get the error:

"Object does not source automation events"

What you posted seems like what I want. Do you know what may be causing that error?

[TysonLPrice]

Thanks everyone. I found a good example in the help library. I'm posting it in case someone searchs on this topic.

Add the Microsoft ActiveX Data Objects 2.7 (that's what I picked). Then you should just be able to paste this in:

'BeginStateVB
 
    'To integrate this code
    'replace the data source and initial catalog values
    'in the connection string
 
Public Sub Main()
    On Error GoTo ErrorHandler
 
    Dim Cnxn1 As ADODB.Connection
    Dim Cnxn2 As ADODB.Connection
    Dim cmdChange As ADODB.Command
    Dim cmdRestore As ADODB.Command
    Dim strCnxn As String
    Dim strSQL As String
    
    ' Open two asynchronous connections, displaying
    ' a message while connecting
    Set Cnxn1 = New ADODB.Connection
    Set Cnxn2 = New ADODB.Connection
    strCnxn = "Provider='SQLOLEDB';Data Source='CMISQL';" & _
        "Initial Catalog='Pubs';Integrated Security='SSPI';"
       
    Cnxn1.Open strCnxn, , , adAsyncConnect
    Do Until Cnxn1.State <> adStateConnecting
       Debug.Print "Opening first connection...."
    Loop
    
    Cnxn2.Open strCnxn, , , adAsyncConnect
    Do Until Cnxn2.State <> adStateConnecting
       Debug.Print "Opening second connection...."
    Loop
    
    ' Create two command objects
    Set cmdChange = New ADODB.Command
    cmdChange.ActiveConnection = Cnxn1
    strSQL = "UPDATE Titles SET type = 'self_help' WHERE type = 'psychology'"
    cmdChange.CommandText = strSQL
    
    Set cmdRestore = New ADODB.Command
    cmdRestore.ActiveConnection = Cnxn2
    strSQL = "UPDATE Titles SET type = 'psychology' WHERE type = 'self_help'"
    cmdRestore.CommandText = strSQL
    cmdChange.Execute , , adAsyncExecute
    ' Executing the commands, displaying a message
    ' while they are executing
    
    Do Until cmdChange.State <> adStateExecuting
       Debug.Print "Change command executing...."
    Loop
    
    cmdRestore.Execute , , adAsyncExecute
    Do Until cmdRestore.State <> adStateExecuting
       Debug.Print "Restore command executing...."
    Loop
 
    ' clean up
    Cnxn1.Close
    Cnxn2.Close
    Set Cnxn1 = Nothing
    Set Cnxn2 = Nothing
    Exit Sub
    
ErrorHandler:
    ' clean up
    If Not Cnxn1 Is Nothing Then
        If Cnxn1.State = adStateOpen Then Cnxn1.Close
    End If
    Set Cnxn1 = Nothing
    
    If Not Cnxn2 Is Nothing Then
        If Cnxn2.State = adStateOpen Then Cnxn2.Close
    End If
    Set Cnxn2 = Nothing
    
    If Err <> 0 Then
        MsgBox Err.Source & "-->" & Err.Description, , "Error"
    End If
End Sub
'EndStateVB