????????????
????????????????????У?????????????????????????????????????????????????????????????????????????????????????????
?????????????????????????SQL SERVER?й????????????????????????????????Server??????Job???м???
??????????
????1?????????????е?SQL Server????Job?????????????????????????????????????????
????2???????????????????
?????????
????????????????????????????????????????????
????<1>??????????????????????????JOb?????????????????????????????????????????????
????<computernames>
????<computername>
????wuxuelei-pc
????</computername>
????</computernames>
????????????????computername.xml???????????????????????????????????????????????????????????????????????????????
????<2>????Power Shell???????????????????Job???????????Job??????????????????????????????????????????γ?????????
??????????￡?
$server = "(local)"
$uid = "sa"
$db="master"
$pwd="password"
$mailprfname = "TestMail"
$recipients = "787449667@qq.com"
$subject = "????????????Щ????????Job????????"
$computernamexml = "F:PowerShell???????computername.xml"
function GetServerName($xmlpath)
{
$xml = [xml] (Get-Content $xmlpath)
$return = New-Object Collections.Generic.List[string]
for($i = 0;$i -lt $xml.computernames.ChildNodes.Count;$i++)
{
if ( $xml.computernames.ChildNodes.Count -eq 1)
{
$cp = [string]$xml.computernames.computername
}
else
{
$cp = [string]$xml.computernames.computername[$i]
}
$return.Add($cp.Trim())
}
$return
}
function GetAlterCounter($xmlpath)
{
$xml = [xml] (Get-Content $xmlpath)
$return = New-Object Collections.Generic.List[string]
$list = $xml.counters.Counter
}
function CreateAlter($message)
{
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$CnnString ="Server = $server; Database = $db;User Id = $uid; Password = $pwd"
$SqlConnection.ConnectionString = $CnnString
$CC = $SqlConnection.CreateCommand();
if (-not ($SqlConnection.State -like "Open")) { $SqlConnection.Open() }
$cc.CommandText=
" EXEC msdb..sp_send_dbmail
@profile_name  = '$mailprfname'
??@recipients = '$recipients'
??@body = '$message'
??@subject = '$subject'
"
$cc.ExecuteNonQuery()|out-null
$SqlConnection.Close();
}
$report = ""
$item = New-Object Collections.Generic.List[string]
$names = GetServerName($computernamexml)
foreach($cp in $names)
{
$srv=New-Object "Microsoft.SqlServer.Management.Smo.Server" "(local)"
$item=$srv.jobserver.jobs | where-object {$_.lastrunoutcome -eq "Failed" -and $_.isenabled -eq $TRUE} |  select OriginatingServer??name??Description??lastrunoutcome??lastrundate??JobSteps
#Write-Host $item.JobSteps.name
$report += " ????????"+$item.OriginatingServer+"  Job?????"+$item.name+"  Job??????"+$item.Description +"  Job??????????"+$item.lastrunoutcome  +"  Job?????????"+$item.lastrundate +"  Job????????????"+$item.JobSteps.name  + "`n"
}
#????????
CreateAlter $report
???????????????????????????????????????????????Job???
????????????????????Job???????????????м??
?????????????У?????????????
????1????????????SQL Server???????????巽????????????????????
????2????????????????????????????????????????