Thursday, March 24, 2011

Basics - How to confirm a Biztalk update

Here are the version numbers of current Biztalk 2006 R2 as seen at Microsoft.

BizTalk Server 2006 R23.6.1404.0
BizTalk Server 2006 R2 SP13.6.2149.10January 27th, 2010
Cumulative Update 1 (CU1)3.6.2210.12April 12th, 2010
Cumulative Update 2 (CU2)3.6.2217.12June 24th, 2010
Cumulative Update 3 (CU3)3.6.2224.12August 30th, 2010

For other Biztalk versions you can explore another Microsoft Link.

We made an update on Biztalk to CU3 and the version seen at About was still 3.6.2149.10, referring to the SP1.

To know the specific add-ons like CU's, and hotfixes, the only place to go is Add and Remove Programs. Just remember to mark the Show Updates. checkbox.

Thursday, March 17, 2011

Using SAP Dates in Biztalk

Half the maps I've been doing have SAP in one side or the other. One of the biggest problems are in map transformation.

Making all those substring concatenations is ugly. Creating a functoid is the best thing to do, but in case it is needed just in a map or two, how can it be made?

Convert .NET Date to SAP using C#

dateVariable.ToString("yyyyMMdd", System.Globalization.CultureInfo.InvariantCulture);

Convert SAP Date to .NET using C#

DateTime.ParseExact(val1, "yyyyMMdd", System.Globalization.CultureInfo.InvariantCulture);

(the CultureInfo attribute is optional but recommended in both methods)

This problem appeared when I had to calculate a difference between two SAP dates. It end up by being just this:

public int DaysToPay(string val1, string val2) {
DateTime startTime = DateTime.ParseExact(val1,"yyyyMMdd", System.Globalization.CultureInfo.InvariantCulture);
DateTime endTime = DateTime.ParseExact(val2,"yyyyMMdd", System.Globalization.CultureInfo.InvariantCulture);

TimeSpan span = endTime.Subtract ( startTime );

return span.Days-1;

Sunday, March 13, 2011

Count entries in a SQL table by minute

Imagine that you need to count entries on a table by date. That query is rather obvious and probably you already made it sometimes. And what if you need to count entries on a table by minute? For instance, to determine the period when more orders are received.

select count(*), datepart(HH, lastchange), datepart(MI,lastchange)
group by datepart(HH, lastchange), datepart(MI,lastchange)
order by datepart(HH, lastchange), datepart(MI,lastchange)

Running that query two things are immediately wrong. The hours separated from minutes, the minutes with one digit, and some periods have no info and therefore no entry saying 0.

The first step is to fill the blanks with 0. For that, we'll generate a temporary table with all the hours and minutes from 0:00 until 23:59. We'll be using Common Table Expressions and a temporary table for that.
Just pick a day, and use DateAdd to increase it minute by minute. With Recursion it will fill the entire table based on that rule.

WITH mycte AS(
SELECT CAST('2011-01-01 0:00' AS DATETIME) DateValue
FROM mycte
WHERE DateValue < '2011-01-02'

SELECT DATEPART(HH,DateValue) hours,DATEPART(MI,DateValue) minutes
INTO #temptable

Finally, we'll left join the complete table and our results, and write the time in a proper format.

':'+REPLICATE('0',2-LEN(minutes))+CAST(minutes AS VARCHAR),COUNT(lastchange)
FROM #temptable
LEFT JOIN ORDERSTABLE ON hours = DATEPART(HH, lastchange) AND minutes=DATEPART(MI,lastchange)
GROUP BY hours, minutes
ORDER BY hours, minutes

Don't forget to drop the temporary table.
DROP TABLE #temptable

And that's it.