Interrupting a long SQL Server database access from VB

What i do for really long-running reports, is that i build the data into a table, using a stored procedure that is started by a job (using sp_startjob).
This way, you vb app keeps responding, and only needs to find out when the data has been populated.
* check the job status (by querying the sysjobhistory table)
* have your own table that is updated by your stored proc to find out if the process has completed or not.

Cheers

Either increase your timeout or decrease the number of records you are getting in one go. Can you build your report up in bits with several database requests, each getting less data. Not the neatest of solutions but it is one mechanism I have used.

Regards
Andrew

The stored procedure will run on the client. Best bet would be to use MTS and the ObjectContext and use the GetObjectContex().SetAbort method once the escape button has been pressed. That should take care of the stored procedure too...

regards,
CJ

VincentLawlor, first of all, you might review the EE guidelines about posting comments/answers at the bottom of the page.
Then, you have not properly read the question:
josereyes is calling the stored procedures basically from crystal reports...

Not using MTS, CJ S !!!

Some clarification needed..

Running under Windows 2000, VB6, ADO 2.6.
One stored procedure takes full set of print data filters from ui
and then passes that on to a specific stored procedure to populate data table for use by a specific report. So cannot really use sp_startjob easily at this stage of
development.

Must be a way of asyncronously executing an ADO Command ?

See my suggestion josereyes this can be done with an asynchronous ADODB Command

V.

To: Angellll, I was only offering a proposed solution he is using ADO not just Crystal and stored procedures. And I have read the EE guidelines.

V.

Think I just got Vincent's mail on async command as I was typing in last comment !!!!
Also, not calling sp's from Crystal , Crystal display is just the last bit to get done after waiting for table to
be filled.

hint: josereyes, you should reject the proposed answer, unless it "answeres" your question.

But actually, you might combine that proposal:
using ADO's asyncronous command to fill a table with the data which could be cancelled, and then fill the report, which should simply be based on this table. This will make the report much faster, and it can be reopened on the same data again without need to requery the data. The other advantage is that this table can be repopulated whenever you want, and the report be opened without using the stored procedure...

Cheers

So we are all speaking of the same thing: a stored procedure (asyncronously called) filling a table, and the crystal report basing itself on the table, and not on the stored proc...

VincentLawlor, part of the guidelines says that proposing an answer when other experts have already posted should not be done...
BTW, i cannot see where josereyes speaks of ADO prior to your comment...
Please don't think that your comment isn't worthful, but simply doesn't respect EE guidelines IMHO

BTW, if you use the WITHEVENTS + async execution, you won't need this loop (while state = adStateExecuting)...

Cheers

To: angellll Sorry about that I'm new here.

I was offering a proposed solution and the question as far as I remember was not locked at that stage.

IMHO the solution I have proposed will work.

In future I'll post a comment.  

to VincentLawlor: Thanks again for your understanding. Again, i do not doubt that your proposal can work, but it might not be THE solution for josereyes. My first proposal can also work, but as you see, it is not possible for him "at this point of development".
Note that i posted my first proposal as a comment, even being first for the q :-)

Cheers

Again apologies...

V.

Please gentlemen ! Do we need to have these minor discussions over EE protocol ? It's a distraction.

my apologies...

To Vincent....

UI is in a form, and this calls a method inside a DLL to execute the Command,
where is best place to place DoEvents ?
Presume I catch ESC key press in KeyPress event ?
Regards Jose

If you use the loop as suggestion, you need the doevents inside that loop. If you add the withevents in order to catch the event of the command to complete / fail, you don't need the doevents at all.

I would catch the esc key in the KeyUp event, and compare KeyCode to the vbKeyEscape constant ...

Cheers

Agreed.

If you are calling the command from within a DLL, I assume you are using some public accessor functions to execute the commands, you will need to declare an Event in the DLL to indicate that the Asynchronous command execution call has completed.
You will also need to add a public accessor to the DLL to set the cancel flag from your main application.

WRT Capturing the ESC key:

'set your form.keypreview=true


Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)

If KeyCode = vbKeyEscape And Shift = 0 Then
   objYourDLL.Cancel = True
End If

End Sub

V.



 

Thanks Vincent,

Tried your last but one solution , with some of your last solution already there. Works fine so far .

Regards,
Jose