Looking back, Looking ahead, and Moving on

Whoah, it's been almost 2 months since my last posts.

Reason
I have joined a new company last month, so it's been quite busy with the new work items, however unfortunately, haven't really played much with programming lately, I was assigned to do some migrations, so more manual works have to be done on that ;)

Looking Back

Well, I would say that I started blogging more frequently since a year ago, and most of them were related to BizTalk. It's been fun indeed, thinking the cases / scenarios where we want them to be implemented using Biztalk. And the number of times I have to explain : "NO, it doesn't work that way, the receive location and the send port are not directly linked to each other, there is a message box in the middle of those two and bla bla bla" I'm gonna miss that though haha :P

Looking Ahead

The role that I have in the new company is still my favorite one : DEVELOPER, can't get enough of it really haha, glad that I'm going to learn new stuffs and understand the business side as well, i guess that's the upside of moving from one company to another, you'll face similar but different problems ;) however I don't think I will be blogging about BizTalk anymore for the time being, because the work will be more on .Net and SQL :)

Moving On

Just when blogger actually added inline comments as a feature, for a particular reason I need to move my blog activities to other site :| just to let you know that I will be blogging at http://bembengarifin.wordpress.com/,

The purpose stays the same, to post all the gotchas that i found in software development, so you don't spend hours/days like I did to solve the issue and can have more times to spend with your family and friends :)

I would like to thank you for visiting this blog and I'll see you on the other site ;)

Generate Script & Export Data From SQL CE (Compact Edition) 3.5

Several months ago I migrated my family financial and expense information from excel file to SQL CE 3.5.
SQL CE is indeed good enough for data entry purpose, but after a while, I felt that it is not good enough for reporting purpose.
So I decided that it's better to use the SQL Server Express instead.

Now the first problem that I had : How to migrate the tables along with the data from SQL CE 3.5 to SQL Server Express?

I have been googling around for a while, but haven't really found a good one for this, which make me realized that there are more ways to get the data from SQL Server to SQL CE but not the other way around duhh :|

So later, I decided to code it myself ;)

When browsing around the net, I found about Information Schema View that is available in SQL Server, and fortunately it is also available in the SQL CE 3.5 ;) (before I found out about the view, I was using Linq Attributes to generate the table creation ouccch)
So now by using the view, I generate the script for the SQL CE 3.5 database file (.sdf).

In summary, the code will :
1. Generate Table Creation Script
2. Generate Insert Statement for the data in all of the tables
3. Generate script to assign the Primary Keys for the tables
4. Generate script to assign the foreign keys / references for the tables

** Update 5. Generate script to assign Indexes for the tables

6. Save the script into the output file

So here's a little peek of how to use the tool, I'm using Northwind.sdf file in the example below :
There are 2 parameters to pass in ;
1. Connection String to the sdf file
2. Output File Name for the script

Note : If you get any access denied when trying to open the Northwind.sdf file, here is why and the solution.

After the Northwind.sql file has been generated :
1. Create the SQL Server database file (.mdf)
2. Open the Northwind.sql script into your SQL Server Management Studio or Visual Studio (Since I'm currently using SQL Epxress 2008 CTP, there is no management studio yet for now) and just execute them, if everything's ok (finger crossed), you will see this kind of message below and you're done :)

Please note :
1. The tool will not generate the database for you, it will only generate the scripts to execute
2. Currently I purposely convert nchar and nvarchar data type to varchar in the table creation script. I don't see any purpose of using them unless if storing unicode characters in the table.
3. I haven't handled Image / BLOB data type for generating data (Insert Statement).

Ok now, stop talking and just give me the download link :P

Here is the executable file :
**Removed: Please get the files from CodePlex site instead.

And here is the code, if you want to see what's running inside :
**Removed: Please get the files from CodePlex site instead.

**Updates: Erik EJ has provided the newer version of this at CodePlex website, I really recommend that you get the files from there. Huge thanks to Erik to make this utility tool better and to make it possible for others to contribute

Hope it helps ;)
and do leave a comment if you have any feedback or find any bugs, Thanks ;)

Great series of Separation of Concern by Jimmy Bogard

I just finished reading Jimmy Bogard's 5 series of Separation of Concern by example and they are just too awesome to be missed out.

The series :
# Separation of Concerns - how not to do it
# Separation of Concerns by example: Part 1 - Refactoring away from static class
# Separation of Concerns by example: Part 2 - Specialized interface for Cache
# Separation of Concerns by example: Part 3 - Creating the repository
# Separation of Concerns by example: Part 4 - Fixing a bug with unit test
# Separation of Concerns by example: Part 5 - Dependency Injection with StructureMap

The best thing that I like about them is that they are based on a real world example :D
Enuf said, go and see for your self ;)

C# 2.0 Generic Collection Sort By Value

I’m working on a web application prototype and I’m using some dummy data in xml form because it is simple and I don’t want to care about the database for a prototype ;)

Let say this is my sample data :

<root>     <member id="1" name="Jeff" />     <member id="2" name="Fred" />     <member id="3" name="Greg" />     <member id="4" name="Andy" /> </root>

I have some drop down lists or combo boxes to fill in with this kind of data in the form, and I want them to be sorted based on the description (name) not based on the key (id).

Since the data come from xml file, it may not be sorted that way, so I need to find a way to sort the data first and then bind the values to the drop down lists.

Firstly I thought it will be very simple, only need to create a class which implements IComparer for this and use it in the available generic collection types. However, it only true at the new IComparer class, but not on the generic collection part, I found out that SortedDictionary<TKey, TValue> and SortedList<TKey, TValue> will use the key to sort, even though we can specify the IComparer in the constructor.

So based on the forum discussion, I found that we can use a generic List<T> to perform sorting and we can use KeyValuePair<TKey, TValue>as the type of T.

Here is the SortByValueComparer class which implements IComparer :

public class SortByValueComparer<TKey, TValue> : IComparer<KeyValuePair<TKey, TValue>> {     #region IComparer<KeyValuePair<TKey, TValue>> Members     public int Compare(KeyValuePair<TKey, TValue> x, KeyValuePair<TKey, TValue> y)     {         return Comparer<TValue>.Default.Compare(x.Value, y.Value);     }     #endregion }

Then we can use List<T> such as :

[Fact] public void Test_ListSort() {     List<KeyValuePair<string, string>> l = new List<KeyValuePair<string, string>>();     l.Add(new KeyValuePair<string, string>("1", "Jeff"));     l.Add(new KeyValuePair<string, string>("2", "Fred"));     l.Add(new KeyValuePair<string, string>("3", "Greg"));     l.Add(new KeyValuePair<string, string>("4", "Andy"));     Assert.Equal(l[0].Value, "Jeff");     Assert.Equal(l[1].Value, "Fred");     Assert.Equal(l[2].Value, "Greg");     Assert.Equal(l[3].Value, "Andy");     // Sort the collection based on the value     l.Sort(new SortByValueComparer<string, string>());     Assert.Equal(l[0].Value, "Andy");     Assert.Equal(l[1].Value, "Fred");     Assert.Equal(l[2].Value, "Greg");     Assert.Equal(l[3].Value, "Jeff"); }

However, I felt that the code is a bit cluttered, so I add a new Custom List class which implements List<T> :

public class CustomList<TKey, TValue> : List<KeyValuePair<TKey, TValue>> {     public CustomList() : base() { }     public CustomList(int capacity) : base(capacity) { }     public void AddEntry(TKey key, TValue value)     {         this.Add(new KeyValuePair<TKey, TValue>(key, value));     }     public void SortByValue()     {         this.Sort(new SortByValueComparer<TKey, TValue>());     } }

So I can use it like this :

[Fact] public void Test_CustomList() {     CustomList<string, string> l = new CustomList<string, string>();     l.AddEntry("1", "Jeff");     l.AddEntry("2", "Fred");     l.AddEntry("3", "Greg");     l.AddEntry("4", "Andy");     Assert.Equal(l[0].Value, "Jeff");     Assert.Equal(l[1].Value, "Fred");     Assert.Equal(l[2].Value, "Greg");     Assert.Equal(l[3].Value, "Andy");     // Sort the collection based on the value     l.SortByValue();     Assert.Equal(l[0].Value, "Andy");     Assert.Equal(l[1].Value, "Fred");     Assert.Equal(l[2].Value, "Greg");     Assert.Equal(l[3].Value, "Jeff"); }

Hope this helps ;)

Testing Concurrency using ThreadPool (Multithread)

There are times when we need to test our code in concurrent manner where there are some numbers of instances running simultaneously.

This is different with the invocation such as below code where the method will be executed in sequential manner, this kind of test may be useful to test how long it may take to execute the method.

for (int iTest = 0; iTest < 100; iTest++) {     TestingMethodA(); }

 

For this testing concurrency, I’m using Joseph Albahari’s Threading in C# articles in ThreadPooling section as references to the MultiThreadRunner class that I created.

Code below in plain English : Submit 5 invocations of TestingMethodA with 10 maximum concurrent threads.

MultiThreadRunner runner = new MultiThreadRunner();            runner.Run(new MultiThreadRunner.ParameterlessMethodDelegate(TestingMethodA), 5, 10);

 

The MultiThreadRunner class :

class MultiThreadRunner {     static object workerLocker = new object();     static int runningWorkers = 0;     /// <summary>     /// The delegate is to be used as a placeholder for a parameterless method to invoke     /// </summary>     public delegate void ParameterlessMethodDelegate();     /// <summary>     /// An overload to Run method, set the concurrentNoOfInstances to a default no     /// </summary>     /// <param name="myMethod">The method to be invoke, passed as a delegate</param>     /// <param name="noOfInvocations">Total no of invocations to execute</param>     public void Run(Delegate myMethod, int noOfInvocations)     {         this.Run(myMethod, noOfInvocations, 0);     }     /// <summary>     /// The method will submit the method into the ThreadPool     /// </summary>     /// <param name="myMethod">The method to be invoke, passed as a delegate</param>     /// <param name="noOfInvocations">Total no of invocations to execute</param>     /// <param name="concurrentNoOfInstances">Concurrent no of instances -> ThreadPool.MinThreads</param>     public void Run(Delegate myMethod, int noOfInvocations, int concurrentNoOfInstances)     {         int iRow = runningWorkers = noOfInvocations;         int iStartWorker, iEndWorker, iCompletion;         // Adjust the min threads         SetMinThreads(concurrentNoOfInstances);         ThreadPool.GetAvailableThreads(out iStartWorker, out iCompletion);         Debug.WriteLine(string.Format("Available Threads, Start : {0}", iStartWorker));         for (int i = 1; i <= iRow; i++)         {             ThreadPool.QueueUserWorkItem(new System.Threading.WaitCallback(InvokeMethod), myMethod);             Debug.WriteLine("Submitted a queue : " + i.ToString());         }         lock (workerLocker)         {             while (runningWorkers > 0) Monitor.Wait(workerLocker);         }         ThreadPool.GetAvailableThreads(out iEndWorker, out iCompletion);         Debug.WriteLine(string.Format("All threads have been completed, Start : {0} - End : {1} - Completion : {2}", iStartWorker, iEndWorker, iCompletion));     }     /// <summary>     /// Set the ThreadPool Mininum Threads, this is to avoid a delay bottleneck, please refer to http://msdn.microsoft.com/en-us/library/system.threading.threadpool.aspx     /// </summary>     /// <param name="concurrentNoOfInstances"></param>     private void SetMinThreads(int concurrentNoOfInstances)     {         int iMaxWorker, iMinWorker, iCompletion;         ThreadPool.GetMaxThreads(out iMaxWorker, out iCompletion);         ThreadPool.GetMinThreads(out iMinWorker, out iCompletion);         if (concurrentNoOfInstances != 0)         {             if (concurrentNoOfInstances > iMaxWorker)             {                 ThreadPool.SetMinThreads(iMaxWorker, iCompletion);                 Debug.WriteLine("The requested concurrent no of instances is higher than max threads, set the min threads as max threads : " + iMaxWorker);             }             else             {                 ThreadPool.SetMinThreads(concurrentNoOfInstances, iCompletion);                 Debug.WriteLine("Set the min threads to " + concurrentNoOfInstances);             }         }         else         {             Debug.WriteLine("Using the default min threads : " + iMinWorker);         }     }     /// <summary>     /// To invoke the method     /// </summary>     /// <param name="myMethod"></param>     private void InvokeMethod(object myMethod)     {         Debug.WriteLine("Invoke the method in thread : " + Thread.CurrentThread.ManagedThreadId);         ((Delegate)myMethod).DynamicInvoke(null);         lock (workerLocker)         {             runningWorkers--; Monitor.Pulse(workerLocker);         }     } }

or get the complete project from here :

 

Hope this helps with your test as well :D

Passing method using delegate

I was doing some code refactoring today and this is one of the things that fits nicely to remove those repetitive codes.

I have lines of code below as an example where I process some data based on certain key.

private void ProcessData() {     string theKey;     Key key = new Key("A", "B");                theKey = key.KeyA;     Console.WriteLine("Processing data with key : {0}", theKey);     theKey = key.KeyB;     Console.WriteLine("Processing data with key : {0}", theKey);     theKey = key.KeyA + key.KeyB;     Console.WriteLine("Processing data with key : {0}", theKey); }

This looks simple enough, but imagine what would happen if i replace “the processing data with key…” line with 20 real codes, they suddenly become 60 lines which are repetitive, doing the same thing using the key ;)

 

So this is what I came out with, create a delegate which receives Key object then create 3 new methods which have the same signature as the delegate and will have the logic to construct the key.

private delegate string AssignKeyDelegate(Key key); private string AssignKeyA(Key key) {     return key.KeyA; } private string AssignKeyB(Key key) {     return key.KeyB; } private string AssignKeyAB(Key key) {     return key.KeyA + key.KeyB; }

 

By using this, I can process the data and construct the key based on the appropriate method which is specified in the parameter using the delegate.

private void Run() {     ProcessData(new AssignKeyDelegate(AssignKeyA));     ProcessData(new AssignKeyDelegate(AssignKeyB));     ProcessData(new AssignKeyDelegate(AssignKeyAB)); } private void ProcessData(AssignKeyDelegate del) {     Key key = new Key("A", "B");                string theKey = del(key);     Console.WriteLine("Processing data with key : {0}", theKey); }

 

I provided the sample code above as a basic example only, there are many solutions that may come out with this kind of code :) The purpose is only to describe another feature that we can do with delegate.

Hope this helps :)

Asynchronous Delegate in Serviced Component COM+

I’m currently assigned to optimize a COM+ performance which is storing some data in the cache. The loading time will take around 2-3 minutes.

Since it is loading data from different tables and they are not related one with another, I’m thinking of using Asynchronous Delegate for the loading process so they can be loaded in separate threads.

This is what we should get if we use delegates

Load Data A, Load Data B, and Load Data C are executed using delegates :

LoadDataDelegate loadDataA = new LoadDataDelegate(LoadDataA); IAsyncResult result1 = loadDataA.BeginInvoke(null, null); LoadDataDelegate loadDataB = new LoadDataDelegate(LoadDataB); IAsyncResult result2 = loadDataB.BeginInvoke(null, null); LoadDataDelegate loadDataC = new LoadDataDelegate(LoadDataC); IAsyncResult result3 = loadDataC.BeginInvoke(null, null); // Wait the results to complete loadDataA.EndInvoke(result1); loadDataB.EndInvoke(result2); loadDataC.EndInvoke(result3);

 

Turned out after I deployed the component to COM+, all the delegates ran in only single thread as sequentially / synchronously.

Only if I create a new thread explicitly, it will create a new thread.

 

Load Data A is being executed in a new thread, Load Data B, and Load Data C are executed using delegates but they were executed in the same thread synchronously.

Thread loadDataA = new Thread(new ThreadStart(LoadDataA)); loadDataA.Start(); LoadDataDelegate loadDataB = new LoadDataDelegate(LoadDataB); IAsyncResult result2 = loadDataB.BeginInvoke(null, null); LoadDataDelegate loadDataC = new LoadDataDelegate(LoadDataC); IAsyncResult result3 = loadDataC.BeginInvoke(null, null); // Wait the results to complete loadDataA.Join(); loadDataB.EndInvoke(result2); loadDataC.EndInvoke(result3);

 

I haven’t really found the reason why for COM+ deployment will have this behavior for delegate asynchronous invocation :(

Note: I’m currently not using this asynchronous operation for shared resources reason which requires major changes.

So my dearest readers, please show me the light if you know the reason, thanks ;)

  

COM+ Catalog Error : Error code 800401E4 - Invalid syntax

Got this weird error yesterday when trying to start up some COM+ applications in my PC where previously they were working just fine.

Got the solution from here.

Watch out for that blank space

We have created a receive location with send port group as the subscriber and will send the message output using FILE adapter and FTP adapter.

So yesterday, I got an email from the engineering site, they said that they were not able to process the file from the FTP drop. 

Then they sent this output to me :

$ cat FILEA.txt cat: FILEA.txt: No such file or directory

 

But if they used this, the file is actually there :

$ more * :::::::::::::: FILEA.txt :::::::::::::: CONTENT FILE A

 

Another test :

$ more FILEA.txt* CONTENT FILE A

 

So from the test results above, you probably know what’s the cause right? :P

See that in the target file name properties, there is a blank space behind the “txt”.

After removed the blank space, life is good again :D

BizTalk MsgBoxViewer

While reading a post from One Blog for BizTalk Engineers, I found an interesting tool, BizTalk MsgBoxViewer, built by Jean-Pierre Auconie, which can be used to monitor and generate reports for BizTalk. You can see a little bit of background of the tool at here.

There are 2 user interfaces to use the tool, through GUI or console.

This is a screen capture for the GUI

This is the screen capture for the console

And here’s the generated report, I would say that I will need some time to chew the whole report out ;p

Go and check it out for your self, it’s definitely worth to try, especially with the “limited” information that we can get by using the BizTalk Administration Console or Health and Activity Tracking :P

Microsoft BizTalk Server Performance Optimization Guide

For Biztalkers, you may want to spend a while to read the new Microsoft BizTalk Server Performance Optimization Guide white paper.

I found this while reading a post from Richard Seroter’s blog from my google reader feeds.

BizTalk Wrong Message Type / Schema Routing Problem?

This morning, we saw that numbers of orchestrations were suspended.
After some investigations, we were quite surprised that the messages were sent to wrong orchestrations after being polled by the receive locations.

Below is how the messages should be routed based on the message type / schema subscription.


But what just happened to us was :


The data was polled correctly with the Database Alpha but later the message was constructed with the wrong message type / schema, which caused it to be delivered to the wrong orchestration :(


I have experienced this in the past with my testing box, however it only happened 1 time only and I can't think of at least a way to reproduce the problem.

And BTW, the "Restart the host instance" magic solution saved the day as usual :P

Tell me my dear readers, what i should say to my boss if he asks me about this? ;)

BizTalk SOAP Adapter - Consuming Remote Web Services Bottleneck

This morning I was told by my colleague that he found hundreds of instances running in the BizTalk Server and from the look of it, most of them are the send port SOAP adapter instances.

We have an orchestration which consumes a web services, so the first thing that came to my mind is that it's gotta be the web services which can not handle the load.

I was thinking that there's gotta be hundreds of concurrent calls to the web services, then i tried to open up the Performance Counter -> ASP.NET Apps v1.1.4322 -> Requests Executing for the particular web services, i was surprised to see that there are only 2 requests executing concurrently :|

Then my other colleage told me that there's a settings in the machine.config file for this, to limit the numbers of call to particular connection.

<system.net>
<connectionManagement>
<add address="*" maxconnection="2">
<add address = "http://www.contoso.com" maxconnection = "5" />
<add address = "http://www.northwind.com" maxconnection = "2" />
<add address = "200.200.200.1" maxconnection="12">
</connectionManagement>
</system.net>

After changing this settings to a higher value, the instances were getting completed gradually and not building up anymore :)

You can find more about this at :
http://channel9.msdn.com/wiki/default.aspx/PerformanceWiki.HowToTuneASPNET
http://support.microsoft.com/kb/821268
http://msdn.microsoft.com/en-us/library/aa560822.aspx / BizTalk 2006 Server Documentation -> SOAP Adapter Configuration and Tuning Parameters
http://msdn.microsoft.com/en-us/library/1tkaca2y.aspx

However, in the BizTalk 2006 Server Documentation, it states that the default is 20 where .Net Framework Documentation states only 2.

Hope this helps anyone with the same issue ;)

Running xUnit Console inside VS Express IDE

I just downloaded the VS 2008 Express SP1 yesterday, and got excited because now the Visual Web Developer 2008 Express Edition supports Web Application and Class Library Projects, so more things can be done with the express edition, thanks to the VS Express team ;)

Usually we can use the unit test inside the VS IDE itself by using TestDriven.Net, however express edition will not support VS addins, so yeah it's the downside of it but anyway it's FREE, so live with it :P

I'm currently using xUnit which comes with a GUI interface and a console runner, i find it always better if we can use it inside the VS IDE itself without having to switch program using ALT+TAB to run the GUI / Console runner.

Wouldn't it be nicer if we can do it inside the IDE itself? So this is how i setup it in my VS IDE :
1. Go to Tools -> External Tools
2. Add new menu :

• Title : x&Unit (& is used for shorcut keys)
  
• Command : D:\Extension\xUnit\xunit.console.exe (This should be the your console runner location)
  
• Arguments : $(BinDir)$(TargetName)$(TargetExt)
  **Updates : In VS2005 / VS2003 : $(ItemDir)bin\Debug\$(TargetName)$(TargetExt)
  
• Check the Use Output window
  

Now in the Tools menu, you can find the new xUnit menu item.

So now, after you finish building the project, you only need to use the shortcut ALT T+U and it will out like this below :)


**Updates :
This is how to bind a keyboard shortcut to the unit test menu item, based on Distant Sounds's comment.
You will need to choose Tools.ExternalCommand(n), where n is the sequence order of your unit test menu item.
and then you get another option to execute the unit test ;)

Hope this helps ;)

Setup Local FTP Server for BizTalk 2006 FTP Adapter Development

Today, I was asked by my colleague to find out more about BizTalk 2006 FTP Adapter, because we may use that for future projects.

Setup my local PC with Windows XP Professional SP 2

1. Windows XP comes with built in FTP Site under the IIS Installation, you can install them through Add or Remove Programs -> Add/Remove Windows Component.
You can follow the steps from Mark Salloway's post and Mike D.'s article for FTP overview.

2. Open Computer Management -> System Tools -> Local Users & Groups -> Users

3. Create a new local user named FTPUser with the password

4. Create a folder named BizAppFTP and several subfolders :

• In -> Receive Location
• Out -> Send Port
• Log -> FTP Adapter Log Folder
• Sample -> Contain sample files for input

Right click on the BizAppFTP Folder -> Properties -> Security Tab -> Add FTPUser into the list and allow modify access.

5. Open Computer Management -> Services and Application -> Internet Information Services -> FTP Sites -> Default FTP Site

6. Create a virtual directory named BizApp and set the path to the previously created BizAppFTP folder. Check Read, Write, and Log visits options for the virtual directory.

7. Open BizTalk Server 2006 Administration Console

8. Create a one-way receive port named rp_TestFTP and a receive location rl_TestFTP and configure the adapter

• File Mask : *.xml
• Folder : BizApp/In
• Log : D:\Temp\BizAppFTP\Log\Receive.Log
• Password : (Fill this with FTPUser password)
• Server : (Your server name / IP Address)
• User Name : FTPUser

9. Create a send port named sp_TestFTP and configure the adapter

• Folder : BizApp/Out
• Log : D:\Temp\BizAppFTP\Log\Send.log
• Password : (Fill this with FTPUser password)
• Server : (Your server name / IP Address)
• User Name : FTPUser

10. Set the filter expression for the sp_TestFTP to subscribe the message from rp_TestFTP receive port.

Testing Time

1. Create a sample xml file in then sample folder with only inside.
2. Copy and paste the file into the "In" folder

3. Depends on the polling interval, it will pick up the file through rl_TestFTP and then send the file directly to the "Out" folder through sp_TestFTP.

4. The output file was generated and if you can see the Receive.Log and Send.Log for the FTP activities.
Similar to FILE adapter, the adapter will try to get the file and delete the file, this is why you will need to have a user who has privilege to delete the file and by using this built in FTP, the security can be configured through NTFS.

You may want to read more on the FTP Adapter in the BizTalk documentation, especially on the security part where it mentions "FTP is, by nature, not secure: The user name, password, and other credentials traverse the network in clear text".

I'll post more later when I have the project at hand.
Hope this helps :)

BizTalk Server 2006 Development with SQL Express 2005 - Job Agent Replacement

BizTalk Server 2006 can be installed with the SQL Express 2005 edition.

I'm currently using SQL Express 2005 for the development purpose because it is free so we don't have to worry about the licensing.

The downside of using SQL Express 2005 is that the job agent is not available anymore, where BizTalk has several clean up jobs which need to run to optimize the database performance.
Usually we don't have to worry about this since we're only using for development or testing purpose, but I'm currently investigating a problem with our singleton orchestration, we suspect that by using a singleton orchestration, some messages were cleaned up in the database after they are completed.

So how to investigate this problem in my local development PC where we don't have running biztalk clean up jobs in SQL Express 2005?
Luckly, I found this article which mentions about SQLScheduler.
It's free and we can have unlimited instances with unlimited jobs :)

I just tried to setup the jobs based on the jobs which were created in SQL Server Instances, and they seems to work fine because the stored procedures for the jobs were created in the SQL Express Database as well.

This is the job details example for MessageBox_Message_Cleanup_BizTalkMsgBoxDb :

Go and give it a try for yourself, it's more than enough for development / testing purpose :)

BizTalk 2006 Oracle Adapter (ODBC) - An alternative for Poll Statement with Function (Not Procedure)

In Oracle Adapter, there are several ways to poll the data from the database, in this post, I will be discussing on the polling data without triggers, just by using Poll SQL Statement and Post Poll SQL Statement.

Part 1 - The First Project
We have a table of data which will be polled by several orchestrations based on a status field in the table to process, see below screen for the details.

By using Poll SQL Statement to get the rows and Post SQL Statement to update the rows to other status is actually working fine for most of the cases, and it is important to note that they are both executed within a transaction of serializable isolation level.

In my first project, it is working fine, because the frequency is quite low, poll 1 row of data / 60 seconds. Life is good ;)

Part 2 - The Second Project
Ok, now in which kind of scenarios where it is not working as expected?

We have a new application which needs to poll rows of data in short interval, because the expected data to process will be huge, so it is not acceptable for us to poll 1 row / 60 seconds.
In short, we need to process 3.000 rows of data in 1 hour, so that leaves us with 50 rows / 60 seconds.

So what's the issue with that? just change the query to select top 50 and off you go? hhhmm... not as smooth as we thought :P

Reasons : As the processing got huge, it seems that the server and/or the adapter are affected as well. Because of the latency, it seems that the Post Poll SQL Statement is not executed in the expected time, this cause the subsequent polls may poll the same data as the previous one, because the Update the row status in the Post Poll SQL Statement hasn't been executed yet. This eventually lead to double processing of the data.

Part 3 - The Search for Alternatives
What is the ideal way for this? I would say that call a procedure to poll the data, because we can get the rows and update them in the same time. And btw, I don't really like the way Poll SQL Statement and Post SQL Statement work, we need to make sure that the conditions / where statements in the Poll SQL Statement and Post Poll SQL Statement are the same or we'll get nasty update results :P

Unfortunately, I have been trying for calling a procedure without any good results for quite a while now, and i have posted some questions about this in the web and they have been replicated to many places now :P links

Well, several people have mentioned that they are able to use oracle procedure to poll the data, however I haven't received more details about it until now.
http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=2527819&SiteID=17
http://www.microsoft.com/communities/newsgroups/en-us/default.aspx?dg=microsoft.public.biztalk.general&tid=97564c53-15ee-4a8e-997c-72f8c2520241

The main problem with oracle procedure, it will need an output parameter to return a resultset, and I was not able to think a way to work with this parameter in the Poll SQL Statement.

So several days ago, i stumbled on a post (I forgot where from) but someone from the post mentioned about using function in the select statement like this below :

Select packagename.functionname from dual;

This gave me an idea, because it will just like a normal select query which returns row(s).

So the next thing to do is to have the DML statement to update the selected rows, I found a way to allow DML Statement in the function, is to use PRAGMA AUTONOMOUS_TRANSACTION which will summon its own transaction so it will not affect the caller transaction.

Part 4 - The Solutions

Solution 1 - Get one row
1. Create an oracle function to select and update the row then return the unique key

CREATE OR REPLACE FUNCTION pollonejob RETURN NUMBER IS
PRAGMA AUTONOMOUS_TRANSACTION;
JOBID NUMBER;
BEGIN
SELECT JOB_ID
INTO JOBID
FROM JOBS
WHERE JOB_STATUS = 'NEW'
AND ROWNUM = 1 FOR UPDATE;

-- This is to test the lock only
--DBMS_LOCK.SLEEP(3);

UPDATE JOBS
SET JOB_STATUS = 'POLLED'
WHERE JOB_ID = JOBID;

COMMIT;

RETURN JOBID;
END;
/

2. Setup the Poll SQL Statement :
SELECT pollonejob AS JOBID FROM dual

Or, if you can do it like this to retrieve more information based on the key :
SELECT job_id, job_desc, job_status FROM jobs WHERE job_id = pollonejob

Solution 2 - Get more rows
1. Create an oracle type as the container for the unique keys
CREATE OR REPLACE TYPE number_key_t is TABLE OF NUMBER

2. Create an oracle function returning the previously created type with a parameter to speficy how many rows to retrieve
CREATE OR REPLACE FUNCTION pollmultiplejob(noOfRows NUMBER) RETURN NUMBER_KEY_T IS
PRAGMA AUTONOMOUS_TRANSACTION;
l_data NUMBER_KEY_T := NUMBER_KEY_T();
BEGIN
SELECT JOB_ID
BULK COLLECT
INTO l_data
FROM JOBS
WHERE JOB_STATUS = 'NEW'
AND ROWNUM <= noOfRows FOR UPDATE;

-- This is to test the locking only
--DBMS_LOCK.SLEEP(3);

UPDATE JOBS
SET JOB_STATUS = 'POLLED'
WHERE JOB_ID IN (SELECT column_value FROM TABLE(CAST(l_data AS NUMBER_KEY_T)));

COMMIT;

RETURN l_data;
END;
/

3. Setup the Poll SQL Statement :
SELECT column_value FROM TABLE(CAST(pollmultiplejob(20) AS NUMBER_KEY_T))

Or, if you can do it like this to retrieve more information based on the key :
SELECT job_id, job_desc, job_status
FROM jobs WHERE job_id IN (SELECT column_value FROM TABLE(CAST(pollmultiplejob(20) AS NUMBER_KEY_T)))

Notes :
1. You do not need the Post Poll SQL Statement anymore, since when the Poll SQL Statement is executed, the rows will be updated in the same time it polls
2. Avoid setting the poll interval less than the time to execute the polling query, for example if the polling takes 30 seconds to complete, it's better to set the poll interval 40 seconds or more, consider the future data growth
3. As the function will update the data when being called in the select query, please do handle that more carefully by giving more meaningful function name, so everyone will know that the function will not only retrieve the data but also update the rows in the same time.

I have tested this with one receive location for several days now and it seems that they are working fine as expected :)

I'm just glad that I have this workaround or you might say that this is a bit hack :P but it did solve my problem and work for Oracle Adapter with ODBC, and i hope it will be useful for you guys as well.

**Update** I have tried creating 5 receive locations calling the same function and there were no duplicate poll :D
However, there is a flaw, the FOR UPDATE statement may not work as expected when trying to poll the data sequentially where in oracle you will need to have a sub query like below :
SELECT JOB_ID
BULK COLLECT
INTO l_data
FROM JOBS
WHERE JOB_ID IN (
SELECT JOB_ID
FROM (
SELECT JOB_ID
FROM JOBS
WHERE JOB_STATUS = 'NEW'
ORDER BY JOB_ID
) A
WHERE ROWNUM <= 1
)
FOR UPDATE;
I experienced duplicate polls with this, so make sure you test your query first for any duplicate issue.

Feel free to drop any comments or questions ;)

Microsoft.ServiceModel.Channels.Common.ConnectionException: ORA-1017: invalid username/password; logon denied

This is the first problem I had with the new oracle adapter pack.
From the error message, it is quite obvious right. But I was quite sure that I have provided the right user name and password.

I found the answer here, the user name / password may be case sensitive. After providing the user name and password with the appropriate case sensitivity, it is working now. hopla ;)

Installation and setup files for BizTalk 2006 R2 with Oracle Adapter Pack 3 (Evaluation Edition)

I'm currently trying BizTalk 2006 R2 with the new BizTalk Oracle Adapter Pack 3.0 (ODP.Net) to resolve the locking issue we had with the previous Oracle Adapter version with ODBC.

You can grab the installation files from here :

1. Oracle Data Access Component (ODAC)
   
2. BizTalk Server 2006 R2 Evaluation Edition (Note: You will not be able to perform upgrade with the evaluation edition, you will need to uninstall the current BizTalk development edition first and then install the evaluation edition)
3. WCF LOB Adapter SDK
4. BizTalk Oracle Adapter Pack 3.0 (Oracle adapter included inside)
5. BizTalk Adapter Pack: Oracle Database Adapter Samples
   

FOR UPDATE statement may not work with BizTalk Server 2006 Oracle Adapter ODBC

I created a simple test console application today for testing FOR UPDATE statement using different types of component.

1. Using ODP.Net - Working
Note : The second execution will wait until the first execution to finish first.

2. .Net Oracle Client - Working
Note : A slight different between odp.net and .net oracle client is that the second will wait until the first program ends, so eventhough the first program has finished the execution, it was still waiting until i close the first program, i suspect there is a different transaction handling between them.

3. ODBC - Not working
Note : Quite a surprising result, but as we expected. They seems to ignore the FOR UPDATE statement.

Then I found these information at MSDN and oracle sites for ODBC :
http://msdn2.microsoft.com/en-us/library/ms711792(VS.85).aspx
http://msdn2.microsoft.com/en-us/library/ms713566(VS.85).aspx
http://forums.oracle.com/forums/thread.jspa?threadID=607056&tstart=60
http://www.oracle.com/technology/software/tech/windows/odbc/htdocs/ODBCFAQ.pdf

It says that before the statement is passed to the driver, the FOR UPDATE clause will be removed first and this seems to apply not only for Oracle ODBC, but as a standard ODBC Programming reference.

My conclusion : As long as we're using Oracle Adapter with ODBC, we'll stuck with this unless we use the new Oracle WCF LOB Adapter with ODP.Net which requires BizTalk Server 2006 R2 and .Net Framework 3.0.

Update : Below is the query result which displays the executed queries in the oracle, see that the query for TestOracleLock.exe (2nd row) didn't have the FOR UPDATE statement which I specified in the code earlier.

You can use this query below to get the result above :

Select sql_text, module, first_load_time, last_load_time, users_opening, users_executing
From v$SQL
Where last_load_time != ' '
Order By last_load_time DESC;

My Podcast List, Shared RSS Feeds, and Twitter

Podcast List
If you noticed in the right section after my shared feeds, I have compiled the list of podcasts (Audio only & w\Video) into a shared Google Notebook, I have put it there a week ago but I didn't have anytime yet to blog it until now.

This is the podcast list which I'm currently subscribing and listening to them mostly in my daily commute to and from the office.

Shared RSS Feed
I also have a Google Shared feeds at the right section or you can open it up from here.

These are the tools which I have been using in my windows mobile 6 :
1. Viigo - RSS Feed
One great feature of Viigo is I can manage my subscription online through My Viigo website
2. BeyondPod - RSS Feed & Podcatcher
I only use this to subscribe my podcasts since I already have Viigo for RSS Feed

On the desktop / laptop, I'm using Google Reader for my feed subscriptions and yes I need to sync up manually of what i have read in Viigo and Google Reader, I do this because I don't have unlimited data subscription plan for my mobile. I'll probably wait until Google Gears for Mobile can be used for Google Reader Mobile ;)

Twitter
If you haven't used this, you might want to read Scott Hanselman's post and Robert Scoble's post to see whether it's worth for you to use.
Please add me if you already have a twitter id or you have just joined ;)

BizTalk Deployment with MSBuild without Custom Task

In this post, I will be discussing more on BizTalk Deployment to production environment.

I just had a deployment yesterday, the scenario :
We have some bug fixes to some orchestrations which we need to apply to an existing BizTalk application in production.

I have been interested in automated BizTalk deployment since I read a post about Creating highly-repeatable builds and deployments from Brian Loesgen. However, Brian's post is about compilation and export of the application to MSI, and he's using a plain batch command file.
Later I found a post about Using MSBuild to Build and Deploy BizTalk 2006 Solutions from Stephen W. Thomas. The post is more relevant to my scenario, however I found that there are some custom tasks being used to perform the deployment.

I'm trying to make things simpler by using the existing tools because we have a quite strict policy for deployment and I hope with this, others can also use it without having to dig in into the custom tasks.

So based on both articles, I managed to come out with a set of MSBuild files for the deployment.

1. Deploy.bat : Batch command file for the IT person to run the MSBuild with it parameters (project file & log file)
2. Build.proj : Main project file which specifies all the steps (targets) to execute
3. Build.properties : Definition of variables & collections for the deployment
4. Deploy.targets : List of steps (targets) for the deployment

Grab the files from here :


Scenario :

1. Stop BizTalk Host Instance
2. Register Helper library to GAC
3. Stop & Unenlist Orchestration
4. Import MSI
5. Install MSI
6. Import Binding File
7. Start & Enlist Orchestration
8. Start BizTalk Host Instance
9. Copy MyWebServices update files
   

Deploy.bat

@ECHO Starting up MS Build
C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\MSBuild.exe Build.proj /l:FileLogger,Microsoft.Build.Engine;logfile=Build.log;append=false;verbosity=diagnostic;encoding=utf-8

Build.proj

<Project xmlns="http://schemas.microsoft.com/developer/msbuild/2003" DefaultTargets="StopBizTalkServices;RegisterGAC;StopOrchestration;DeployMSI;InstallMSI;ImportBindingFile;StartOrchestration;StartBizTalkServices;DeployWS">
<Import Project="Build.properties" />
<Import Project="Deploy.targets" />
</Project>

Note: Specify the steps (targets) to execute at DefaultTargets attribute value

Build.properties

<Project xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
<PropertyGroup>
<!-- Path to the .Net utilities (gacutil.exe) folder -->
<DotNetPath>C:\Program Files\Microsoft.NET\SDK\v2.0\Bin\</DotNetPath>
<!-- Path to the BizTalk folder -->
<BizTalkPath>C:\Program Files\Microsoft BizTalk Server 2006\</BizTalkPath>
<!-- Path to the deployment root folder -->
<DeploymentPath>C:\DeploymentFolder\</DeploymentPath>
<!-- Path to the Web Services Deployment path -->
<WSDestinationPath>C:\Inetpub\wwwwroot\MyWebServices\</WSDestinationPath>
<!-- Path to the build dll files -->
<GACPath>$(DeploymentPath)\Common Library\</GACPath>
<!-- Path to the msi files -->
<MSIPath>$(DeploymentPath)\MSI Files\</MSIPath>
<!-- Path to the binding files -->
<BindingPath>$(DeploymentPath)\Binding Files\</BindingPath>
<!-- Path to the WMI script files -->
<WMIScriptPath>$(DeploymentPath)\WMIScripts\</WMIScriptPath>
<!-- Name of the BizTalk Application to deploy code into -->
<BTApplicationName>MyBizApp</BTApplicationName>
<!-- Bts Log File -->
<BtsLogFile>BtsTask.log</BtsLogFile>
<!-- MsiExec Log File -->
<MsiLogFile>MsiExec.log</MsiLogFile>
</PropertyGroup>

<!-- List all .net items that need to be GACed -->
<ItemGroup>
<GacStuff Include="MyLibrary1.dll" />
<GacStuff Include="MyLibrary2.dll" />
</ItemGroup>
<!-- List all MSI Files -->
<ItemGroup>
<MSIFile Include="MyBizApp.msi" />
</ItemGroup>
<!-- List all affected BizTalk Services to restart -->
<ItemGroup>
<BizTalkServices Include="BizTalk Service BizTalk LOCAL : BizTalkServerApplication" />
</ItemGroup>
<!-- List all web services files to deploy -->
<ItemGroup>
<WSFiles Include="$(DeploymentPath)\MyWebServices\**\*.*" />
</ItemGroup>
</Project>

Note: I grouped the deployment files into certain folders to make it easier to understand :

1. Common Library folder -> Consists of helper dlls which needs to be GAC-ed seperately
2. MSI Files folder -> BizTalk generated MSI Files to import and to install
3. Binding Files folder -> Binding Files to import
4. WMIScripts folder -> Consists of vbs files to stop (StopOrch.vbs) & start (EnlistOrch.vbs) the orchestrations, you can find the vbs files under the C:\Program Files\Microsoft BizTalk Server 2006\SDK\Samples\Admin\WMI
5. MyWebServices folder -> Consists of updates for the existing MyWebServices

Deploy.targets

<Project  xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
<!-- START DEPLOYMENT -->

<!-- Stop BizTalk Services -->
<Target Name="StopBizTalkServices" >
<Exec Command="NET STOP "%(BizTalkServices.Identity)"" ContinueOnError="true" />
<Message text="Stopped BizTalk Services : %(BizTalkServices.Identity)" />
</Target>

<!-- Register GAC -->
<Target Name="RegisterGAC" >
<Exec Command=""$(DotNetPath)gacutil.exe" /i "$(GACPath)%(GacStuff.Identity)" /f" />
<Message text="Registered DLLs to GAC : %(GACStuff.Identity)" />
</Target>

<!-- Stop Orchestration : The process sequence matters because of the dependency -->
<Target Name="StopOrchestration" >
<Exec Command="cscript.exe $(WMIScriptPath)StopOrch.vbs [Orchestration name 1] [Assembly Name 1] Unenlist" />
<Exec Command="cscript.exe $(WMIScriptPath)StopOrch.vbs [Orchestration name 2] [Assembly Name 2] Unenlist" />

<Message text="Stopped and Unenlisted Orchestrations" />
</Target>

<!-- Deploy MSI -->
<Target Name="DeployMSI" >
<Exec Command=""$(BizTalkPath)BtsTask.exe" ImportApp /Package:"$(MSIPath)\%(MSIFile.Identity)" /ApplicationName:$(BTApplicationName) /Overwrite >> $(BtsLogFile)" />
<Message text="Deployed $(BTApplicationName)" />
</Target>

<!-- Install MSI in quite mode -->
<Target Name="InstallMSI" DependsOnTargets="DeployMSI">
<Exec Command="MsiExec.exe /i "$(MSIPath)\%(MSIFile.Identity)" /quiet /l+ $(MsiLogFile)"  />
<Message text="Installed %(MSIFile.Identity)" />
</Target>

<!-- Import Binding File -->
<Target Name="ImportBindingFile" DependsOnTargets="DeployMSI">
<Exec Command=""$(BizTalkPath)BtsTask.exe" ImportBindings -Source:"$(BindingPath)$(BTApplicationName).BindingInfo.xml" -ApplicationName:$(BTApplicationName) >> $(BtsLogFile)" />
<Message text="Imported Binding for $(BTApplicationName)" />
</Target>

<!-- Start Orchestration : The process sequence matters because of the dependency -->
<Target Name="StartOrchestration" DependsOnTargets="DeployMSI;ImportBindingFile">
<Exec Command="cscript.exe $(WMIScriptPath)EnlistOrch.vbs [Orchestration Name 2] [Assembly Name 2] Start" />
<Exec Command="cscript.exe $(WMIScriptPath)EnlistOrch.vbs [Orchestration Name 1] [Assembly Name 1] Start" />

<Message text="Enlisted and Started Orchestrations" />
</Target>

<!-- Start BizTalk Services -->
<Target Name="StartBizTalkServices" DependsOnTargets="DeployMSI;ImportBindingFile;StopOrchestration">
<Exec Command="NET START "%(BizTalkServices.Identity)"" ContinueOnError="true" />
<Message text="Started BizTalk Services : %(BizTalkServices.Identity)" />
</Target>

<!-- Update the Web Services -->
<Target Name="DeployWS">
<Copy SourceFiles="@(WSFiles)" DestinationFiles="@(WSFiles->'$(WSDestinationPath)%(RecursiveDir)%(Filename)%(Extension)')" />
<Message text="Deployed Web Services" />
</Target>

<!-- END DEPLOYMENT -->
</Project>

Note: As you see above, I only use existing tasks such as exec and copy for the deployment and I use BtsTask.exe for BizTalk deployment. For the complete list of MS Build tasks, you can find it here.


After executing the deploy.bat, there will be 3 log files :

1. Build.log -> MSBuild generated log file which contains all the deployment activities
2. BtsTask.log -> BtsTask.exe generated log file for BizTalk deployment
3. MsiExec.log -> MsiExec.exe generated log file for BizTalk Msi File installation

Notes:

• GACUtil.exe may not be available in the server if the SDK is not installed, I did it manually using the Microsoft .NET Framework 2.0 Configuration yesterday.
• cscript.exe will not have return success or fail to MSBuild so it will not stop when there is any error.
• There is one tweak on the EnlistOrch.vbs, by default it will try to start and enlist the orchestration with the default host. Since I'm not using the default host for my application, i comment out the line 90 -> Inst.Enlist(HostName) and use only Inst.Enlist
• I haven't tried yet, but the Build.properties and Deploy.target files should be able to be merged into the Build.proj itself, however by splitting them into 2 files, they are easier to maintain and understand as well.

Why bother ourselves to use MSBuild than to use plain batch command for this like Brian's post?

1. MSBuild provide built-in file log mechanism for the whole execution process
2. Better and clearer dependency specification between tasks
3. MSBuild comes with .Net Framework 2.0 which means that every BizTalk server should have MSBuild installed without any additional installation efforts.
   

My wish list :

1. MSBuild provides new Built-in task for GACUtil
2. BizTalk Server provides add-on BizTalk tasks for MSBuild
   

Please leave questions or comments if you have any ;)
Hope this helps :)