October 28, 2012 at 6:11 pm
Hello Experts,
I have an application that does many things, at the end, it calls a stored procedure to perform some cleanup tasks. this stored procedure may take 10+ minutes to complete. I don't want the application to sit there and wait for the stored procedure to complete then return control to the application. instead it should kick off the task and say "my job is done" and stored procedure continues to run until it is done. is this the type of thing that should be handled on the application end, wonder if there is a away to handle this in sql end?
I look at the NOWAIT option, but it doesn't seem something I could use. Any suggestion would be appreciated.
October 28, 2012 at 7:10 pm
As you suspect this is something to be resolved on the application side.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.October 28, 2012 at 7:22 pm
This can be done pretty easily. Create an SQL Server job to run the proc and have the app kick the job off.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 28, 2012 at 7:25 pm
Jeff Moden (10/28/2012)
This can be done pretty easily. Create an SQL Server job to run the proc and have the app kick the job off.
I thought about this too. Thanks!
October 28, 2012 at 9:08 pm
haiao2000 (10/28/2012)
Jeff Moden (10/28/2012)
This can be done pretty easily. Create an SQL Server job to run the proc and have the app kick the job off.I thought about this too. Thanks!
Tou may also want to look at Service Broker if it is possible that the procedure must be able to run in parallel with itself.
October 29, 2012 at 8:19 am
Jeff Moden (10/28/2012)
This can be done pretty easily. Create an SQL Server job to run the proc and have the app kick the job off.
Saying I have a SQL job that executes a stored procedure as follow. Could you give an example (VB or C#) code to execute this job. Provided procedure DoSomeCleanupTasks takes 2 parameters (A Int, B DateTime)
Thank You!
USE [msdb]
GO
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Database Maintenance' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Database Maintenance'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'DoSomeCleanupTasks',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'No description available.',
@category_name=N'Database Maintenance',
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Refresh Parent Last Modified Date',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'Exec dbo.DoSomeCleanupTasks',
@database_name=N'MyDataBase',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO
October 29, 2012 at 9:10 am
Instead of calling the job explicitly, you could have a job that runs every five minutes or so, polls a table to see if any works needs to be done, and then executes the cleanup tasks.
What type of "cleanup" tasks are we talking about here?
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
October 29, 2012 at 9:51 am
Eric M Russell (10/29/2012)
Instead of calling the job explicitly, you could have a job that runs every five minutes or so, polls a table to see if any works needs to be done, and then executes the cleanup tasks.What type of "cleanup" tasks are we talking about here?
This cleanup task needs to be part AND intergrated to a loader, client may schedule the loader to run on whichever schedule they want and yes we can have client set up this cleanup task/job to run immediately after the loader, etc. but that complecates things, client does not need to care about this cleanup task, that is just a quick explanation. On the end, we want it to be intergrated into one piece.
October 30, 2012 at 7:24 am
I agree with Lynn - this is sounding more and more like a service broker opportunity.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
October 30, 2012 at 8:48 am
haiao2000 (10/29/2012)
Jeff Moden (10/28/2012)
This can be done pretty easily. Create an SQL Server job to run the proc and have the app kick the job off.Saying I have a SQL job that executes a stored procedure as follow. Could you give an example (VB or C#) code to execute this job. Provided procedure DoSomeCleanupTasks takes 2 parameters (A Int, B DateTime)
My apologies. Can't help there. I'm a hardcore data troll. I don't even know how to spell C# never mind use it.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 30, 2012 at 9:09 am
haiao2000 (10/29/2012)
Eric M Russell (10/29/2012)
Instead of calling the job explicitly, you could have a job that runs every five minutes or so, polls a table to see if any works needs to be done, and then executes the cleanup tasks.What type of "cleanup" tasks are we talking about here?
This cleanup task needs to be part AND intergrated to a loader, client may schedule the loader to run on whichever schedule they want and yes we can have client set up this cleanup task/job to run immediately after the loader, etc. but that complecates things, client does not need to care about this cleanup task, that is just a quick explanation. On the end, we want it to be intergrated into one piece.
If the cleanup task is to be called explicitly, then I don't see the reason for implementing it as a job. Could the cleanup tasks possibly be implemented as a stored procedure, which gets called asynchronously by the application workflow?
For example:
http://www.codeproject.com/Articles/42266/ADO-NET-Asynchronous-SQL-Calls
...This article will show you how to use asynchronous calls in ADO.NET to receive progress messages from long running stored procedure calls or any other SQL command that raises messages back to the client...
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
October 30, 2012 at 12:57 pm
Jeff Moden (10/30/2012)
haiao2000 (10/29/2012)
Jeff Moden (10/28/2012)
This can be done pretty easily. Create an SQL Server job to run the proc and have the app kick the job off.Saying I have a SQL job that executes a stored procedure as follow. Could you give an example (VB or C#) code to execute this job. Provided procedure DoSomeCleanupTasks takes 2 parameters (A Int, B DateTime)
My apologies. Can't help there. I'm a hardcore data troll. I don't even know how to spell C# never mind use it.
Not big of a deal Jeff, I figured it out.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply