Solved

SQL Server - adAsyncExecute

Posted on 2013-10-28
12
499 Views
Last Modified: 2014-03-12
I'm using the following code in Excel to run a long store procedure from the Sql server, but I notice that it didn't execute the whole procedure, only the first portion of the store procedure got executed, then I think it stop when the Sub cmdTRP_Upload_Click() end. Can you please let me know how to fix this issue?


Sub cmdTRP_Upload_Click()

       Set cmd = New ADODB.Command
        cmd.ActiveConnection = Cn

 If Me.chkPartial_proj Then
   
        cmd.CommandText = "exec TRP.FFS_Contracts_process_v1 " + "Update TRP.Param set F_Proj_type = 'P' " + "exec TRP.sp_FFS_Projection "
        cmd.CommandTimeout = 1200               ' 20 minutes
        cmd.CommandType = adCmdText
        cmd.Execute , , adAsyncExecute

        MsgBox ("Loaded/Processed & Ran partial projection!")
 Else
        cmd.CommandText = "TRP.FFS_Contracts_process_v1"
        cmd.CommandTimeout = 1200               ' 20 minutes
        cmd.CommandType = adCmdStoredProc
        cmd.Execute , , adAsyncExecute

        MsgBox ("Only loaded/processed - No projection")
 End If

End Sub
0
Comment
Question by:HNA071252
  • 7
  • 5
12 Comments
 
LVL 22

Expert Comment

by:Nico Bontenbal
Comment Utility
<< I think it stop when the Sub cmdTRP_Upload_Click() end>>
You can check if that's true by waiting a long time before clicking away the messagebox. If this helps it is probably because cmd variable is no longer available. Try to dim it as a module var. Put the line
dim cmd as ADODB.Command

Open in new window

0
 

Author Comment

by:HNA071252
Comment Utility
I do have this line at the top: dim cmd as ADODB.Command. My codes is very long, so I didn't copy the whole thing in here and I accidentally left this line out the first time.

Yeah, I was holding on before clicking away the msgbox then I have no problem, so I checked to make sure the procedure completed running before clicking on the msgbox.

But I do not want the user to hang on like this, they should be able to click the msgbox away then move on and let the procedure running in the background until it done. Can someone please give me advice?
0
 
LVL 22

Assisted Solution

by:Nico Bontenbal
Nico Bontenbal earned 500 total points
Comment Utility
Strange. I tried to reproduce your problem with a simple test. On SQL Server I created this:
create table TestAsyncData (Time varchar(10))
go
create PROCEDURE TestAsync
	@Time varchar(10)
AS
BEGIN
    delete from TestAsyncData
    WAITFOR DELAY '00:00:05'
    insert into TestAsyncData (Time) values (@Time)
END

Open in new window

In Excel I added this code to a normal module:
Option Explicit

Sub TestAsync()
    Dim con As New ADODB.Connection
    Dim cmd As ADODB.Command
    Set cmd = New ADODB.Command
    
    con.ConnectionString = "Provider=SQLOLEDB;Data Source=(local)\sqlexpress2008r2;Initial Catalog=FS;Integrated Security=SSPI"
    con.Open
    
    cmd.ActiveConnection = con
    cmd.CommandText = "exec TestAsync '" & Format(Now, "hh:nn:ss") & "'"
    cmd.CommandTimeout = 1200
    cmd.CommandType = adCmdText
    cmd.Execute , , adAsyncExecute
    MsgBox "Finished"
End Sub

Open in new window

When I execute this code in Excel the current time shows up in the table, if I wait for 5 seconds before clicking on the 'Finished' message. When I close it before that the table remains empty. So this looks like your problem. But when I change the module to:
Option Explicit
Global cmd As ADODB.Command

Sub TestAsync()
    Dim con As New ADODB.Connection
    
    Set cmd = New ADODB.Command
    
    con.ConnectionString = "Provider=SQLOLEDB;Data Source=(local)\sqlexpress2008r2;Initial Catalog=FS;Integrated Security=SSPI"
    con.Open
    
    cmd.ActiveConnection = con
    cmd.CommandText = "exec TestAsync '" & Format(Now, "hh:nn:ss") & "'"
    cmd.CommandTimeout = 1200
    cmd.CommandType = adCmdText
    cmd.Execute , , adAsyncExecute
    MsgBox "Finished"
End Sub

Open in new window

The current time shows up in the table after 5 seconds, even when I immediately click on the 'Finished' message.
In the immediate window I can check
?cmd.State

Open in new window

It returns 4 for the first 5 seconds. Then it returns 0.
I'm using Excel 2007 with a reference to 'ADO 2.8'. Can you reproduce the results in my test in your environment? If not then there is some difference causing the problem. You could also start by using
Global cmd As ADODB.Command

Open in new window

in your code. This way you can check the cmd.State.
Maybe your procedure runs more than 20 minutes. Or maybe you can see this way it runs for 10 minutes and then stops, without executing all the code. In that case it might stop because of an error.
0
 

Author Comment

by:HNA071252
Comment Utility
When I added these 2 lines in the codes:

Option Explicit
Global cmd As ADODB.Command

I got this compile error says "Constants, fixed-length strings, arrays, user-defined types and Declare statements not allowed as Public members of object modules"

Please let me know how to fix this.
0
 
LVL 22

Expert Comment

by:Nico Bontenbal
Comment Utility
Then I suppose your code is in Sheet (Sheet1 for example) or ThisWorkbook module. You need to add a 'normal' module to the VBA project. You can use the menu option Insert, Module to add one. Then put your code in this module. But replace the line:
Sub cmdTRP_Upload_Click()
with
Sub Contracts_process()

Change your original code to:
Sub cmdTRP_Upload_Click()
  Contracts_process
End Sub

Open in new window


Another way to solve this is probably this:
Option Explicit
dim cmd As ADODB.Command
But then you won't be able to use cmd in the immediate window.

You can also change your code to something like this:
Option Explicit
Dim WithEvents Cn As ADODB.Connection
Dim cmd As ADODB.Command

Private Sub Cn_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)
    MsgBox "Execution complete"
End Sub

Sub cmdTRP_Upload_Click()

       Set cmd = New ADODB.Command
        cmd.ActiveConnection = Cn

 If Me.chkPartial_proj Then
    
        cmd.CommandText = "exec TRP.FFS_Contracts_process_v1 " + "Update TRP.Param set F_Proj_type = 'P' " + "exec TRP.sp_FFS_Projection "
        cmd.CommandTimeout = 1200               ' 20 minutes
        cmd.CommandType = adCmdText
        cmd.Execute , , adAsyncExecute

        MsgBox ("Loaded/Processed & Ran partial projection!")
 Else
        cmd.CommandText = "TRP.FFS_Contracts_process_v1"
        cmd.CommandTimeout = 1200               ' 20 minutes
        cmd.CommandType = adCmdStoredProc
        cmd.Execute , , adAsyncExecute

        MsgBox ("Only loaded/processed - No projection")
 End If

End Sub

Open in new window

This way a message is displayed when the execution is complete. See http://msdn.microsoft.com/en-us/library/windows/desktop/ms676183(v=vs.85).aspx for more information. This way the user knows not to close the Excel file before the execution is finished. It's probably also possible to display a warning to the user when he tries to close the file while the command is still executing.
0
 
LVL 22

Expert Comment

by:Nico Bontenbal
Comment Utility
I just noticed the 'Me.chkPartial_proj' line in your code. So you probably run this code from a UserForm module. Then the execution of the stored procedure is probably stopped when the form is closed. My first solution in the previous post will probably work in this situation. The other two probably won't. But there still is a way to get the WithEvents thing working in this situation.
0
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 

Author Comment

by:HNA071252
Comment Utility
Yes I run this code from a UserForm module in Excel.

I changed my codes to what you recommended:

Sub cmdTRP_Upload_Click()
  Contracts_process
End Sub

and this:

Option Explicit
Dim WithEvents Cn As ADODB.Connection
Dim cmd As ADODB.Command

Private Sub Cn_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)
    MsgBox "Execution complete"
End Sub

Sub cmdTRP_Upload_Click()

       Set cmd = New ADODB.Command
        cmd.ActiveConnection = Cn

 If Me.chkPartial_proj Then
   
        cmd.CommandText = "exec TRP.FFS_Contracts_process_v1 " + "Update TRP.Param set F_Proj_type = 'P' " + "exec TRP.sp_FFS_Projection "
        cmd.CommandTimeout = 1200               ' 20 minutes
        cmd.CommandType = adCmdText
        cmd.Execute , , adAsyncExecute

        MsgBox ("Loaded/Processed & Ran partial projection!")
 Else
        cmd.CommandText = "TRP.FFS_Contracts_process_v1"
        cmd.CommandTimeout = 1200               ' 20 minutes
        cmd.CommandType = adCmdStoredProc
        cmd.Execute , , adAsyncExecute

        MsgBox ("Only loaded/processed - No projection")
 End If

End Sub

but I'm still having problem that the procedure is stop running in the middle on the larger process files. Please let me know how can I make that my procedure completed running? Thanks.
0
 
LVL 22

Expert Comment

by:Nico Bontenbal
Comment Utility
The simple answer is: the cmd variable is closed when the userform is closed. Initialize the cmd variable in a seperate module as
Global cmd As ADODB.Command
I think this will solve your problem.

A more advanced answer is in the attached example. In this example the code is in the ThisWorkbook module. This enables the use of the 'WithEvents'.

The modAsync module contains a macro that opens the userform. The userform only has one button that executes the code in the ThisWorkbook module. I suggest you first test this code with the stored procedure 'TestAsync' from one of my previous post. When this example is working for you, you can change the code so it uses your stored procedures. Experiment with the delay in the stored procedure also, so you can check what happens when the procedure runs longer than the timeout you set for example.
0
 

Author Comment

by:HNA071252
Comment Utility
Where's the attached example? Did you forget to attach a file?
0
 
LVL 22

Expert Comment

by:Nico Bontenbal
Comment Utility
I'm afraid I did. Sorry.
Async.xlsm
0
 

Author Comment

by:HNA071252
Comment Utility
There's nothing in the Async.xlsm you attached. It's empty?
0
 
LVL 22

Accepted Solution

by:
Nico Bontenbal earned 500 total points
Comment Utility
The Excel sheets are empty but the VBA project isn't. It has code in the ThisWorkbook module, the frmAsync module and in the modAsync module. You can run the 'OpenAsync' macro to open the test form.
Maybe your macro security settings prevent you from opening the macro's in the Async.xlsm.
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now