Using ADO to kick-off SQL jobs from NAV

bbrown

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);

ara3n

I have blogged about the other way around using SQL Agent jobs schedule to run NAV jobs.

Here is the link

http://mibuso.com/blogs/ara3n/2009/11/1 ... b-service/

David_Singleton

Thanks to both of you.

I am in a tuning project right now, and trying to decide between one of these approaches, your posts will make it easier for me to review both options.

Thanks.