I'm trying to us SQL-DMO in a script to control SQL 2000 jobs. Part of
what I want to do is to manage the job history and remove selected job
history records. I have successfully used the JobHistoryFilter object with
the EnumJobHistory Method to select and display the records I want to
delete. However, passing the same JobHistoryFilter object to the
PurgeJobHistory Method doesn't work! That is, the JobHistoryFilter
doesn't constrain the purge action. I've included a sample of the code and
would appreciate some insight as to how you are supposed to use the
PurgeJobHistory method to delete specific history records.
thanks,
---------------------------
Sample JScript Code:
var oSQLServer = new ActiveXObject("SQLDMO.SQLServer");
var objJob = new ActiveXObject("SQLDMO.Job");
var objJobSchedule = new ActiveXObject("SQLDMO.JobSchedule");
var objJobHistory;
var objJobHistoryFilter;
var qr ;
// Connect to the server
oSQLServer.LoginSecure = true;
oSQLServer.Connect ("ServerName");
// Get the job to modify
objJob = oSQLServer.JobServer.Jobs("JobName");
objJobHistoryFilter = oSQLServer.JobServer.JobHistoryFilter;
objJobHistoryFilter.JobName = objJob.Name;
// I want to remove all the failed history records from the start of the
job
// until the file was found, so set the properties on the JobHistoryFilter
object
objJobHistoryFilter.StartRunDate = '20041116';
objJobHistoryFilter.EndRunDate = '20041116';
objJobHistoryFilter.StartRunTime = '121800';
objJobHistoryFilter.EndRunTime = '131900';
// This code runs correctly, selecting the records according to the
properties set in the JobHistoryFilter object.
qr = oSQLServer.JobServer.EnumJobHistory(objJobHistoryFilter) ;
WScript.echo ("Records = " + qr.Rows);
for (e = 1; e < qr.Rows; e++)
{
WScript.echo(qr.ColumnName(1) + " - " + qr.GetColumnString(e, 1));
WScript.echo(qr.ColumnName(2) + " - " + qr.GetColumnString(e, 2));
WScript.echo(qr.ColumnName(3) + " - " + qr.GetColumnString(e, 3));
WScript.echo(qr.ColumnName(4) + " - " + qr.GetColumnString(e, 4));
WScript.echo(qr.ColumnName(5) + " - " + qr.GetColumnString(e, 5));
WScript.echo(qr.ColumnName(6) + " - " + qr.GetColumnString(e, 6));
WScript.echo(qr.ColumnName(7) + " - " + qr.GetColumnString(e, 7));
WScript.echo(qr.ColumnName(8) + " - " + qr.GetColumnString(e, 8));
WScript.echo(qr.ColumnName(9) + " - " + qr.GetColumnString(e, 9));
WScript.echo(qr.ColumnName(10) + " - " + qr.GetColumnString(e, 10));
WScript.echo(qr.ColumnName(11) + " - " + qr.GetColumnString(e, 11));
WScript.echo(qr.ColumnName(12) + " - " + qr.GetColumnString(e, 12));
WScript.echo(qr.ColumnName(13) + " - " + qr.GetColumnString(e, 13));
WScript.echo(qr.ColumnName(14) + " - " + qr.GetColumnString(e, 14));
WScript.echo(qr.ColumnName(15) + " - " + qr.GetColumnString(e, 15));
WScript.echo(qr.ColumnName(16) + " - " + qr.GetColumnString(e, 16));
WScript.echo(qr.ColumnName(17) + " - " + qr.GetColumnString(e, 17));
}
// Now I pass in exactly the same JobHistoryFilter object and instead of
deleting the
// specified records (records created on 11 Nov 2004 between 12:18 and
13:19), ALL history records are deleted. However, it does seem to
constrain itself by the jobname, i.e. all history records for the specified
job are deleted, but not for every job.
oSQLServer.JobServer.PurgeJobHistory(objJobHistoryFilter);
----------------------------
MS Sql Server LazyDBA home page