Calling Stored Procedure from asp without waiting

[Anne]

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

[SQL]

Is this done once a day?
Maybe you should create a scheduled job

Denis the SQL Menace
http://sqlservercode.blogspot.com/

[Anne]

Thnx for your reply.
No, I should have mentioned that. It's not done with a regular interval
at all.

Anne

[Aaron Bertrand [SQL Server MVP]]

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....@gmail.com> wrote in message
news:1145632601....@z34g2000cwc.googlegroups.com...

[Will]

is it asp.NET - in which case you want to use an asynchronous thread to
fire off the sp

[sloan]

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.

uspFireOffTheJobWhichRunsTheMainStoredProcedure
-- 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 weeks 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....@gmail.com> wrote in message

news:1145633124.9...@g10g2000cwb.googlegroups.com...

[anne....@gmail.com]

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)

[SQL]

wow????
all that hour minute second stuff can be replace with

declare @now datetime
select @now = getdate()

select
convert(varchar,@now,112),replace(convert(varchar,@now,108),':','')

[Tibor Karaszi]

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....@gmail.com> wrote in message

news:1145632601....@z34g2000cwc.googlegroups.com...

[sl...@ipass.net]

Thanks,

I felt like what I was doing was too much drama when I wrote it.

I appreciate the convert tip.