the best way to implement batch job

[ck9663]

hi guys this is a little challenging, at least for me...here goes...

i have to run a DOS batch file from a server. with some parameters that i need to pass. these parameters can be found on a sql-server table. which means i need to build the string that will be used to call the batch file (set @var = 'callbatch.bat ' + @var1 + ' ' + @var2). then call the batch file using the xp_cmdshell command. each batch file runs about 4-5 hours. i don't have an option. i have to use the batch file. it's an engine call.

here's the trick. once a new file is received i need to run this command. for one file, i might need to run 1 or more batch file. i also need to see if the batch file runs successfully. i am also considering an application that will run even if you close the GUI/Browser.

there are two ways i'm thinking of implementing this:
1. create a "jobs" table. create a trigger on that table that will run those batch file everytime a new record will be inserted. since each batch file runs for 4-5 hours, will the table be locked during the execution of the trigger and will be released only once the trigger is done? is this a good way of implementing this considering the length of time the trigger runs?

2. create a "jobs" table. create a scheduled job that runs every hour to check if there are new record inserted on the table and run the necessary batch files. same question here, since the batch file runs 4-5 hours and the scheduled job runs every hour, will the next scheduled job wait for the preceding one to complete? will this stress my server cpu since it will be running every hour. although the hourly scheduled run will only check if there are new record on the table and will only run the (4-5 hour running) batch file if there are new record?

are there any other option?

thanks so much for the help

[Purple]

Hi ck9663,

did you sort this out ? What solution did you implement in the end ?

Purple

[ck9663]

Hi ck9663,

did you sort this out ? What solution did you implement in the end ?

Purple

hi purple, nope i have not implemented this yet. although am thinking of implementing a scheduled job that scans the jobs_table for a newly added job. if i do trigger, the process will stop if the connection is lost. that means i'll have problem if i implement it in a browser-client-server based application

[Purple]

Hi ck9663,

I think I would be inclined to approach this the other way around, use windows scheduler to fire an hourly job which looks for the input files and runs an app to pre process and then load into the database. I have no idea how big these files are but I expect you should be able to improve the run time by optimising the insert.

You can then roll back the full file if the processing fails and generate an alert, send someone an email etc..

Is this approach possible ?

Regards Purple

[ck9663]

Hi ck9663,

I think I would be inclined to approach this the other way around, use windows scheduler to fire an hourly job which looks for the input files and runs an app to pre process and then load into the database. I have no idea how big these files are but I expect you should be able to improve the run time by optimising the insert.

You can then roll back the full file if the processing fails and generate an alert, send someone an email etc..

Is this approach possible ?

Regards Purple

yes, it's possible....and i think my earlier response is a little confusing coz it's the same concept that you're suggesting ;)

the biggest size in row is about 70 columns by 16M rows...the biggest size in columns is about 250 columns by 2M rows...