schedule sql server stored procedures defined in a table??

[jobs]

I have a growning list of stored procedures that accept a single string
as a parameter.

The list and parameters are maintained in a table with two columns.

Some of the Stored procedures take hours to complete.

From asp.net, I want users to be able to select and launch the stored

procedures and the page come right back and recording infomation about
the stored procedure (like a job number) that can later be used to
report what stored procedures are still running.

Can I do this in ASP.NET via ADO.NET and sql commands?

If possible I don't want to create an sql server 2005 job for every
stored procedure. If possible, not have to create any jobs at all. I've
been able to do the above with an Oracle Procedures and the same
asp.net solution using DBMS.Job_Submit.

 

[sloan]

You can work something like this. Here are my ideas...........


Create a "holding" table for each stored procedure.
What I mean by this is that this is where your parmeters can do for a stored
procedure.

Let's say you needed to call a procedure called
uspUpdateEmployeeHistory

instead of having

uspUpdateEmployeeHistory (empid int)

just have it as

uspUpdateEmployeeHistory
......

Ok.

Then create a "holding table"

create table UpdateEmployeeHistoryHoldingTable (empid int)

.......
Ok
uspUpdateEmployeeHistory is smart enough to look in the holding table for
parameters.

..........

Ok next step, create a

StoredProceduresToRun table

create table StoredProceduresToRun ( uspname varchar(128) )

........

whenever you need a job to run, you just pop an entry into this table.

......

Finally, you schedule 1 job. I guess to run every 1 minute for example.

You also have a stored procedure. called
uspCheckJobToRunTableAndRunJob


.......

the schecued job will always call uspCheckJobToRunTableAndRunJob when it
runs.
uspCheckJobToRunTableAndRunJob contains code to look at the
StoredProcecursToRunTable

then using dynamic sql, you can call

declare @usp varchar(128)
select @usp = top 1 uspname from StoredProcecursToRunTable

if (@usp IS NOT NULL)
begin
EXEC ( @usp )
end


You'll have to write a cursor (ahhhhh?) to handle the multi records in
StoredProcecursToRunTable

.......


Ok..

Finally, you want something to fire.

When you actually need an employee history to update , you do this:


INSERT INTO UpdateEmployeeHistoryHoldingTable (empid ) values 1001
INSERT INTO StoredProceduresToRun (uspname) values
'uspUpdateEmployeeHistory'


.......

Then, when the job goes off 1 to 60 seconds later (because it fires every
minute).......

eventually

EXEC 'uspUpdateEmployeeHistory' will run (via the EXEC ( @usp ) above)

and remembering that
'uspUpdateEmployeeHistory' is smart enough to find the "1001" value in the
UpdateEmployeeHistoryHoldingTable table.


Kinda hacky, but it'll get the job done.


PS
I like the "holdingtable" idea, because then you're not screwed if you need
to go to 2 parameters for each stored procedure.
And, if you get duplicates in your holding table, its not a big idea.
Like if 1001 (empid) got added to the UpdateEmployeeHistoryHoldingTable 55
times, you only need to run it once.
select distinct empid from UpdateEmployeeHistoryHoldingTable


Make any sense?


I call this poor man's async processing.






When you need to update the history of any employee