Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
calling each stored procedure parallel in sql job

Answered calling each stored procedure parallel in sql job

  • Thursday, January 31, 2013 5:29 AM
     
     
    Sign In to Vote
    0

    There   are 4  steps


    Step 1. Loading  data  from  different  sources into local server
    Step 2. Three  separate  stored procedure to load  data into three different tables
            using  tables loaded from  step 1.


    I  want  to run   step 2,  

    calling each stored procedure parallel  instead of running   step by step.


    is it  possible in SQL Agent job?


    currently  i have job steps  as follows

    Step 1   : Load Source data
    Step 2   : Populate  first table calling  stored procedure 1
    Step 3   : Populate  second table calling stored procedure 2
    Step 4   : Populate  third table calling  stored procedure 3

    Thanks

    V

     

All Replies

  • Thursday, January 31, 2013 5:35 AM
     
     Answered
    Sign In to Vote
    0
    Hi, SSIS is the way for you. SSIS can do the parallel work as you desired. Then call the SSIS package in the step 2.

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.


     
  • Thursday, January 31, 2013 5:38 AM
     
     
    Sign In to Vote
    0
    Yes,  I can do with SSIS but is it possible  without  SSIS  like batch file
     
  • Thursday, January 31, 2013 5:41 AM
     
     
    Sign In to Vote
    0
    Yes, it is possible.  Just create one connection for each SP and run one SP per connection.

    Jose R. MCP
    Code Samples

     
  • Thursday, January 31, 2013 5:44 AM
     
     
    Sign In to Vote
    0

    Hi Vaishu - As said, SSIS is the best for you.

    It does not mean we can not acheive it. We can acheive by splitting the job into multiple jobs and schedule at the same time once the Step 1 is executed(Step1 should be separate job which should be completed before we kick off the others). And I am not fan of ofcourse. We have better way with SSIS.

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

     
  • Thursday, January 31, 2013 5:45 AM
     
     
    Sign In to Vote
    0
    Jose,   can you give example  or  more detail?   hope  you are not referring to use SSIS
     
  • Thursday, January 31, 2013 5:55 AM
     
     
    Sign In to Vote
    0
    It depends on what you use to run the SP's.  Is it SSMS?  If yes then just open 3 query windows to the same SQL Server.  Each window has its own connection.  Simply execute a single SP in each of the query windows.  Since they use separate connections then they will run in parallel.

    Jose R. MCP
    Code Samples

     
  • Thursday, January 31, 2013 6:14 AM
     
      Has Code
    Sign In to Vote
    0

    Yes, you can do usign SQL Server Ajent Job,

    Please see below code, tweak it as per your SP names

    exec Sp_Create_Jobs 	@job_name =  'Start SP1',
@command  = 'exec YourStpredprocedure1 ',
@output_file_name =  'SP1.log'
exec Sp_Create_Jobs 	@job_name =  'Start SP2',
@command  = 'exec YourStpredprocedure2 ',
@output_file_name =  'SP2.log'
exec Sp_Create_Jobs 	@job_name =  'Start SP3',
@command  = 'exec YourStpredprocedure3 ',
@output_file_name =  'SP3.log'

    Thanks & Regards Prasad DVR

     
  • Thursday, January 31, 2013 7:59 AM
     
     
    Sign In to Vote
    0

    i decided to go with SSIS,  what is good practice,    three Execute  SQL Task,  with one connection  or   create three connection  use separate connection for each SQL Task

    Thanks

    V