G
Guest
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
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