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
"jobs" <jo**@webdos.comwrote in message
news:11**********************@51g2000cwl.googlegro ups.com...
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.
