Monitoring Biztalk

We do not have any fancy system monitoring tool, we still need to track if something is going wrong in BizTalk, to my experience it is most likely either suspended messages or a receive port that shuts down

The below script will check for that and send a mail alerting me about the status, not very fancy but it does the trick

-- Query if any messages are suspended

declare @Status int

declare @messages varchar(256)
declare @msgbody varchar(max)

select @messages = COUNT(*) from [dbo].[InstancesSuspended]

select @msgbody = 'There are ' + @messages + ' suspended messages' + CHAR(13)

if @messages != 0

begin

      exec sp_send_dbmail @profile_name ='ProfileName'

      ,@recipients = 'name@company.com'

      ,@subject ='Error in BizTalk application, Suspended messages'

      ,@body = @msgbody

end

-- Query for any receive ports that are disabled

declare @Status2 int

declare @ports varchar(256)

declare @msgbody2 varchar(max)

select @msgbody2 = 'The following receive ports are disabled:' + CHAR(13)

select @Status2 = COUNT(*) from [dbo].[adm_ReceiveLocation] where [Disabled] != 0

 

if @Status2 != 0

begin

declare port_cursor cursor FOR

select name from [dbo].[adm_ReceiveLocation] where [Disabled] != 0;

open port_cursor

      fetch next from port_cursor into @ports

            while @@FETCH_STATUS = 0

            begin

                  select @msgbody2 = @msgbody2 + @ports + CHAR(13)

                  fetch next from port_cursor into @ports;

            end

            close port_cursor;

            deallocate port_cursor;   

begin

      exec sp_send_dbmail @profile_name ='ProfileName'

      ,@recipients = 'name@company.com'

      ,@subject ='Error in BizTalk application, receive port(s) are disabled'

      ,@body = @msgbody2

end

end

 

I have put this script in a SQL job that runs every hour, that should do it.

Deploying SSRS reports from AX 2012 R2

I have been tearing my hair out (I don’t ha have much) for the last couple of days trying to deploy reports to SSRS from the Dynamics AX 2012 R2 and having read all the tips on how to do that I finally managed to resolve the issue

The error messages are several, permission related but also more generic ones referring to the configuration of the SSRS.

There are many related threads on various forums covering several aspect of this but none that clearly states what finally resolved it for me.  The SSRS service account was setup as a BCproxy account, when running the Power Shell under the same account I was finally able to deploy my report. The command is:



publishAxreport -reportName salesInvocie –skipreportserveradmincheck

 
I still believe this should be possible to resolve without having to run the command under a different account. Probably somewhere on the SSRS this account will need additional permission, however I have tried giving my user account all possible permission without any luck... the search continue, at least this is an easy workaround.

EDIT: You have to run the power shell as admin for this to work.

 

 

SQL server connection error

Over the years I have had this (or similar) error several times, however I get so rarely I tend to forget how I fixed them. So even though a million other have already posted this I will also do it, as a personal reference.

The error is:

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 5)

The solution:
Enable Named pipes on the server
HowTo:
Open SQL server configuration manager go to client protocols and enable Named pipes.

Modifying Intercompany in Dynamics AX 4.0

We have in our solution added a few new fields on the sales and purchase order for transport management. Obviously we need to include these fields in the intercompany functionality. It can be somewhat a daunting task to find exactly where in the application you need to go to find the right methods for this.
 
Example:
You have a new field on the sales and purchase order called TransportCode, you need to include this field in the IC functionality, and here is how you do it.
First add a new “parm” method in the AXSalesTable class, something like this:
 
public str parm_TransportCode(str _transportCode = "")
{;
    _transportCode = Some code to find and set the value;
    return _transportCode;
}
 
Note! The parm methods must have a name that starts with “parm”

Second add the same “parm” method in the AXPurchTable class.
 
Third, update the InterCompanyMirror method on the SalesTableType class
Like so:
axPurchTable.parm_TransportCode(salesTable.TransportCode);
 
Do the same on the PurchTableType IntercomapnyMirror
This time adjust to the parm method you wrote on the AxSalesTable class.
 
Do make sure this also takes affect when users manually change the value on one or the other side of the IC relation you need also to incude the field in the InterCompanyUpdateNow methods found on SalesTable and PurchTable tables. Like so:
this.orig().TransportCode != this. TransportCode    
Under the if statement. This will act as a trigger when a user changes a value. 
 

Object	Method
AxSalesTable	New parm method
AxPurchTable	New parm method
SalesTableType	InterCompanyMirror
PurchTableType	InterCompanyMirror
PurchTable (table)	InterCompanyUpdateNow
SalesTable (table)	InterCompanyUpdateNow

If you only need to update to work in one direction you only need to make the changes on one side of the relation.
 
 

 

Suppressing nodes in BizTalk mapper part deux

I wrote some post back about how to suppress nodes in BizTalk, using the graphical interface. This solution was recently under review and we had to implement some further changes to it. First I thought this could be solved in the GUI, having spent some time in there trying to fix it, I had to give up, this could not be solved using functiods or the likes. That is when I (for the second time) discovered the power of XSLT. XSLT or Extensible Stylesheet Language Transformations, is the way to go, very few limitations exist and at the end of the day, you end up with a mapping which is easier to understand and gives you a good overview, because, let’s face it, the graphical mapping in BT is anything but easy overview.

Calendar table in SQL

Found myself needing a calendar table to join my data to, in order to ensure that each day of the week and each day of the month had one Point in the graph, regardless of any sales was made that day. This below code does the trick and is quite straightforward. Note this is with the European week numbering, where the first day in the week is Monday.

Create the table
CREATE TABLE [dbo].[MyCalendar](

[DayNumber] [int] NULL,
[CalendarYear] [int] NULL,
[CalendarMonth] [int] NULL,
[DayInMonth] [int] NULL,
[WeekDay] [varchar](10) NULL,
[IsWeekEnd] [smallint] NULL,
[Period] [varchar](7) NULL,
[CalendarDate] [datetime] NULL,
[DateString] [varchar](10) NULL

) ON [PRIMARY]

This scrip will populate the tabel with all dates from @FromDate until @ToDate


declare @StartDate datetime
declare @EndDate datetime
declare @_date datetime
declare @counter int

set @StartDate = '2010-01-01'
set @EndDate = '2020-12-31'
set @counter = 0
set @_date = DateAdd(dd, @counter, @StartDate)

while @_date < @EndDate

begin

insert into GAB_Calendar values(
@counter, YEAR(@_date), MONTH(@_date), DAY(@_date)
,case when DATEPART(DW, @_date) = 1 then 7
when DATEPART(DW, @_date) = 2 then 1
when DATEPART(DW, @_date) = 3 then 2
when DATEPART(DW, @_date) = 4 then 3
when DATEPART(DW, @_date) = 5 then 4
when DATEPART(DW, @_date) = 6 then 5
when DATEPART(DW, @_date) = 7 then 6 end
,case when DATEPART(DW,@_date) in(1,7) then 1 else 0 end
,CONVERT(varchar, YEAR(@_date)) + '-' + CONVERT(varchar, MONTH(@_date))
,@_date
,CONVERT(varchar(10), @_date, 120)
)
set @counter = @counter + 1
set @_date = DateAdd(dd, @counter, @StartDate)
 end

Translating enumerations to T-SQL strings

If you are anything like me, you are spending a lot of time designing querys to transfer data from your ERP system to a data warehouse, in Dynamics AX a lot of the data is represented by enumerations, now that kind of data does not look very nice and user friendly in a data warehouse, to translate them in your T-SQL query I use this simple script to get the labels for each enumeration index. Simply replace the “PriceType” string with any enum in the system.

static void GAB_PrintEnumValues(Args _args)
{
    DictEnum DEnum;
    int      i;
    ;

    DEnum = new DictEnum(enumName2Id("PriceType"));
    for (i=0; i < DEnum.values(); i++)
    {
        info("when PostingType  = " + int2str(DEnum.index2Value  (i)) + "
        then  \'" + DEnum.index2Label(i) + "\'");
    }
}

The outcome should look like this, simply right click and paste this into your query window.

Parsing XML files to Excel or webpage

I found myself needed to go over a large number of XML files, to check the content. I love XML, it's simple structure and flexibility but you do not want to use XML to browse through thousands of rows of data, the structure simple does not allow that. So what do you do? I wrote a very simple program that loops over all files in a directory, opens the files and outputs the key data in a table format, either to Excel or to a web page. The result is easy to read, easy to summarize and analyze.

Write this in a windows form, web form or even an CMD application.

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Xml;

using System.IO;

using System.Drawing;

using Microsoft.Office.Interop.Excel;

namespace ParseXMLFiles

{

    public partial class WebForm1 : System.Web.UI.Page

    {

        protected void Page_Load(object sender, EventArgs e)

        {

            ParseXML();

            ParseXMLToExcel();

        }

        public void ParseXMLToExcel()

        {  

            int NoOfFiles = 1;

            int NoOfNodes = 1;

            string folder = "\\\\euex1app11\\data\\BaseLogistics\\SOPackingSlip\\Original\\";

            string[] files = Directory.GetFiles(folder, "*.xml");

            Microsoft.Office.Interop.Excel.Application app = new Application();        

Microsoft.Office.Interop.Excel.Workbook workbook = app.Workbooks.Add(System.Reflection.Missing.Value);

Microsoft.Office.Interop.Excel.Worksheet sheet = workbook.Sheets.Add(System.Reflection.Missing.Value);

            sheet.Cells[1, 1] = "Rec ID";

            sheet.Cells[1, 2] = "File";

            sheet.Cells[1, 3] = "Item ID";

            sheet.Cells[1, 4] = "Sales ID";

            sheet.Cells[1, 5] = "Quantity";

            NoOfNodes++;

            foreach (string file in files)

            {

                XmlDocument doc = new XmlDocument();

                doc.Load(file);

                XmlNode root = doc.DocumentElement;

                XmlNodeList node = root.SelectNodes("SalesTable/SalesLine/Fields");

                foreach (XmlNode nodes in node)

                {                

                    sheet.Cells[NoOfNodes, 1] = nodes["RecId"].InnerText;                

                    sheet.Cells[NoOfNodes, 2] = file;                

                    sheet.Cells[NoOfNodes, 3] = nodes["ItemId"].InnerText;                

                    sheet.Cells[NoOfNodes, 4] = nodes["SalesId"].InnerText;                

                    sheet.Cells[NoOfNodes, 5] = nodes["InventDeliverNow"].InnerText;

                    NoOfNodes++;

                }

                NoOfFiles++;

            }

            app.Visible = true;

        }    

               

        public void ParseXML()

        {

            TableRow row;

            TableCell cell;

            TableCell cell1;

            TableCell cell2;

            TableCell cell3;

            TableCell cell4;

            int NoOfFiles = 1;

            int NoOfNodes = 1;

            string folder = "\\\\euex1app11\\data\\BaseLogistics\\SOPackingSlip\\Original\\";

            string[] files = Directory.GetFiles(folder, "*.xml");

                       

            NoOfNodes++;

            foreach (string file in files)

            {

                XmlDocument doc = new XmlDocument();

                doc.Load(file);

                XmlNode root = doc.DocumentElement;

                XmlNodeList node = root.SelectNodes("SalesTable/SalesLine/Fields");

               

                foreach (XmlNode nodes in node)

                {

                    row = new TableRow();

                    cell = new TableCell();

                    cell1 = new TableCell();

                    cell2 = new TableCell();

                    cell3 = new TableCell();

                    cell4 = new TableCell();

                    cell.Text = nodes["RecId"].InnerText;                   

                    cell1.Text = file;                   

                    cell2.Text = nodes["ItemId"].InnerText;                   

                    cell3.Text = nodes["SalesId"].InnerText;                   

                    cell4.Text = nodes["InventDeliverNow"].InnerText;                   

                    row.Cells.Add(cell);

                    row.Cells.Add(cell1);

                    row.Cells.Add(cell2);

                    row.Cells.Add(cell3);

                    row.Cells.Add(cell4);

                    Table1.Rows.Add(row);

                    NoOfNodes++;

                }

                NoOfFiles++;

            }

           

        }      

    }

}