March 17, 2019 at 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.
March 17, 2019 at 6:26 am
Bruin - Sunday, March 17, 2019 4:54 AMI 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.
March 17, 2019 at 11:51 am
That sounds like a great solution any hints\examples to get me started?
Thanks.
March 17, 2019 at 4:09 pm
Bruin - Sunday, March 17, 2019 11:51 AMThat 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
March 17, 2019 at 5:15 pm
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?
March 18, 2019 at 7:03 am
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
March 18, 2019 at 10:33 am
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!!
March 18, 2019 at 6:41 pm
Is that the correct way from Batch to run the process?
April 12, 2019 at 10:54 am
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.
April 12, 2019 at 10:57 am
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 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply