PS running numerous SQL scripts

This topic contains 4 replies, has 2 voices, and was last updated by Profile photo of Paul Tracey Paul Tracey 1 year, 3 months ago.

  • Author
    Posts
  • #33323
    Profile photo of Paul Tracey
    Paul Tracey
    Participant

    I am writing a script to iterate through numerous SQL scripts. These have been generated through a Redgate tool, and will basically migrate a database. There are several hundred of these.

    I have written the basic script that iterates through them, and it works. I am using the Invoke-Command (with a ScriptBlock), since the scripts have already been generated.

    However, there is a lot of output generated, all the SQL table drops etc, and I really only want to see any output if there has been a failure. In such a situation, I would like to stop execution. I guess I would us a try-catch method.

    So, given the above, I am after any tips regarding verbosity and error-catching.

    Note, I am doing the PluralSight course in PS, but it is a bit basic so far.

  • #33324
    Profile photo of Dave Wyatt
    Dave Wyatt
    Moderator

    How are you executing the sql scripts? That will determine how you do error handling as they're running.

  • #33356
    Profile photo of Paul Tracey
    Paul Tracey
    Participant

    It is probably easiest to illustrate with the code:

     
     #Amend the following as necessary:
    $SQLServer		= "XXXX\XXXXX";
    $SQLDatabase	= "XXXXX";
    $SQLUser		= "XXXXXX";
    $SQLPassword	= "XXXXXX";
    
    $SQLFolderPath	= "C:\XXXXXXX";
    
    $FolderTop      =$(get-childitem "$SQLFolderPath");
    
    clear-host
    
    foreach($Folder in $FolderTop)
    {
    
        $Message			= "Processing Folder $Folder";
        $MessageUnderLine	= "=" * $Message.Length;
    
        Write-Host `n"Processing Folder $Folder`n$MessageUnderLine`n";
    
    #    $Folder2=$(get-childitem -Filter "*Initial.sql" $Folder.FullName);
        $Folder2=$(get-childitem $Folder.FullName -Include *.sql -Recurse);
        foreach($File in $Folder2)
        {
            $MessageF			= "Processing File $File";
            $MessageUnderLineF	= "=" * $MessageF.Length;
            Write-Host "Processing File $File`n$MessageUnderLineF`n";
    
             $scriptblock = {param($p1, $p2, $p3, $p4); `
                            sqlcmd -S `"$p1`" `
                                   -U `"$P2`" `
                                   -P `"$P3`" `
                                   -i `"$P4`" }
    
            Invoke-Command -ScriptBlock $scriptBlock -ArgumentList $SQLServer, $SQLUser, $SQLPassword, $file.FullName
            
    
        }
    
    }
    

    Note, I have managed to suppress the output from SQLCMD using the -o switch to send the output to a file.

    Maybe I can use a try-catch inside the script block. Or maybe Invoke-Command is the wrong way to go. They key thing is that the SQL files are all generated already, so I don't need any SQL in the script itself.

    Any ideas greatly appreciated.

  • #33359
    Profile photo of Dave Wyatt
    Dave Wyatt
    Moderator

    Okay, two things here. One, there's probably no advantage to using Invoke-Command, since you're still running it locally. You can just run sqlcmd directly, without the scriptBlock variable:

        foreach($File in $Folder2)
        {
            $MessageF			= "Processing File $File";
            $MessageUnderLineF	= "=" * $MessageF.Length;
            Write-Host "Processing File $File`n$MessageUnderLineF`n";
    
             $scriptblock = {param($p1, $p2, $p3, $p4); `
                            sqlcmd -S `"$p1`" `
                                   -U `"$P2`" `
                                   -P `"$P3`" `
                                   -i `"$P4`" }
    
            sqlcmd -S $SQLServer -U $SQLUser -P $SQLPassword -i $file.FullName -b
        }
    

    You shouldn't need to worry about injecting quotation marks; PowerShell will do that for you when it calls external commands.

    Next is error handling. When you're working with external commands like sqlcmd.exe, you'll want to check the value of the automatic $LASTEXITCODE variable right after you execute the command. You may notice that I added the "-b" switch to the call to SqlCmd; that's from glancing at the documentation ( https://msdn.microsoft.com/en-us/library/ms162773.aspx ) and noting that you need -b in order to make sqlcmd return an exit code other than zero when there's a problem.

  • #33367
    Profile photo of Paul Tracey
    Paul Tracey
    Participant

    Thanks for that Dave. I didn't realise there was a 'b' switch. that has worked a treat, many thanks.

    I kept the script block, as it just looks neater. I simply check the $LASTEXITCODE, then break out of both loops displaying an appropriate error. Don't like breaking out of nested loops, but this is a one-off task.

    I did try to get INVOKE-SQLCMD working, but the import module failed bizarrely.

    Once again, many thanks.

You must be logged in to reply to this topic.