Calling Stored Procedure from asp without waiting

Hi,
I'm developping a asp application that somestimes needs some heavy
synchronisation done. I wrote a stored procedure for that purpose witch
takes approximatly 15 minutes. Right now I run this directly on the
server (using MS SQL Server management studio). But I would love to be
able to call it from a asp-page. But I don't want the asp script to
wait for 15 minutes before returning. I figured, it should be possible
to tell the stored procedure call that it should run on the server on
its own. Is this possible? And how? Are there better/other solutions?
Thnx for your time,
Anne SchuthIs this done once a day?
Maybe you should create a scheduled job
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||Thnx for your reply.
No, I should have mentioned that. It's not done with a regular interval
at all.
Anne|||In ASP.Net you could use an asynchronous call.
If you create a job in SQL Server Agent, you could call a stored procedure
that starts the job. The stored procedure does not wait for the job to
finish, so neither does your ASP page.
"Anne" <anne.schuth@.gmail.com> wrote in message
news:1145632601.510533.45240@.z34g2000cwc.googlegroups.com...
> Hi,
> I'm developping a asp application that somestimes needs some heavy
> synchronisation done. I wrote a stored procedure for that purpose witch
> takes approximatly 15 minutes. Right now I run this directly on the
> server (using MS SQL Server management studio). But I would love to be
> able to call it from a asp-page. But I don't want the asp script to
> wait for 15 minutes before returning. I figured, it should be possible
> to tell the stored procedure call that it should run on the server on
> its own. Is this possible? And how? Are there better/other solutions?
> Thnx for your time,
> Anne Schuth
>|||is it asp.NET - in which case you want to use an asynchronous thread to
fire off the sp|||I would build an ASP page.. which called a stored procedure.
And that stored procedure would schedule a job (like 10 seconds into the
future) to run.. and that job would run a stored procedure which did your
15min of work.
uspFireOffTheJobWhichRunsTheMainStoredPr
ocedure
-- the contents of this stor proc would be
dbo.uspJobScheduleUpdate ( 'MyLongJob1' , 'JobSchedule1ForMyLongJob1' , 20
uspLongRunningStoredProcedure
--the contents of this proc would be ... your code to do the 15 minutes or
work.
..
That was about 2 ws of my time (the stuff above and below), so post a
thank you... if you go this route.
Here is some code to help ,,, this would programmatically CREATE the job.
The 2 procedure above ... esp the uspFireOffxxxxx would be needed to
SCHEDULE the job
declare @.jobName varchar(128)
declare @.jobStepName varchar(128)
declare @.jobScheduleName varchar(128)
declare @.uspName varchar(128)
declare @.uuid uniqueidentifier
select @.jobName = 'MyLongJob1'
select @.uspName = 'dbo.uspLongRunningStoredProcedure'
select @.jobStepName = 'JobStep1For' + @.jobName
select @.jobScheduleName = 'JobSchedule1For' + @.jobName
select @.uspName = 'EXEC ' + @.uspName
declare @.serverName varchar(128)
select @.serverName = CONVERT(varchar(128) , SERVERPROPERTY('servername') )
--print @.serverName
declare @.dbName varchar(128)
select @.dbName = DB_NAME()
EXEC msdb.dbo.sp_add_job @.job_name = @.jobName,
@.enabled = 1,
@.description = @.jobName,
@.owner_login_name = 'sa' -- << THIS ONE YOU NEED TO WATCH .. and use
the loginname that your asp page uses to connect to the db()
EXEC msdb.dbo.sp_add_jobstep @.job_name = @.jobName,
@.step_name = @.jobStepName,
@.subsystem = 'TSQL',
@.database_name = @.dbName ,
@.command = @.uspName
EXEC msdb.dbo.sp_add_jobserver @.job_name = @.jobName,
@.server_name = @.serverName
-- END create jobs programatically
here is a final script.. I created to update the schedule on a job .. by
using its name, schedueName and delay seconds.
in my testing I found 10 seconds was the smallest increment I could use.
if exists (select * from sysobjects
where id = object_id('dbo.uspJobScheduleUpdate') and sysstat & 0xf = 4)
drop procedure dbo.uspJobScheduleUpdate
GO
CREATE Procedure dbo.uspJobScheduleUpdate ( @.jobName varchar(128) ,
@.jobScheduleName varchar(128) , @.delaySeconds int )
AS
SET NOCOUNT ON
declare @.minimumTimeDelaySeconds int
Select @.minimumTimeDelaySeconds = 10
if @.delaySeconds < @.minimumTimeDelaySeconds
BEGIN
select @.delaySeconds = @.minimumTimeDelaySeconds
END
declare @.now datetime
select @.now = GETDATE()
Select @.now = DATEADD(s , @.delaySeconds , @.now)
--print @.now
--select DATEPART(s , @.now)
--select DATEPART(hh , @.now)
--select DATEPART(mi , @.now)
/*
[@.active_start_date =] active_start_date
Is the date on which execution of the job can begin. active_start_date is
int,
with a default of NULL. Values must be formatted as YYYYMMDD.
If active_start_date is not NULL, the date must be greater than or equal to
19900101.
*/
declare @.year varchar(4)
declare @.month varchar(2)
declare @.day varchar(2)
declare @.yearint int
declare @.monthint int
declare @.dayint int
declare @.hour varchar(2)
declare @.minute varchar(2)
declare @.second varchar(2)
declare @.hourint int
declare @.minuteint int
declare @.secondint int
--year does not need prefixed "0"
select @.yearint= DATEPART(yyyy , @.now)
select @.year = convert(varchar(4) , @.yearint )
select @.monthint= DATEPART(m , @.now)
if @.monthint < 10
BEGIN
select @.month = '0' + convert(varchar(1) , @.monthint )
END
else
BEGIN
select @.month = convert(varchar(2) , @.monthint )
END
select @.dayint= DATEPART(d , @.now)
if @.dayint < 10
BEGIN
select @.day = '0' + convert(varchar(1) , @.dayint )
END
else
BEGIN
select @.day = convert(varchar(2) , @.dayint )
END

select @.hourint= DATEPART(hh , @.now)
if @.hourint < 10
BEGIN
select @.hour = '0' + convert(varchar(1) , @.hourint )
END
else
BEGIN
select @.hour = convert(varchar(2) , @.hourint )
END
select @.minuteint= DATEPART(mi , @.now)
if @.minuteint < 10
BEGIN
select @.minute = '0' + convert(varchar(1) , @.minuteint )
END
else
BEGIN
select @.minute = convert(varchar(2) , @.minuteint )
END
select @.secondint= DATEPART(s , @.now)
if @.secondint < 10
BEGIN
select @.second = '0' + convert(varchar(1) , @.secondint )
END
else
BEGIN
select @.second = convert(varchar(2) , @.secondint )
END
--print '*' + @.hour + '*'
--print '*' + @.minute + '*'
--print '*' + @.second + '*'
declare @.derivedstartdate varchar(12)
select @.derivedstartdate = @.year + @.month + @.day
declare @.derivedstarttime varchar(12)
select @.derivedstarttime = @.hour + @.minute + @.second
--print @.derivedstartdate
--print @.derivedstarttime
declare @.alreadyExists bit
declare @.enabled bit
declare @.active_start_date int
declare @.active_start_time int
EXEC dbo.uspJobScheduleExists @.jobName , @.jobScheduleName , @.alreadyExists
output , @.enabled output , @.active_start_date output , @.active_start_time
output
/*
print 'Debugging Values'
print '@.jobName=' + @.jobName
print '@.jobScheduleName=' + @.jobScheduleName
print '@.alreadyExists=' + convert(varchar(8) , @.alreadyExists)
print '@.enabled=' + convert(varchar(8) , @.enabled)
print '@.active_start_date=' + convert(varchar(16) , @.active_start_date)
print '@.active_start_time=' + convert(varchar(16) , @.active_start_time)
print '/Debugging Values'
*/
declare @.jobSchedulingCheck int
if @.alreadyExists = 0 --FALSE
BEGIN
EXEC @.jobSchedulingCheck = msdb.dbo.sp_add_jobschedule
@.job_name = @.jobName,
@.name = @.jobScheduleName,
@.enabled = 1,
@.freq_type = 1 ,
@.freq_interval = 1 , --once
@.active_start_date = @.derivedstartdate ,
@.active_start_time = @.derivedstarttime--'153000' --(3:30 pm) 24hr HHMMSS.
END
else
BEGIN
declare @.allClearToUpdateTheJob bit
select @.allClearToUpdateTheJob = 1 -- default to true
if @.enabled <> 0 -- The job is enabled,,but how far in the future'
BEGIN
--most times, if its enabled..then its set to fire pretty quickly in the
future..so set it to false..but also check a "too far in the future" date
select @.allClearToUpdateTheJob = 0 -- false
--/*
if @.active_start_date - @.derivedstartdate >= 0 -- the date is in the future
..or today
begin
if @.active_start_time - @.derivedstarttime > @.delaySeconds -- the time is
greater in the future than the delay period
begin
--print 'too far into the future for the job!'
select @.allClearToUpdateTheJob = 1-- while it was enabled..it was too far
into the future
end
end
--*/
END
--if its not enabled, then the @.allClearToUpdateTheJob value will not
changed..thus it will be updated
if @.allClearToUpdateTheJob <> 0
BEGIN
EXEC @.jobSchedulingCheck = msdb.dbo.sp_update_jobschedule --0 (success) or 1
(failure)
@.job_name = @.jobName,
@.name = @.jobScheduleName,
@.enabled = 1,
@.freq_type = 1 ,
@.freq_interval = 1 , --once
@.active_start_date = @.derivedstartdate ,
@.active_start_time = @.derivedstarttime--'153000' --(3:30 pm) 24hr HHMMSS.
END
END
--Return Code Values
--0 (success) or 1 (failure)
if (@.jobSchedulingCheck<>0)
BEGIN
INSERT INTO dbo.tblAuditTrail (SubjectID , ObjectID , ShortDescription)
VALUES (@.jobName , @.jobScheduleName, 'sp_add_jobschedule or
sp_update_jobschedule FAILED.')
END
SELECT( @.jobSchedulingCheck )
if (@.jobSchedulingCheck IS NOT NULL)
begin
return @.jobSchedulingCheck
end
SET NOCOUNT OFF
GO
"Anne" <anne.schuth@.gmail.com> wrote in message
news:1145633124.959348.250700@.g10g2000cwb.googlegroups.com...
> Thnx for your reply.
> No, I should have mentioned that. It's not done with a regular interval
> at all.
> Anne
>|||Yeah, that does deserve a THANK YOU!
I will need some time to get it all together and running but this
certainly points me in the right direction! I've never used jobs
before, so din't think of it.
Thnx all for the quick replies!
(NP: I don't use asp.net)|||wow'
all that hour minute second stuff can be replace with
declare @.now datetime
select @.now = getdate()
select
convert(varchar,@.now,112),replace(conver
t(varchar,@.now,108),':','')
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||If you are on 2005, consider using Service Broker for this.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Anne" <anne.schuth@.gmail.com> wrote in message
news:1145632601.510533.45240@.z34g2000cwc.googlegroups.com...
> Hi,
> I'm developping a asp application that somestimes needs some heavy
> synchronisation done. I wrote a stored procedure for that purpose witch
> takes approximatly 15 minutes. Right now I run this directly on the
> server (using MS SQL Server management studio). But I would love to be
> able to call it from a asp-page. But I don't want the asp script to
> wait for 15 minutes before returning. I figured, it should be possible
> to tell the stored procedure call that it should run on the server on
> its own. Is this possible? And how? Are there better/other solutions?
> Thnx for your time,
> Anne Schuth
>|||
Thanks,
I felt like what I was doing was too much drama when I wrote it.
I appreciate the convert tip.
SQL wrote:
> wow'
> all that hour minute second stuff can be replace with
> declare @.now datetime
> select @.now = getdate()
> select
> convert(varchar,@.now,112),replace(conver
t(varchar,@.now,108),':','')
>
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/