Solved

Interrupting a long SQL Server database access from VB

Posted on 2001-07-09
19
210 Views
Last Modified: 2008-02-07
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


0
Comment
Question by:josereyes
  • 7
  • 5
  • 5
  • +2
19 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 6264693
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
 
LVL 5

Expert Comment

by:AndrewDev
ID: 6264699
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
 
LVL 22

Expert Comment

by:CJ_S
ID: 6264865
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
 
LVL 4

Accepted Solution

by:
VincentLawlor earned 100 total points
ID: 6265059
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 6265098
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
 

Author Comment

by:josereyes
ID: 6265137
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
 
LVL 4

Expert Comment

by:VincentLawlor
ID: 6265154

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
 

Author Comment

by:josereyes
ID: 6265155
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 6265160
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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 6265182
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
 
LVL 4

Expert Comment

by:VincentLawlor
ID: 6265214
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 6265238
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
 
LVL 4

Expert Comment

by:VincentLawlor
ID: 6265262
Again apologies...

V.
0
 

Author Comment

by:josereyes
ID: 6265329
Please gentlemen ! Do we need to have these minor discussions over EE protocol ? It's a distraction.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 6265347
my apologies...
0
 

Author Comment

by:josereyes
ID: 6265404
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 6265585
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
 
LVL 4

Expert Comment

by:VincentLawlor
ID: 6265659
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
 

Author Comment

by:josereyes
ID: 6265938
Thanks Vincent,

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

Regards,
Jose
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now