Using ADO to kick-off SQL jobs from NAV

The sample code below is an example of how ADO can be used to kick-off a SQL Agent job from NAV. This can be helpful when you need to run things but they don't fit into a fixed schedule. This particular code is called from Adjust Cost, but I'm sure there are other uses. It allows us to run nightly SQL maintenance following the completion of Adjust Cost.

Name	                     DataType	        Subtype	        Length
ADOConnection	        Automation	        'Microsoft ActiveX Data Objects 2.8 Library'.Connection	
GLSetup	                     Record	        General Ledger Setup	
ADOCommand	        Automation	        'Microsoft ActiveX Data Objects 2.8 Library'.Command	
ADOParameter	        Automation	         'Microsoft ActiveX Data Objects 2.8 Library'.Parameter	
lvarActiveConnection      Variant		


OnRun()
IF ISCLEAR(ADOConnection) THEN;
  CREATE(ADOConnection);

ADOConnection.ConnectionString := GetConnectionString;
ADOConnection.Open;

IF ISCLEAR(ADOCommand) THEN
  CREATE(ADOCommand);

lvarActiveConnection := ADOConnection;
ADOCommand.ActiveConnection := lvarActiveConnection;

ADOCommand.CommandText := 'sp_start_job';
ADOCommand.CommandType := 4;
ADOCommand.CommandTimeout := 0;

ADOParameter := ADOCommand.CreateParameter('@job_name', 200, 1, 50, GLSetup."Index Job Name");
ADOCommand.Parameters.Append(ADOParameter);
ADOCommand.Execute;

ADOConnection.Close;
---------------------

GetConnectionString() : Text[1024]
GLSetup.GET;
GLSetup.TESTFIELD("Server Name");
GLSetup.TESTFIELD("Index Job Name");

ConnectString := 'Driver={SQL Native Client};'
      + 'Server=' + GLSetup."Server Name" + ';'
      + 'Database=msdb;Trusted_Connection=yes;';
EXIT(ConnectString);