| Author |
Topic |
|
smartlizard
Starting Member
24 Posts |
 Posted - 2002-02-20 : 01:09:51
|
| I have to create a report from an ASP file that will take at least 5 minutes. Can't have the person sit there looking at a blank white browser window, so I thought I could create a Queue for the report. Here is what I did...1. Put all search elements in a SQL table,2. Added a trigger on Insert that uses xp_cmdshell to call a VBS file,3. The VBS file runs the report.Problem is the Trigger hangs waiting for the VBS script to complete.I would really like the trigger to call the VBS file and return, letting the VBS go on it's own.Am I doing the right thing in this situation, is there a better way, or do I simply need to add something to make it not hang and wait?Thanks is advance.Ron Sellhttp://smartLIZARD.com |
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2002-02-20 : 01:50:45
|
| HiI like to do things similar to the way I wrote in the mail queue article.Create a queue table with details of the job. Then have a DTS task that fires off whatever events are needed for queued jobs, in your case, run a report and generate a file.Then set up a job to call the DTS task. You can either run the job every minute, or call the sp_start_job procedure as needed.That's one way of doing it anyway. Hope it gives you some ideas.Damian |
 |
|
|
smartlizard
Starting Member
24 Posts |
Posted - 2002-02-20 : 02:25:52
|
| If I have the Job fire once a minute, and it takes 5 minutes to run one report, how do I keep the job from trying to process the same record twice, three times, etc...Also, would the Job execute the VBS script through WSH or directly?Ron Sellhttp://smartLIZARD.comEdited by - smartlizard on 02/20/2002 02:47:03 |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2002-02-20 : 08:35:53
|
| HiYou can have a flag in your queue table for "in progress" or something. DTS can have script steps.Like I said, it was only an idea..... There will obviously be implementation details and problems I can not predect. I was just give you something to start with.Damian |
|
|
|
smartlizard
Starting Member
24 Posts |
Posted - 2002-02-20 : 13:44:21
|
| Has anyone done something similar that can give me some specific insight?Ron Sellhttp://smartLIZARD.com |
|
|
|
graz
Chief SQLTeam Crack Dealer
4149 Posts |
Posted - 2002-02-20 : 14:41:27
|
| I recently did something very similar. I was doing some computations on stock price history. Each batch would take from 50-90 seconds depending on how much data there was and I typically ran 4-5 batches at once. All my processing was done inside a stored procedure. My batches were also rerunnable. If I reran the same batch multiple times it wasn't a problem.I build one table that had columns for every parameter for my stored procedure. This was the queue table. It also had a field for status holding Ready, Running, Completed.I also built a batch engine configuration table. It only had one column and one row :) The valid values were Running, Stopped, and Paused.Then I created a SQL Server agent job. This job fired up once every minute and checked the configuration table. If the value was Paused or Running, it exited immediately.If it was Stopped, it checked for any unprocessed work in the queue table. If it found any, it updated the engine status to running. Grabbed the next item in the work queue, set it's status to running and called proc with the parameters. I also checked the engine status between calls to the queue table. That way I could pause the engine in mid-run. In hindsight Pause and Stopped were just about the same thing.At the end of each run it would update statuses. Probably not enough to code from but you get the idea. I ended making a little ASP page to keep track of what was running.===============================================Creating tomorrow's legacy systems today.One crisis at a time. |
|
|
|
smartlizard
Starting Member
24 Posts |
Posted - 2002-02-20 : 15:58:56
|
| you are pretty proud of your self aren't you!!Thanks for the reply. Been racking my brain to figure out how to make this work.When I run the job, it will select any items in the Queue (I love spelling that word!) that are not running or complete, then I guess (this is where I get a little fuzzy) we loop through each record and fire up an instance of Windows Script Host to execute my report writer. Does this sound even remotely correct?Thanks.Ron Sellhttp://smartLIZARD.com |
|
|
|
graz
Chief SQLTeam Crack Dealer
4149 Posts |
Posted - 2002-02-20 : 17:09:27
|
quote:
you are pretty proud of your self aren't you!!
You'd have to see what I had before to really appreciate it. I just finally got sick of staring at a blank screen while it processed. Of course, what I had before was pretty kludgy and I'm not very proud of it  quote:
When I run the job, it will select any items in the Queue (I love spelling that word!) that are not running or complete, then I guess (this is where I get a little fuzzy) we loop through each record and fire up an instance of Windows Script Host to execute my report writer. Does this sound even remotely correct?
Possibly as good as you'll get. You might think about a stand-alone app that just runs the report writer. It turns on and looks for new jobs to run and just sits there when it isn't running.Of course, this also begs the question, can you reduce the run time from 5 minutes to 15 seconds. Is your code pretty optimized?===============================================Creating tomorrow's legacy systems today.One crisis at a time. |
|
|
|
smartlizard
Starting Member
24 Posts |
Posted - 2002-02-20 : 18:52:49
|
| I am sure it is not 100% optimized, but don't you optimize it once your done :)It literally will search though BILIIONS of records. It will find info from one database, then use that data on another database and so on...I keep saying 5 minutes, but it could actually take a bit longer.The app you are talking about, I only know VBScript, SQL very good and a moderate to low end VB programmer. Is this something that could be written in VB? Will it actually perform better than straight VBScript in WSH? What about multiple people asking for the report, could it handle it?Wow, thanksRon Sellhttp://smartLIZARD.com |
|
|
|
smartlizard
Starting Member
24 Posts |
Posted - 2002-02-21 : 21:32:46
|
| It works now, here is what I did (in case anyone else ever needs to know.)1. Created a VBScript to create my report by writing out a text file of the HTML report.2. When the user requests a report, I store all the search elements into a table in SQL server. Also I added a field called "Status" and add "Queued" in this field.3. Created a SQL Server Job that executes a command to the operating system to execute my VBS script using wscript.exe. It selects all records in the queue table that has a Status of "Queued". Once it selects these records it marks the records "In Process".4. Wscript creates my report, then marks the records "Complete"Hope this helps someone, I appreciate all the helpful advice.(Does this thing have spell checker :) )Ron Sellhttp://smartLIZARD.com |
|
|
|
|
Creating a Report Queue