agreed, thank you 🙏🏼

I was curious and asked Claude to do some research as to why it happens and the answer sounds plausible.

Here's the TL;DR:

Why it fails on AppVeyor: SMO's Database.ExecuteNonQuery internally loads a native C++ DLL called Microsoft.SqlServer.BatchParser.dll to parse GO separators — even though you're already pre-splitting batches in PowerShell. If that DLL is missing or version-mismatched on AppVeyor's image (very common), every batch execution fails. This is documented in dbatools issues #4073 and #5356. It's not a batch size limit — sp_WhoIsActive's ~150KB script is way under SQL Server's ~256MB cap.

Why the event log explodes: When ExecuteNonQuery throws, SMO stuffs the entire SQL batch text into the exception. Passing that via -ErrorRecord $_ to Stop-Function → AppVeyor writes it to the Windows Event Log → hits the 32,766-char hard limit → secondary ArgumentException.

Fix both problems at once: Replace SMO ExecuteNonQuery with raw ADO.NET SqlCommand:

foreach ($batch in $batches) {
    try {
        $cmd = $server.ConnectionContext.SqlConnectionObject.CreateCommand()
        $cmd.CommandText = $batch
        $cmd.CommandTimeout = 300
        $null = $cmd.ExecuteNonQuery()
    } catch {
        $msg = $_.Exception.Message.Substring(0, [Math]::Min($_.Exception.Message.Length, 500))
        Stop-Function -Message "Failed to install stored procedure: $msg" -Continue -Target $instance
    }
}

This eliminates the BatchParser.dll dependency entirely and truncates error messages so they never overflow the event log. No more chasing environment-specific DLL issues across CI platforms.

Then we should change our methods .Query() and .Invoke() on the Server SMO and Database SMO.