Run SQL Job from NAVISION

Business Central/NAV Developers Forum
1

Hi all! I want to run a SQL Job from NAVISION (by Job Scheduler). The possibility I found is this:ReturnCode := SHELL('osql.exe -S MyServer -E -d msdb -q "EXEC sp_start_job @job_name = [MySQLJob]" -c -b'); But … … if I use -q then the Command-Window is opened - and NAVISION waits until it is closed (modal) - but this window is not closed automatically when the SQL job finished, so NAVISION waits until someone manually closes it. … if I use -Q then the Command-Window is closed immediatly, the SQL job runs in the background - but NAVISION does not wait for it (non modal) What I need is, that the window is opened, NAVISION waits until the SQL job is finished (modal) and then the window should be closed automatically. How can I do this? Thanx in advance! Kind regards,

2

I’m not sure, but what about using a .bat file to run the osql.exe command. The contents of the batch file would be: osql.exe -S MyServer -E -d msdb -q "EXEC sp_start_job @job_name = [MySQLJob]" -c -b exit Then call this batch file in Navision instead of osql.exe directly. The EXIT command should do the trick… I guess… [:I]

3

Hi Nelson! Thanks for your advice, but it did not solve the problem [V] it’s the same behaviour with a .bat or a SHELL …

4

This is what I do now: - Execute the sp_start_job via OSQL - Within a loop, I execute sp_help_job to get the current job-status - I write this status to a local file to be analyzed by the NAVISION program - If the status shows that the job is finished, I exit the loop and continue the processing … Well, that’s the “old” version (from yesterday evening [;)]) … the “new” way (from today) is quite similar, but using MS ADO to execute the statements and without using a file, as I can analyze the status now “online” … Regards,

5

… and of course I appreciate any different or better solution! [:P]

6

Have You read this: http://www.mbsonline.org/forum/topic.asp?TOPIC_ID=12693 /Lars

7

Hi Lars! Thank for the advice! The solutions discussed there seem to be very similar to my ADO solution now, this shows me that this might be the right way …

8

Hi Guys.

I’ve been reading this topic and have tried the first solution mentioned;

ReturnCode := SHELL('osql.exe -S MyServer -E -d msdb -q "EXEC sp_start_job @job_name = [MySQLJob]" -c -b');

When I try to run this, Navision complains about the filename containing a character that cannot be used. I have just copied exactly what you posted - so what’s the problem?

BTW - My Nav version is UK v4 sp3 with a SQL back-end.