GitHub Copilot Chat just blew my mind: SQL -> SMO

[potatoqualitee]

OMGGGG, yall are gonna be so pumped! Look at this wildness. How in the world. HOW?

Just asked

what is the powershell / SMO equivalent of this sql query:

EXEC msdb.dbo.sp_add_alert @name=N'Severity 017 - Insufficient Resources', @message_id=0, @Severity=17, @enabled=1, @delay_between_responses=60, @include_event_description_in=1, @job_id=N'00000000-0000-0000-0000-000000000000'; GO > EXEC msdb.dbo.sp_add_notification @alert_name=N'Severity 017 - Insufficient Resources', @operator_name=N'SQL Server DBA Team', @notification_method = 7;

AND IT DID IT OR CLOSE ENOUGH TO FOOL ME???

# Load the SQL Server Management Objects (SMO) assembly
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null

# Set up the connection to the SQL Server instance
$serverInstance = "localhost"
$server = New-Object Microsoft.SqlServer.Management.Smo.Server($serverInstance)

# Set up the alert properties
$alertName = "Severity 017 - Insufficient Resources"
$messageId = 0
$severity = 17
$enabled = $true
$delayBetweenResponses = 60
$includeEventDescriptionIn = 1
$jobId = "00000000-0000-0000-0000-000000000000"

# Create the alert
$alert = New-Object Microsoft.SqlServer.Management.Smo.Agent.Alert($server.JobServer, $alertName)
$alert.MessageID = $messageId
$alert.Severity = $severity
$alert.Enabled = $enabled
$alert.DelayBetweenResponses = $delayBetweenResponses
$alert.IncludeEventDescriptionIn = $includeEventDescriptionIn
$alert.JobID = $jobId
$alert.Create()

# Set up the notification properties
$notificationMethod = [Microsoft.SqlServer.Management.Smo.Agent.NotificationType]::NetSend
$operatorName = "SQL Server DBA Team"

# Create the notification
$notification = New-Object Microsoft.SqlServer.Management.Smo.Agent.Notification($server.JobServer, $alertName, $notificationMethod, $operatorName)
$notification.Create()

Amazing! Check it out @Matteo-T!

[shueybubbles]

It looks pretty good to me, except for using NetSend as the notification method, because 7 is NotifyAll.

[potatoqualitee]

I wonder if it'd work for your coding. It magically worked for me with New-DbaAgentAlert, which I wrote in bits and pieces bc I already had the structure.