Basics - How to confirm a Biztalk update

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

BizTalk Server 2006 R2	3.6.1404.0
BizTalk Server 2006 R2 SP1	3.6.2149.10	January 27th, 2010
Cumulative Update 1 (CU1)	3.6.2210.12	April 12th, 2010
Cumulative Update 2 (CU2)	3.6.2217.12	June 24th, 2010
Cumulative Update 3 (CU3)	3.6.2224.12	August 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.

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;
}

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)
from ORDERSTABLE
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
UNION ALL
SELECT DATEADD(MINUTE, 1, DateValue)
FROM mycte
WHERE DateValue < '2011-01-02'
)

SELECT DATEPART(HH,DateValue) hours,DATEPART(MI,DateValue) minutes
INTO #temptable
FROM mycte OPTION (MAXRECURSION 0)

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

SELECT REPLICATE('0',2-LEN(hours))+CAST(hours AS VARCHAR)+
':'+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.