skip function if completed successfully

  • Bruin

    SSC Eights!

    Points: 894

    I have a function where I'm executing in succession a list of sql queries\updates. The function has try\catch so if it detects error. My question
    is( hopefully asking correctly) is there a way if one fails(without editing script each time) to restart\rerun just the failed one?

    example:  3 updates

    Update-SqTable -Instancename localhost -Database copydb1 -Sourcefile 'c:\invhead.sql'
    Update-SqTable -Instancename localhost -Database copydb1 -Sourcefile 'c:\invdtl.sql'
    Update-SqTable -Instancename localhost -Database copydb1 -Sourcefile 'c:\invtry.sql'

    If update 1 completes, but update 2(invdtl file)fails. Can I on next execution skip(invhead file process)?

     Thanks.

  • Jonathan AC Roberts

    SSCoach

    Points: 16683

    Bruin - Sunday, March 17, 2019 4:54 AM

    I have a function where I'm executing in succession a list of sql queries\updates. The function has try\catch so if it detects error. My question
    is( hopefully asking correctly) is there a way if one fails(without editing script each time) to restart\rerun just the failed one?

    example:  3 updates

    Update-SqTable -Instancename localhost -Database copydb1 -Sourcefile 'c:\invhead.sql'
    Update-SqTable -Instancename localhost -Database copydb1 -Sourcefile 'c:\invdtl.sql'
    Update-SqTable -Instancename localhost -Database copydb1 -Sourcefile 'c:\invtry.sql'

    If update 1 completes, but update 2(invdtl file)fails. Can I on next execution skip(invhead file process)?

     Thanks.

    I think you'd need to log the progress of the function to a table. The function would read the table to see where it needs to restart from and write the restart point (the next query) to the table after each successful statement.

  • Bruin

    SSC Eights!

    Points: 894

    That sounds like a great solution any hints\examples to get me started?

     Thanks.

  • Jonathan AC Roberts

    SSCoach

    Points: 16683

    Bruin - Sunday, March 17, 2019 11:51 AM

    That sounds like a great solution any hints\examples to get me started?

     Thanks.

    I was thinking something like this:
    CREATE TABLE dbo.CheckStep
    (
      Process nvarchar(20) NOT NULL PRIMARY KEY CLUSTERED,
      NextStep int NOT NULL
    );
    GO
    INSERT INTO dbo.CheckStep(Process, NextStep) VALUES ('my3Scripts',1)
    GO

    /* Script c:\invhead.sql */
    DECLARE @NextStep int
    SELECT @NextStep=NextStep
    FROM dbo.CheckStep
    WHERE Process = 'my3Scripts'

    IF @NextStep <> 1 RETURN

    BEGIN TRY
      /* The sql you want to run */
      UPDATE myTable
      SET col1 = 'Hello'

      /*  Update to next step */
      UPDATE dbo.CheckStep SET NextStep=2 WHERE Process = 'my3Scripts'
    END TRY

    BEGIN CATCH
    END CATCH

    GO

    /*  Script c:\invdtl.sql */
    DECLARE @NextStep int
    SELECT @NextStep=NextStep
    FROM dbo.CheckStep
    WHERE Process = 'my3Scripts'

    IF @NextStep <> 2 RETURN

    BEGIN TRY
      /* The sql you want to run */
      UPDATE myTable
      SET col1 = 'How are you'

      /*  Update to next step */
      UPDATE dbo.CheckStep SET NextStep=3 WHERE Process = 'my3Scripts'
    END TRY

    BEGIN CATCH
    END CATCH
    GO

    /*  Script c:\invtry.sql */
    DECLARE @NextStep int
    SELECT @NextStep=NextStep
    FROM dbo.CheckStep
    WHERE Process = 'my3Scripts'

    IF @NextStep <> 3 RETURN

    BEGIN TRY
      /* The sql you want to run */
      UPDATE myTable
      SET col1 = 'Goodbye'

      /*  Update to next step */
      UPDATE dbo.CheckStep SET NextStep=1 WHERE Process = 'my3Scripts'
    END TRY

    BEGIN CATCH
    END CATCH

  • Bruin

    SSC Eights!

    Points: 894

    Thanks!! I'm going to try and incorporate that with the Powershell  function. Can you call the function in a batch(cmd) file
    and supply different values values at run time rather than have them hard coded in the function definition?

  • jonathan.crawford

    SSCertifiable

    Points: 6347

    https://stackoverflow.com/questions/36999575/powershell-loop-through-format-table

    -------------------------------------------------------------------------------------------------------------------------------------
    Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses

  • Bruin

    SSC Eights!

    Points: 894

    This is where I'm confused, to be able to pass in the params do I comment out the lines in function, and it will use my params when the cmd(Batch script is called)?

    #Update-SqTable -Instancename localhost -Database copydb1 -Sourcefile 'c:\invhead.sql'
    #Update-SqTable -Instancename localhost -Database copydb1 -Sourcefile 'c:\invdtl.sql'
    #Update-SqTable -Instancename localhost -Database copydb1 -Sourcefile 'c:\invtry.sql'

    That's why I asked for help with the script above to log script success. If I can pass params to batch script I can just run different loads in different steps.

    example cmd file 

    :step1
    powershell path\script  -Instancename localhost -Database copydb1 -Sourcefile 'c:\invhead.sql'
    ...   on success goto step2
    :step2
    powershell path\script  -Instancename localhost -Database copydb1 -Sourcefile 'c:\invhead.sql'
      .. on success goto QUITOK

    If I comment out steps in Function it never runs.

     Thanks for replies!!

  • Bruin

    SSC Eights!

    Points: 894

    Is that the correct way from Batch to run the process?

  • garry.lovesey

    SSC Veteran

    Points: 289

    It is hard to understand exactly what you want to do.

    Is this going to be interactive or not?

    If it is interactive then you could create a menu structure to be fed out to Out-GridView which could be used to run the functions.

    If it is going to be run in batch then you should get the functions to check for the existence of the file and just skip doing it.

    You may also want to consider backing the change out if something fails. This would leave everything in a good state and ensure that the script would always need to be started from the top.

  • garry.lovesey

    SSC Veteran

    Points: 289

    Here is a quick example of using Out-GridView as a menu:

     

    $Menu = [ordered]@{

    1 = 'Do something'

    2 = 'Do this instead'

    3 = 'Do whatever you  want'

    }

     

    $Result = $Menu | Out-GridView -PassThru  -Title 'Make a  selection'

    Switch ($Result)  {

    {$Result.Name -eq 1} {'Do something'}

    {$Result.Name -eq 2} {'Do this instead'}

    {$Result.Name -eq 3} {'Do whatever you  want'}

    }

     

    The full post is here: https://mcpmag.com/articles/2016/02/17/creating-a-gui-using-out-gridview.aspx

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply