Interrupting a long SQL Server database access from VB

This is a 'standard' problem but have not seen a specific
example that I can just copy that does actually work

User interface has a list of reports for user to pick and display with Crystal reports. Selecting report causes
an SQL stored procedure to be called to build the data of
say 50 to 100,000 records and display the report.

Problem: Data access times out.

Required: Ability to press ESC key or some other specific user interface key/button and exit from the fetch cycle gracefully.

Have tried DoEvents but with no success


josereyesAsked:
Who is Participating?
 
VincentLawlorCommented:
I would use an ADO connection here and use an Asynchronous Execute for your Stored procedure.

With this you can invoke ADODB.Command.Cancel cancelling your execution. You can then pass the resulting recordset to Crystal reports.


Eg.

Dim adoConnection as new ADODB.Connection
Dim adoCommand as new ADODB.Command
Dim adoRs as new ADODB.Recordset

adoConnection.Open "Connection String here"

adoCommand.ActiveConnection = adoConnection
adoCommand.CommandText = "Stored Proc"

adoCommand.CommandTimeOut = 100000
'As long as you want but remember that this will be overridden by SQL's timeouts no matter what you do.

rsResult = adoCommand.Execute ,,adAsyncExecute

While adoCommand.State = adStateExecuting

    'Use a flag here to check if the user pressed the ESC    button
    If fCancelled then
       adoCommand.Cancel
       'Do some clean up in here and report that the      command was cancelled
       Exit Sub/Function etc.
    endif

    DoEvents

wend

'Pass your recordset to crystal


This should solve your problem.

V.


0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
0
 
AndrewDevCommented:
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
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
CJ_SCommented:
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
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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...
0
 
josereyesAuthor Commented:
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 ?
0
 
VincentLawlorCommented:

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.
0
 
josereyesAuthor Commented:
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.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
0
 
VincentLawlorCommented:
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.  
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
0
 
VincentLawlorCommented:
Again apologies...

V.
0
 
josereyesAuthor Commented:
Please gentlemen ! Do we need to have these minor discussions over EE protocol ? It's a distraction.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
my apologies...
0
 
josereyesAuthor Commented:
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
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
0
 
VincentLawlorCommented:
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.



 
0
 
josereyesAuthor Commented:
Thanks Vincent,

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

Regards,
Jose
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.