September 30, 2013 at 8:56 am
I have a stored procedure which runs in database DB1
This stored procedure will have other stored procedures inside it which should run in parallel.
Create procedure db1.proc1
AS
use db2
call proc2
use db3
call proc3
use db4
call proc4
Is there a way to run this stored procedure in parallel and even if the execution of call proc2 fails other two should run?
Thanks
September 30, 2013 at 9:08 am
1 method would be to create a separate job for Proc2, 3 and 4. Then Proc1 could run sp_start_job "proc2' etc...
September 30, 2013 at 9:08 am
Guras (9/30/2013)
I have a stored procedure which runs in database DB1This stored procedure will have other stored procedures inside it which should run in parallel.
Create procedure db1.proc1
AS
use db2
call proc2
use db3
call proc3
use db4
call proc4
Is there a way to run this stored procedure in parallel and even if the execution of call proc2 fails other two should run?
Thanks
Can't force them to run in parallel afaik but it is easy enough to use try/catch so that each proc will still run. Something like this.
Create procedure proc1
AS
begin try
exec db2.dbo.proc2
end try
begin catch
--do something so you know that proc2 failed
end catch
begin try
exec db3.dbo.proc3
end try
begin catch
--do something so you know that proc3 failed
end catch
begin try
exec db4.dbo.proc4
end try
begin catch
--do something so you know that proc4 failed
end catch
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 30, 2013 at 9:52 am
Service broker could be used to accomplish this. Read this article and maybe you could adapt your system to using this method.
http://rusanu.com/2009/08/05/asynchronous-procedure-execution/
September 30, 2013 at 11:53 am
For what you are doing I think Sean's solution is the way to go. That said, you could run them in parallel in an SSIS package then have your proc run the SSIS package (either via sp_start_job if you setup the SSIS package as a job or via xp_cmdshell calling DTSEXEC)
-- Itzik Ben-Gan 2001
October 1, 2013 at 2:08 am
Though i am not 100 % sure but we cant exec anything in parallel way(NOt talking about parallelism here). behind the scene sql engine will treat it with asynchronous approach ( but yes there could be some conditional way to execute something if something happens)
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
October 1, 2013 at 4:06 am
If you have to run these in parallel, then I would suggest using PowerShell. You can spawn multiple threads to have them all firing at the same time. I don't have an example at hand, but you should be able to search one up pretty quick.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 2, 2013 at 4:52 pm
I used to run it using a set of sp_OACreate\sp_OAMethod procedures. I don't have my procedure around, since I didn't use for the last few years, but I am sure you will be able to find it on the web...
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply