Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Do not wait for Query to completed Expand / Collapse
Author
Message
Posted Sunday, October 28, 2012 6:11 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 2:31 PM
Points: 151, Visits: 391
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.
Post #1378067
Posted Sunday, October 28, 2012 7:10 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 8:15 AM
Points: 3,065, Visits: 4,639
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.
Post #1378070
Posted Sunday, October 28, 2012 7:22 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:19 PM
Points: 35,263, Visits: 31,749
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
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1378075
Posted Sunday, October 28, 2012 7:25 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 2:31 PM
Points: 151, Visits: 391
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!
Post #1378076
Posted Sunday, October 28, 2012 9:08 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 7:59 PM
Points: 20,702, Visits: 32,342
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.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1378082
Posted Monday, October 29, 2012 8:19 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 2:31 PM
Points: 151, Visits: 391
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

Post #1378267
Posted Monday, October 29, 2012 9:10 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 3:26 PM
Points: 1,675, Visits: 4,779
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?
Post #1378297
Posted Monday, October 29, 2012 9:51 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 2:31 PM
Points: 151, Visits: 391
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.
Post #1378318
Posted Tuesday, October 30, 2012 7:24 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Friday, September 26, 2014 9:28 AM
Points: 4,362, Visits: 6,204
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 at GMail
Post #1378764
Posted Tuesday, October 30, 2012 8:48 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:19 PM
Points: 35,263, Visits: 31,749
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
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1378816
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse