SQL Server - Execute a store procedure asynchronously from Excel
Set cmd = New ADODB.Command
cmd.ActiveConnection = Cn
cmd.CommandText = "TRP.FFS_Contracts_process
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"
cmd.ActiveConnection = Cn
cmd.CommandText = "TRP.FFS_Contracts_process
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"
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Would anyone please help me with my question above?
what have you done of the 2 comments?
ASKER
You asked me to combine the three queries in one command:
cmd.CommandText = "exec TRP.FFS_Contracts_process_
"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.
cmd.CommandText = "exec TRP.FFS_Contracts_process_
"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.
since it is a single command, the 3 statements will run one after the other.
try removing your adAsyncExecute
try removing your adAsyncExecute
ASKER
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,
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER