Tag: Job Queue

Posted on

Multiple NAS Services on a single license

Most Dynamics NAV databases have more than one companies.  When setting up Dynamics NAV Application Server as a service, you have to install a service for each company in your database.  Your license might not have enough user licenses to be able to run all services at once.  My solution is the following.

I install a NAS service for each company.  One of them set to automatic startup type, all others are set to manual. I then schedule a task every 30 minutes and execute a script that I save as NAS-Rotate.vbs.
[code lang=”vb”]strComputer = "."
Set objWMIService = GetObject("winmgmts:" _
& "{impersonationLevel=impersonate}!\\" & strComputer & _
"\root\cimv2")

CurrentServiceName = ""
RunNextService "NAS-1","NAS-2"
RunNextService "NAS-2","NAS-3"
RunNextService "NAS-3","NAS-4"
RunNextService "NAS-4","NAS-5"
RunNextService "NAS-5","NAS-6"
RunNextService "NAS-6","NAS-1"
If CurrentServiceName = "" Then
StartService "NAS-1"
End If

Sub RunNextService(ServiceName,NextServiceName)
Set colServices = objWMIService.ExecQuery _
("SELECT * FROM Win32_Service WHERE Name = ‘" & ServiceName & "’")

‘* List all the services on the machine
For each objService in colServices
If objService.State = "Running" Then
‘* WScript.Echo "Stopping: " & objService.DisplayName & "," & _
‘* objService.StartName & "," & objService.State
objService.StopService
StartService(NextServiceName)
CurrentServiceName = NextServiceName
‘* WScript.Quit
End if
Next
End Sub

Sub StartService(ServiceName)
Set colServices = objWMIService.ExecQuery _
("SELECT * FROM Win32_Service WHERE Name = ‘" & ServiceName & "’")

‘* List all the services on the machine
For each objService in colServices
If objService.State = "Stopped" Then
‘* WScript.Echo "Starting: " & objService.DisplayName & "," & _
‘*objService.StartName & "," & objService.State
objService.StartService
End if
Next
End Sub[/code]
In this excample I have installed six services.

Posted on

SQL Maintenance from Dynamics NAV

When running Dynamics NAV on a MS-SQL database you need to execute some maintenance tasks.  They include defrag and rebuild for indexes and statistics update.  By using SQL Native Client, the Table Information from NAV and Job Queue you can automate this maintenance with Dynamics NAV Application Server.

First you must make sure that automatic update statistics and auto shrink are set to false.

Then you create codeunit for each task.  Here is an example.

Variables
[code]TableInformation@1100409000 : Record 2000000028;
ADOConnection@1100409004 :
Automation "’Microsoft ActiveX Data Objects 2.8 Library’.Connection";
SQLSetup@1100409006 : Record 10017176;
Window@1100409003 : Dialog;
WindowLastUpdated@1100409002 : DateTime;
Counter@1100409001 : Integer;
Total@1100409000 : Integer;
Text001@1100409005 :
TextConst ‘ENU=Microsoft ADO not found.;ISL=Microsoft ADO finnst ekki.’;[/code]
Code for Rebuild Indexes using “DBCC DBREINDEX”
[code]SQLSetup.GET;
SQLSetup.TESTFIELD("SQL Server Name");
SQLSetup.TESTFIELD("SQL Database name");
IF GUIALLOWED THEN
Window.OPEN(‘#1####################################\\’ +
‘@2@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@’);
WindowLastUpdated := CURRENTDATETIME;

IF ISCLEAR(ADOConnection) THEN
IF NOT CREATE(ADOConnection) THEN
ERROR(Text001);

IF ADOConnection.State = 1 THEN
ADOConnection.Close;

ADOConnection.Open(
STRSUBSTNO(
‘Driver={SQL Native Client};Server=%1;’ +
‘Database=%2;Trusted_Connection=yes;’,
SQLSetup."SQL Server Name",
SQLSetup."SQL Database name"));
ADOConnection.CommandTimeout(0);

WITH TableInformation DO BEGIN
SETFILTER("No. of Records",’>0′);
SETFILTER("Table No.",'<>150014&<2000000002′);
Total := COUNTAPPROX;
Counter := 0;
IF FINDSET THEN REPEAT
Counter := Counter + 1;
IF GUIALLOWED THEN
IF (CURRENTDATETIME – WindowLastUpdated) > 1000 THEN BEGIN
Window.UPDATE(1,"Table Name");
Window.UPDATE(2,ROUND(Counter / Total * 10000,1));
WindowLastUpdated := CURRENTDATETIME;
END;

IF "Company Name" <> ” THEN
ADOConnection.Execute(
STRSUBSTNO(
‘DBCC DBREINDEX ([%1$%2],””,90);’,
CONVERTSTR("Company Name",’."\/”’,’_____’),
CONVERTSTR("Table Name",’."\/”’,’_____’)))
ELSE
ADOConnection.Execute(
STRSUBSTNO(
‘DBCC DBREINDEX ([%1],””,90);’,
CONVERTSTR("Table Name",’."\/”’,’_____’)))

UNTIL NEXT = 0;
END;

ADOConnection.Close;[/code]
This example is using trusted connection to the SQL Server, that is a domain user account.  Grant that user the processadmin server role.

To defragment indexes, use “DBCC INDEXDEFRAG”.

Skýrr has an addon solution that includes all these functions and more with granule 10,017,160

Posted on

Added to Job Queue E-Mail

Since the report that I posted earlier simply sends an PDF copy of the Invoice and Credit Memo to the recipient via SMTP mail you do not have a copy of that E-Mail in your inbox or sent items.

I added a single line of code to send a carbon copy of the E-Mail to the sender.

[code]

IF CompanyInformation."Ship-to Contact" <> ” THEN BEGIN
SMTPMail.AppendBody(CRLF);
SMTPMail.AppendBody(Text004);
SMTPMail.AppendBody(CRLF);
SMTPMail.AppendBody(CompanyInformation."Ship-to Contact");
SMTPMail.AppendBody(CRLF);
END;

SMTPMail.AddCC(CompanyInformation."E-Mail"); // Add this line
SMTPMail.AddAttachment(Directory + FileName);
SMTPMail.Send;
[/code]

Posted on

E-Mailing Invoices and Credit Memos with Job Queue

This solution uses PDFCreator to automatically email all invoices and credit memos to customers that have an email address entered.

The first step is to download PDFCreator and install on the machine running NAV Application Server.  Then you start PDFCreator – PDF Print Monitor.

Go to Printer and Options (Ctrl+O).  Under General Settings 2 you change the Check Update interval to Never.

This will make sure that an update question will not interrupt the Job Queue execution.  You need to add an Integer field “No. E-Mailed” to tables 112 Sales Invoice Header and 114 Sales Cr. Memo Header.  In here this field is number 10017243 as this field is a part of a solution sold as granule no. 10,017,060 by Advania.  You should be able to create this field in another number and change the following solution accordingly.

This solution uses the Name, E-Mail address, Ship-to Contact in Company Information and sends the email via SMTP.  You will need to name the correct email server in table 409 SMTP Mail Setup.

Here is a ZIP file with the NAV Object.

The last step is to create a Job Queue Entry for a recurring job that executes report 50001 Job Queue E-Mailer.  I choose to execute the task every 240 minutes from eight in the morning to eight in the evening.

Posted on

Job Queue Execution

The Job Queue granule (3,810) in version 5.0 and later can be used to automate tasks by starting Nav Application Server with the parameter JOBQUEUE.  Everyting that Job Queue starts is started with a record in the table Job Queue Entry.  However, there are built in batches, like report 795, Adjust Cost – Item Entries, that Job Queue can start with some help.  Here is a Report that I use to start built in batches from Job Queue.

Here is a zip file with the code needed.