Starting a NAV process from SQL. Ideas?

bbrown

I have a few spots where I'm using ADO to fire off SQL processes from NAV. The example below is used to start a SQL index maintenence job following a nightly adjust cost. Since I never know how long Adjust Cost will run this cannot be simply scheduled. Instead I run the below code from the Adjust Cost process. This has been working fine. {a thanks to Waldo for some very helpful ADO examples}

Now I have the opposite situation. I need to start a NAV process after a SQL process completes. Anybody have some ideas on how this could be accompished. I was thinking of using NAS (possibly with message queue). Thoughts?

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

kine

You will definitely need NAS. How to activate it - more ways:

1) WebService (I hope that it is possible to call WS from SQL procedure, definitely yes, if you create the procedure in Visual Studio...)
2) Some flag in some table (NAS will periodically check the flag, SQL will set the flag)
3) MSMQ

bbrown

Thanks for the reply.

The site is 5.0 SP1 so option 1 is currently not an option. Options 2 and 3 are both possibilities. Another option I'm thinking about is using a Job Queue (a variatio of NAS) task that fires off and watches a table for a specific record, then continues on once it finds that record. This would be similar to your option 2. This process only needs to run once a week (on the weekend) and it does not need to be immediately after the SQL process. If there was a short delay it would be fine. The issue is more not having someone needing to hang around till the SQL process completes, so they can start the NAV process.

kine

1 is option for NAV 5.0 too. It is options for all versions having NAS. It is not problem to create DLL to be able to use NAS as WebService provider. Just search the forum or internet, there are posts and blog about how to create WebServices for older versions of NAV.