function ExecuteSqlQuery ($Server, $Database, $SQLQuery) {
$Datatable = New-Object System.Data.DataTable
$Connection = New-Object System.Data.SQLClient.SQLConnection
$Connection.ConnectionString = "server='$Server';database='$Database';trusted_connection=true;"
$Connection.Open()
$Command = New-Object System.Data.SQLClient.SQLCommand
$Command.Connection = $Connection
$Command.CommandText = $SQLQuery
$Reader = $Command.ExecuteReader()
$Datatable.Load($Reader)
$Connection.Close()
return $Datatable
}
Cls
$packagetoremove = Get-Content -LiteralPath "D:\Script-New\ToremovePackagesFromInProgressDPs\Input.txt"
$count = $report.count
$series = 1
Foreach ($p in $packagetoremove)
{
[string] $Server= "Servername"
[string] $Database = "CM_123"
[string] $UserSqlQuery= $("SELECT vSMS_DPStatusDetails.PackageID,v_Package.Name ,vSMS_DPStatusDetails.DPName,CASE
WHEN vSMS_DPStatusDetails.MessageState = 1 THEN 'Success'WHEN vSMS_DPStatusDetails.MessageState = 2 THEN 'InProgress'WHEN vSMS_DPStatusDetails.MessageState = 4 THEN 'Failed'END AS [State]
FROM vSMS_DPStatusDetails INNER JOIN
v_Package ON vSMS_DPStatusDetails.PackageID = v_Package.PackageID
WHERE vSMS_DPStatusDetails.PackageID = 'CM0004DF' and vSMS_DPStatusDetails.MessageState != '1'
GROUP BY vSMS_DPStatusDetails.DPName, vSMS_DPStatusDetails.MessageState,vSMS_DPStatusDetails.PackageID,v_Package.Name
ORDER BY State")
# declaration not necessary, but good practice
$Report = New-Object System.Data.DataTable
$Report = ExecuteSqlQuery $Server $Database $UserSqlQuery
Foreach ($app in $report)
{
$pkgid = $app.PackageID
$dp = $app.DPName
$appname = $app.Name
Write-Host "Removing " $appname" from " $dp ------- $series " From total of " $count
Remove-CMContentDistribution -ApplicationName '$appname' -DistributionPointName $dp -Force
$app = " "
$series++
}
$p = " "
$report = " "
}
