Rashed Amini

The ara3n weblog

Archive for the 'webservice' Category

Replacing NAS with SQL Jobs and NAV Web service

14th November 2009

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;

public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void NavJobScheduler(string ObjectType, int ObjectID, string Login, string Password,string Domain,string WebServiceURL)
{
string Body = @"" +
"" +
"" + ObjectType + "" +
"" + ObjectID + "" +
"";

WebRequest request = HttpWebRequest.Create(WebServiceURL);
request.Headers.Add("SOAPAction", @"""urn:microsoft-dynamics-schemas/codeunit/RunObject:RunJob""");
request.ContentType = "application/xml; charset=utf-8";
request.ContentLength = Body.Length;
request.Method = "POST";
System.Net.CredentialCache myCredentials = new System.Net.CredentialCache();
NetworkCredential netCred = new NetworkCredential(Login, Password, Domain);
myCredentials.Add(new Uri(WebServiceURL), "NTLM", netCred);
request.Credentials = myCredentials;

Stream strWrite = request.GetRequestStream();
StreamWriter sw = new StreamWriter(strWrite);
sw.Write(Body.ToString());
sw.Close();

WebResponse wr = request.GetResponse();
HttpWebResponse httpRes = (HttpWebResponse)wr;
Stream s = httpRes.GetResponseStream();
StreamReader sr = new StreamReader(s);
XmlDocument xmlDoc = new XmlDocument();
xmlDoc.Load(sr);

if (xmlDoc.FirstChild.FirstChild.FirstChild.FirstChild.FirstChild.Value != "SUCCESS")
{
throw new Exception("ObjectType " + ObjectType + " ObjectID " + ObjectID.ToString() + " failed with Error: "
+ xmlDoc.FirstChild.FirstChild.FirstChild.FirstChild.FirstChild.Value);
}

}
};

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.
SQL Job Nav Scheduler

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

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.

Posted in Dynamics NAV, webservice | 18 Comments »

Web service Proxy for NAV Web service

4th May 2009

As many of you know Dynamics NAV 2009 is using windows authentication to allow any web services to be consumed. Currently Nav 2009 when it negotiates to negotiate it only uses Kerberos and doesn’t fall back on NTML. This creates connection issues with Non Windows systems. For example Java based system (SOAPUI), or php based website running on Linux servers. To solve this problem you have to write a web service wrapper that allows you to bypass this problem. In this blob I will go through the process to create a WCF .NET web service that runs as windows service and proxies the communications for NAV. In this example I will use soap toolkit instead of referencing NAV web service. But the idea is the same.

In example below I will write a proxy for NAV web service “RunJob” (shown below).

This Codeunit simply takes two parameters, Object type and Object ID and runs that object. In the function I’m simply using “Job Queue Start Codeunit” to run the object and catch any errors which is returned as string.

  • First I will create a new console application in Visual Studio. I will name it NavWinProxy

  • Next rename program.cs to Service.cs

  • I will need to add references to the following assemblies.
    • System.ServiceModel.dll, System.ServiceProcess.dll, System.Configuration.Install.dll

     

  • Next add the following statements to the service.cs
    • using System;
    • using System.Text;
    • using System.ComponentModel;
    • using System.ServiceModel;
    • using System.ServiceProcess;
    • using System.Configuration;
    • using System.Configuration.Install;
    • using System.Net;
    • using System.Xml;
    • using System.IO;
  • Next define service contract as shown in below. Notice that I’ve created a function called RunObject that takes 3 parameters.
    • [ServiceContract(Namespace = “”)]
    • public
      interface
      INavWinProxy
    • {
    • [OperationContract]
    • string RunObject(string ObjectType, int ObjectID, string CompanyName);
    • }
  • Next Implement the service contract in class called RunNavObject as shown below. In the implementation I decided to use webrequest and build the xml document. You can also add the RunJob as web Reference and call it. The only issue is that you have add each company as web reference and call that for that company. You can also dynamically reference the web service. You can read more about it on Freddy’s Blog.
    • public
      class
      NAVService : INavWinProxy
    • {
    • public
      string RunObject(string ObjectType, int ObjectID, string CompanyName)
    • {
    • string Body = @”<?xml version=”"1.0″” encoding=”"utf-8″”?><soap:Envelope xmlns:soap=”"http://schemas.xmlsoap.org/soap/envelope/”">” +
    • @”<soap:Body><RunJob xmlns=”"urn:microsoft-dynamics-schemas/codeunit/RunJob”">” +
    • @”<objectType>” + ObjectType + “</objectType><objectID>” + ObjectID + “</objectID>” +
    • @”</RunJob></soap:Body></soap:Envelope>”;
    • WebRequest req = WebRequest.Create(”http://localhost:7047/DynamicsNAV/WS/” + CompanyName + “/Codeunit/RunJob”);
    • req.Headers.Add(”SOAPAction”, “\”urn:microsoft-dynamics-schemas/codeunit/RunJob:RunJob\”");
    • req.ContentType = “text/xml;charset=\”utf-8\”";
    • req.Method = “POST”;
    • req.UseDefaultCredentials = true;
    • Stream strWrite = req.GetRequestStream();
    • StreamWriter sw = new
      StreamWriter(strWrite);
    • sw.Write(Body.ToString());
    • sw.Close();
    • WebResponse wr = req.GetResponse();
    • HttpWebResponse httpRes = (HttpWebResponse)wr;
    • Stream s = httpRes.GetResponseStream();
    • StreamReader sr = new
      StreamReader(s, Encoding.ASCII);
    • XmlDocument xmlDoc = new
      XmlDocument();
    • xmlDoc.Load(sr);

 

  • sr.Close();
  • return xmlDoc.InnerXml;
  • }
  • }
  • Next create a new class NavWindowService that inherits from ServiceBase class. Add the necessary code and define the Main method. Overwrite the Onstop and OnStart methods. And remove the default class that Visual Studio added.
    • public
      partial
      class
      NAVWindowsService : ServiceBase
    • {
    • public
      ServiceHost serviceHost = null;
    • public NAVWindowsService()
    • {
    • // Name the Windows Service
    • ServiceName = “NAVWindowsService”;
    • }
    • public
      static
      void Main()
    • {
    • ServiceBase.Run(new NVWindowsService());
    • }
    • protected
      override
      void OnStart(string[] args)
    • {
    • if (serviceHost != null)
    • {
      • serviceHost.Close();
    • }
    • serviceHost = new
      ServiceHost(typeof(NAVService));
    • serviceHost.Open();
    • }
    • protected
      override
      void OnStop()
    • {
    • if (serviceHost != null)
    • {
      • serviceHost.Close();
      • serviceHost = null;
    • }
    • }
    • }
  • The last class we need to add is ProjectInstaller class. This will allow our program to be installed as windows service using Installutil.exe tool.

[RunInstaller(true)]

public
class
ProjectInstaller : Installer

{

private
ServiceProcessInstaller process;

private
ServiceInstaller service;

 

public ProjectInstaller()

{

process = new
ServiceProcessInstaller();

process.Account = ServiceAccount.LocalSystem;

service = new
ServiceInstaller();

service.ServiceName = “NAVWindowsService”;

Installers.Add(process);

Installers.Add(service);

}

}

  • The Last Step in the process is to add an application configuration file to the project. This xml file will contact web service settings.


Replace the content of the config file with the following. Change the property of xml file “Copy to Output Directory” to “Copy if newer”. Something very important thing to about config file is that binding is set to basicHttpBinding. This allows non nonwindows programs or Silverlight apps to connect to the web service.

<?xml
version=”1.0″
encoding=”utf-8″ ?>

<configuration>

    <system.serviceModel>

        <services>

            <service
name=”NavWinProxy.NAVService”

                     behaviorConfiguration=”NAVServiceBehavior”>

                <host>

                    <baseAddresses>

                        <add
baseAddress=”http://localhost:8000/service”/>

                    </baseAddresses>

                </host>

                <!– this endpoint is http://localhost:8000/service –>

                <endpoint
address=”"
binding=”basicHttpBinding”
contract=”NavWinProxy.INavWinProxy” />

            </service>

        </services>

        <behaviors>

            <serviceBehaviors>

                <behavior
name=”NAVServiceBehavior”>

                    <serviceMetadata
httpGetEnabled=”true”/>

                    <serviceDebug
includeExceptionDetailInFaults=”true”/>

                </behavior>

            </serviceBehaviors>

        </behaviors>

    </system.serviceModel>

</configuration>

 

Now that we are done, we can build the solution. Build->Build NavWinProxy.

To install it as windows server I’m going to use Visual Studio command prompt.

You can also use regular command prompt. It just you have to navigate to wherever Installutil.exe is and then run the following command

Installutil.exe “C:\Documents and Settings\YOURUSERNAME\My Documents\Visual Studio 2008\projects\NavWinProxy\NavWinProxy\bin\Debug\NavWinProxy.exe”

You’ll need to change the directory ofcourse to the location where your visual studio project resides. Once you run the command above, you will get a message that the transacted install completed. In windows services you should see NAVWindowsService. To uninstall the service simply add –u parameter. installutil.exe –u “Location of the exe”

 

You’ll need to click on properties of this service and change it to login as specific windows user and change it to start automatic if you like. You’ll need to check evenlog to see that it started correctly

If you get any error related to

Service cannot be started. System.InvalidOperationException: Service ‘NavWinProxy.NAVService’ has zero application (non-infrastructure) endpoints. This might be because no configuration file was found for your application, or because no service element matching the service name could be found in the configuration file, or because no endpoints were defined in the service element.

 

It means you have a typo in your config file. Make sure the following elements are spelled correctly and in correct CASE.

<service
name=”NavWinProxy.NAVService”
behaviorConfiguration=”NAVServiceBehavior”>

<endpoint
address=”"
binding=”basicHttpBinding”
contract=”NavWinProxy.INavWinService” />

 

Next I will start SOAPUI and test the web service. Below is a screenshot of SOAPUI

 

 

Attached is the VS Project. NavProxy

Posted in Dynamics NAV, webservice | 3 Comments »