I have a program written in VB6. The program accepts a parameter, and if it is called with a parameter it runs without a form. If there is no parameter it loads up a form.
If I call the program with parameter 3, it will pull a list of employees, it will take the first employee and run a specific stored procedure, create an excel file and then email that employee the excel file. It will cycle through all employees until its done and then exit.
If I call it with parameter 19, it could pull a different list of employees, take the first employee, call a different stored procedure, create an excel file and then email that employee the excel file and continue on to the next employee.
This program works just fine when I run it manually (opens up a form and I choose the report, etc).
It works fine when it is scheduled and I pass a parameter to it.
It does NOT work fine when the scheduler call it and then, while its already running, the scheduler calls it again.
Lets say that each report normally takes 10 minutes to run.
I have the scheduler setup to run Program with Parm#3 at 10am and then run Program with parm#19 at 10:30.
If the 10am process is still running at 10:30 (when Program with parm19 is called) they are both hosed and will remain in the Running state until I cancel them.
Is there a way to get around this, so that the scheduler can call the same VB6 application whenever its needed without messing anything up?
Is this problem caused because its VB6? Could I re-write this in .Net and we would not have this issue?
Is there some type of "wrapper" program I could call which could call the VB6 app in its own process/thread so that they don't get all hosed up? Could I run this on Windows Server 2008 and bypass all of these hassles?
I am looking for the quickest method that would work. It doesn't have to be pretty, it just needs to work.
Like I said, the thing works just fine until the scheduled become overlapped and then they both fail.
The server is Windows Server 2003, 64bit, SP2
I am hitting SQL Server 2005
I use ADODB to call the stored procedure to get the recordset
I have Office 2010 installed on it, which is how I create the excel files, using code like:
Dim xl As New Excel.Application
Dim xlwbook As Excel.Workbook
Dim xlsheet As Excel.Worksheet