Replacing NAS with SQL Jobs and NAV Web service

Many companies, running Dynamics NAV, use NAS (Navision Application Server) to automate certain processes. Some companies use NAS to schedule to run many jobs at night. Others use it for integration where NAS periodically, using timer automation, checks a folder to process certain files throughout the day. Others use NAS to monitor message queue, (MSMQUEUE), for integration with web or 3rd party system.
The solution I will be describing here can replace NAS in the first two scenarios. Scheduling Jobs at night and periodically running on a timer. There are many advantages to using SQL Jobs with web service than NAS. The first advantage is reliability. SQL Jobs is more reliable and stable. Clients already use SQL Jobs to do nightly backups, rebuild indexes, update statistic. I’ve seen many instances where NAS stops working and had to be scheduled to be rebooted every night. A second advantage is intuitive/Familiar Scheduling user interface. Most SQL Administrators are familiar with sql and how to schedule jobs. A third advantage is that you can schedule jobs for multiple companies, where as for NAS you need one instance for each company. A forth advantage is parallel scheduling. You can schedule to run two processes that do not lock each other out at the same time. For example you can run a report and run Adjust Cost at the same time. A Fifth advantage is sequential scheduling with other SQL Jobs. For example clients run at night adjust cost routine, and would like afterwards to backup the file and rebuild the indexes. With current NAS solution you cannot schedule SQL Backup to start automatically after Adjust cost has been run and guess how long it usually takes and setup the time for nightly backups. If a process takes longer than usual and two jobs overlap, sql kills one of the jobs. Another advantage is notification of jobs through mail for example.
This solution can also be used for integration with 3rd party systems, where the 3rd party system connects to sql directly inserts some data into a staging table and calls Navision web service through a stored procedure. Basically the solution allows you to connect to NAV web service using TSQL. The Solution is built in SQLCLR as stored procedure.
Initially I started creating a SQL CRL Project in Visual Studio and added web reference to Navision Web service. I wrote the following code


NavJobScheduler.NavWebService.RunObject MyService = new NavJobScheduler.NavWebService.RunObject();
MyService.UseDefaultCredentials = true ;
MyService.Url = WebServiceURL;
bool Success = false;
Success = MyService.RunJob("Codeunit", 50000);

But quickly found out that sql does not allow dynamic XML Serialization is not allowed in SQL Server. You have to build the dll file outside of visual studio in command prompt using
csc /t:library StoredProc.cs WebService.cs
sgen /a:StoredProc.dll

After following the process and trying to run the stored Process in SQL Server Management Studio, I ran into another problem with web service: authentication. SQL Server Agent running the job do not pass the windows users to SQLCLR stored Procedure. I had to create new credentials and assign it to web Service.


System.Net.CredentialCache myCredentials = new System.Net.CredentialCache();
NetworkCredential netCred = new NetworkCredential("UserID", "password","domain");
myCredentials.Add(new Uri(MyService.Url), "NTLM" , netCred);
MyService.Credentials = myCredentials;


Notice above that I created a NTLM credentials. I could create a Kerberos credentials, but unfortunately it doesn’t work under SQLCLR. I had to change the Dynamics NAV config file and enable NTLM. In addition, since this stored proc was access an external system, I had to change the Permission Level on the Project property to External.
After making all these changes, I was finally able to connect to NAV web service and execute Navision Business logic.
Using web service as reference is nice but it makes it cumbersome to work with. You have to compile your code manually and deal with two dll files. So I decided to use lower level classes and build the xml file manually and connect to the web service.
Here is the code.

using System;
using System.Net;
using System.IO;
using System.Xml;

}
};


Here is how it’s called from SQL Server Management Studio in TSQL.
EXEC NavJobScheduler 'codeunit',50010,'USERID','password','Domain','http://localhost:7047/DynamicsNAV/WS/KRONUS2009SP1/Codeunit/RunObject'

You can schedule a SQL job as shown below and create a schedule for it.

In Dynamics NAV I’ve created a new codeunit 50000 and published it as web service.

With the solution above many clients can now skip using NAS altogether or use this in conjunction with NAS.
I’ve attached the Visual Studio Project with source Code.