Sql-server – Executing SQL Script through PowerShell gets inconsistent results vs in SSMS

powershellsql serverssms

I'm hoping somebody here can give me some advice.

We use a software suite to manage and monitor PC inventory, including which executables get executed. One of the "features" (not a bug, since it's working as designed per support) is that PC's that drop out of management and removed from inventory retain the executed binaries info in one of the tables. Fun things happen when that table gets to about 70 Million records, namely opening any inventory record times out (the app's hard-coded timeout is 5 minutes).

So I wrote an SQL script to remove records that were orphaned from devices that are no longer being managed. Script is as follows:

USE [DB];
SET NOCOUNT ON;

DECLARE @cnt BIGINT,
        @msg VARCHAR(100);
SET @cnt = 1;
SET @msg = '';

WHILE EXISTS
    (
      SELECT TOP 1 * FROM [FileInfoInstance]
      WHERE FileInfo_Idn not in (select fileinfo_idn from FileInfo)
    )
BEGIN

    SET @msg = 'Deleting 1,000 records from FileInfoInstance (iteration #' + CAST(@cnt AS VARCHAR(500)) + ')';
    RAISERROR(@msg, 10, 1) WITH NOWAIT;

    BEGIN TRAN;
    DELETE TOP (1000) FROM [FileInfoInstance]
    WHERE FileInfo_Idn NOT IN (select fileinfo_idn from FileInfo);
    COMMIT TRAN;

    SET @cnt = @cnt + 1;

END;

I have a different WHILE loop, but I'm doing this for testing because the end result is exactly the same.

Executing this in SSMS works flawlessly, however to keep the table from getting like this again I'd like to automate the process and send an email when it's completed each week. So I have the DB code above saved in .SQL, and a powershell script I'd like to execute weekly on Saturdays at midnight:

[CmdletBinding()]
param (
    [Parameter(Mandatory=$true)]
    [string] $SqlScript = $null,
    [Parameter(Mandatory=$true)]
    [string] $SqlUser = $null,
    [Parameter(Mandatory=$true)]
    [string] $SqlPassword = $null
)
cls
import-module sqlserver

$LogPath = "C:\Temp\DB Maintenance"
$FullLogFile = "$LogPath\SLM Orphaned Record Cleanup $(get-date -Format yyyy-MM-dd-HH-mm-ss).log"
$SqlOutput = "$LogPath\Orphaned SLM Cleanup $(get-date -Format yyyy-MM-dd-HH-mm-ss).rpt"


if(-not (Test-Path -Path "C:\Temp\DB Maintenance")){
    New-Item -Path "C:\Temp\DB Maintenance" -ItemType directory
}

function LogTaskStatus($message)
{
    Add-Content "$FullLogFile" "$(get-date -Format yyyy-MM-dd' 'HH:mm:ss)    Message:    $message"
}

function Main
{
    LogTaskStatus("Stopping services...")
    Stop-Service -DisplayName *AppName* -Verbose -Force

    LogTaskStatus("Stopping IIS...")
    iisreset /stop
    
    LogTaskStatus("Executing the SQL Script")
    Try
    {
        Invoke-Sqlcmd -ServerInstance "SQL_SERVER_HOST" -Database "DB" -InputFile "C:\Scripting\WIP\OrphanedRecordCleanup.sql" -Username 'DB_Admin_User' -Password 'DB_Pass' -Verbose
        LogTaskStatus("SQL Script Executed Successfully:")
    }
    Catch
    {
        LogTaskStatus("An error occurred running $SqlScript.")
        LogTaskStatus("Error: $_")
    }
    
    LogTaskStatus("Starting IIS...")
    iisreset /start

    LogTaskStatus("Starting Services...")
    Start-Service -DisplayName *AppName* -Exclude "Specific App Service" -Verbose

    LogTaskStatus("Sending mail about the task")
    Send-MailMessage -From 'address@mail.com' -To 'me@mail.com' -Subject 'Weekly Maintenance Orphaned Record Cleanup Results' -Body "It did the thing" -Attachments "$FullLogFile" -Priority High -SmtpServer "smtp.mail.com"
}

Main

It works, except it shows the first iteration:

Deleting 1,000 records from FileInfoInstance (iteration #1)

And then…. nothing. I let it sit for over an hour, and it never ran a second iteration, and never completed with starting the services again. I can run the exact same script through SSMS and it takes right off and completes within seconds.

I'll admit, my knowledge of SQL is limited mostly to MySQL with WordPress, and while I was proud of being able to build a working script to clear up the initial orphaned records, I'd rather automate this and not work on it on the weekends unless something is clearly not operating correctly. Such as right now. Which is why I'm typing this at 9:07 PM EDT on a holiday weekend after about 6 hours of tinkering because it didn't work.

I do not know what version of SQL Server my company's DBA's are running or the Windows Server version, but I do know that in June we completed a project to upgrade all servers in our domain to at least Server 2016 and all Microsoft SQL Server versions are kept to at least the minimum supported version by Microsoft (though often our DB servers are within a year of release, so I expect this to be SQL Server 2017 or later).

Best Answer

Add a T-SQL TRY/CATCH block so that the loop doesn't continue indefinately following an error. Invoke-SqlCmd buffers SQL Server error messages so you won't see errors until the PowerShell script completes.

USE [DB];
SET XACT_ABORT ON;
SET NOCOUNT ON;

DECLARE @cnt BIGINT,
        @msg VARCHAR(100);
SET @cnt = 1;
SET @msg = '';

BEGIN TRY
    WHILE EXISTS
        (
        SELECT 1 FROM [FileInfoInstance]
        WHERE FileInfo_Idn NOT IN (SELECT fileinfo_idn FROM FileInfo)
    )
    BEGIN

        SET @msg = 'Deleting 1,000 records from FileInfoInstance (iteration #' + CAST(@cnt AS VARCHAR(500)) + ')';
        RAISERROR(@msg, 10, 1) WITH NOWAIT;

        BEGIN TRAN;
        DELETE TOP (1000) FROM [FileInfoInstance]
        WHERE FileInfo_Idn NOT IN (select fileinfo_idn from FileInfo);
        COMMIT TRAN;

        SET @cnt = @cnt + 1;

    END;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0 ROLLBACK;
    THROW;
END CATCH;

As to why the T-SQL script succeeds from SSMS but not PS script, one possible cause is the DB_Admin_User login doesn't have delete permissions and you're using a different account with SSMS. The error handling above will show errors related to permissions as well as others.

Related Question