Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2003
    Posts
    176

    Unanswered: executing multiple procs at one go

    Hi,

    I would like to execute multiple stored procs by calling a single script say A. I want to A to execute on a nightly basis. The multiple stored procs are bulk inserts into separate tables. I want to be notified of the error if one among the many stored procs fail when I call A. But if one fails, I don't want A to fail. The remaining stored procs should execute. Does anyone have a script wriiten for A? I guess A is just not about -

    EXEC SP1
    EXEC SP2
    EXEC SP3
    EXEC SP4
    EXEC SP5

    Thanks

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    create proc dbo.A as
    declare @rc int, @error int, @cmd varchar(255)
    set @error = 0
    EXEC @rc = SP1
    if @rc != 0 set @error = 1
    EXEC @rc = SP2
    if @rc != 0 set @error = 2
    EXEC @rc = SP3
    if @rc != 0 set @error = 3
    EXEC @rc = SP4
    if @rc != 0 set @error = 4
    EXEC @rc = SP5
    if @rc != 0 set @error = 5

    if @error = 1 begin
    set @cmd = 'Failed to execute SP' + cast(@error as varchar(10))raiserror (@cmd, 15, 1)
    return (1)
    end
    return (0)

  3. #3
    Join Date
    Sep 2003
    Posts
    176
    Thanks. I shall test it soon and let you know if I have any problems.

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    If more than one fails this logic will report the last one. In order to capture all failed you'll have to create a temp table (either # or @) and insert a record with corresponding info after each SP fired. At the end of execution check for presence of rows in that table and if found, - construct the error message based on contents.

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Yeah, but if you have to rollback, your logging would rollback as well, and you'll have no idea...

    echo out to a text file

    Also some errors just raise and you can't trap them....I wonder what @rc would be set to in those cases...NULL?

    @@ERROR might be non zero in that case....

    I'll have to test it....

    Vivek: Let us know how it goes...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •