[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1088
  • Last Modified:

SQL Server - Execute a store procedure asynchronously from Excel

Set cmd = New ADODB.Command
        cmd.ActiveConnection = Cn
       
        cmd.CommandText = "TRP.FFS_Contracts_process_v1"
        cmd.CommandType = adCmdStoredProc
        cmd.Execute , , adAsyncExecute
             
        cmd.CommandText = "Update TRP.Param set F_Proj_type = 'P'"
        cmd.CommandType = adCmdText
        cmd.Execute
       
        cmd.CommandText = "TRP.sp_FFS_Projection"
        cmd.CommandType = adCmdStoredProc
        cmd.Execute , , adAsyncExecute

Could you please let me know why I'm getting an error message on this line and how to fix it?
        cmd.CommandText = "Update TRP.Param set F_Proj_type = 'P'"

Error message says "Operation cannot be performed while executing asynchronously"
0
HNA071252
Asked:
HNA071252
  • 4
  • 4
2 Solutions
 
Éric MoreauSenior .Net ConsultantCommented:
a single connection cannot execute multiple command at the same time.

why don't you just send the 3 queries at the same time?

        cmd.CommandText = "exec TRP.FFS_Contracts_process_v1 " +
                                              "Update TRP.Param set F_Proj_type = 'P' " +
                                              "exec TRP.sp_FFS_Projection "
        cmd.CommandType = adCmdText
        cmd.Execute , , adAsyncExecute
0
 
HNA071252Author Commented:
with combining the 3 queries in one command, is it execute one after another or simultaneously? because I would need the first one done, before the 2nd, then the 3rd.
0
 
HNA071252Author Commented:
Would anyone please help me with my question above?
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Éric MoreauSenior .Net ConsultantCommented:
what have you done of the 2 comments?
0
 
HNA071252Author Commented:
You asked me to combine the three queries in one command:

        cmd.CommandText = "exec TRP.FFS_Contracts_process_v1 " +
                                              "Update TRP.Param set F_Proj_type = 'P' " +
                                              "exec TRP.sp_FFS_Projection "
        cmd.CommandType = adCmdText
        cmd.Execute , , adAsyncExecute

and I asked if is it execute one after another or simultaneously? Although I didn't get Error message says "Operation cannot be performed while executing asynchronously", but it didn't execute all three commands, it didn't continue on executing it in the Server but it stops when I closed Excel.
0
 
Éric MoreauSenior .Net ConsultantCommented:
since it is a single command, the 3 statements will run one after the other.

try removing your adAsyncExecute
0
 
HNA071252Author Commented:
If I remove adAsyncExecute, then the command cmd.Execute is hanging in Excel until it finish which can be as long as an hour and I don't want to wait in Excel, I wanted to continue executing in the Server even when I'm done with Excel but I couldn't figure how to make it work,
0
 
Éric MoreauSenior .Net ConsultantCommented:
the execution stops because the connection is closed. so you need to run the command from a different context. one way of achieving this is to schedule your commands.

check the sample at http://stackoverflow.com/questions/287060/scheduled-run-of-stored-procedure-on-sql-server
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now