Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to ensure sequential command execution via SQL Query tables?
Hello,
I am making use of Query Table objects to do a number of things in sequential order, including the execution of several stored procedures on a remote server-db, and then the querying of a table produced by the tasks run by these stored procedures. The trouble is, I need to ensure that each stored procedure is executed in sequence, otherwise the tasks fail and I get errors. To clarify, I need sproc1 to run and complete its tasks before sproc2, and the same with sproc2 before launching sproc3, and so on. I use a simple string along the lines of: "exec sproc1" for the .sql argument of the Query Table to execute the stored procedures. To illustrate, this is a sample execution outline: Using MyQueryTable, I do this: Step 1 - With MyQueryTable ..Sql = "exec sproc1" ..Refresh End With Step 2 - With MyQueryTable ..Sql = "exec sproc2" ..Refresh End With Step 3 - With MyQueryTable ..Sql = "exec sproc3" ..Refresh End With Step 4 - With My QueryTable ..Sql = "select * from mytable" ..Refresh End With For stored procedures 1-3 above, #2 depends on the tasks completed by #1 and #3 must follow on the tasks completed by #2. However, the way the code is currently set up, it is clear that I have no control over the timing of these commands so that they only execute upon the completion of the prior command. I have been looking into other ways of calling these stored procedures, but I'm not sure how to go about doing that (there would appear to be outdated ways of doing it via DAO, but then my Excel implementation does not appear to support certain things I would expect -- like 'Application.OfficeDataSourceObject' -- to instantiate a data source and get on with the rest). I realize my code above is a hack since I'm really using Query Tables to execute stored procedures, but does anyone know how I might control execution flow while doing the above (as in pausing or requesting a 'success/failure' argument from each step before proceeding to the next) or a better way I could execute these stored procedures in their required succession? Any help/advice would be much appreciated. -HK |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to ensure sequential command execution via SQL Query tables?
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
add + suppress command buttons in execution time | Excel Programming | |||
Command Button Macro Execution Failure | Excel Programming | |||
Changing query execution sequence | Excel Discussion (Misc queries) | |||
Non-sequential VLOOKUP function -OR- sequential sort of web query | Excel Worksheet Functions | |||
MS Query, command View-Query Properties | Excel Discussion (Misc queries) |