PS running numerous SQL scripts

PowerShell Help
1

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.

2

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

3

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.

4

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.

5

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.