MVP title activated again

My title was suspended this week and now its activated again. I was missing my MVP tag from forums and I am happy to see it back again. I thank Microsoft again for resolving my NDA issue. I would like to thank my nominator, fellow MVPs, my MVP leads and the Program manager for Global MVP program as they resolved it very quickly.

SQL Server Query Notification with BizTalk Server 2009 WCF SQL Adapter

SQL Server 2005 introduced query notifications which allow applications to subscribe to the database and receive notifications from the database based on the changes in the result set of the query on which the application is subscribed. This can change the behavior and performance of the application as the application does not have to query the database in order to get changes. For e.g. if a service/application has cached the data it can refresh its cache whenever there is a change in the results of the cache data. In this way efficiently and in real time the data can be refreshed.

With regard to BizTalk previously we only used polling (using the SQL Server adapter) in order to get the results from the database. Polling would be a heavy operation depending on the polling interval and the results being returned which would affect the overall BizTalk server performance. But now by utilizing the Query notification feature of SQL Server 2005/2008 BizTalk server can receive notifications whenever there are changes in the result set of the query. For further reading you can read Using Query Notification on MSDN. Before planning to use the SQL Query notifications using the WCF adapter please go through Considerations for Receiving Query Notifications Using the Adapter on MSDN.

Generating schemas from Consume adapter service Wizard

For using Query notifications in BizTalk the first step is to use the Consume Adapter Service Wizard to generate the schemas. You can start the wizard by Right click your project->Add Generated Items -> Consume Adapter service. In order to use the SQL WCF service you have to select sqlBinding from the wizard and supply the SQL URI. Please refer to SQL Server Connection URI on MSDN. Click the configure button and configure the mssql URI.

1-      In the Security tab choose the credential type (windows/username) and supply the username/password if using SQL Authentication.

2-      In the URI tab supply the URI properties. Supply the database name in the InitialCatalog property, SQL Server Instance Name and the SQL server name/IP in the Server property. The inbound id is used for typed polling and it makes the URI unique.

3-      In the Binding properties go to the Inbound property group and set the InboundOperationType property to Notification. For complete binding properties read Working with BizTalk Adapter for SQL Server Binding Properties on MSDN.

4-      Since you are using polling and you have set the Inbound operation type to Notification you will set the Notification properties in which Notification Statement is specified. Notification statement is the query based on which notifications will be received by the adapter. Whenever there will be changes in the result set returned by the query, SQL notifications will be sent. For complete reference for creating Query notification read Creating a Query for Notification on MSDN. In my case the query was ”Select [columns] from MAR_SP_INFO_V2”.

In the end two files will be generated one is the simple schema with three fields as shown below and the other is a binding file xml.

Setting up the Orchestration for Query Notification and processing the results

When the notification is received in the orchestration the orchestration has to determine the type of notification. As the WCF adapter will return two types of notifications.

1-      Notifications based on the changes on the result set.

2-      Notification when receive location was enabled after a failure.  

The adapter will send notification whenever the receive location is back up again when NotifyOnListenerStart is set to true in the binding properties. But beware that the adapter does not perform any activity when the receive location is down and there are changes in the database. The adapter will start notification after the receive location is up again. For e.g. when the receive location was down and a few records were inserted and updated when it will come up again it will not notify what had happened. The orchestration must have an implementation to determine the changes. For this you can read Receiving Query Notifications After a Receive Location Breakdown on MSDN.

The schema that was generated from the wizard will have three fields Info, Source and Type. In the orchestration the first step would be to have a decide shape to decide which type of notification was received by the orchestration. I decided to distinguish all the three fields so that I can use them in my orchestration if you do not you can use xpaths to extract the value of the fields.

In the decide shape first check the Info field and Source. If the Info is “ListenerStarted” and Source is “SqlBinding” and Type is “Startup” you can proceed to the logic to detect changes to the database while the receive location was down. If the Info is “Insert/Update or Delete” operation the Source would be “Data” and Type would be “Change”.

Field On Data changes in the database On Listener Start (Receive location enabled)
Info Insert/Update or Delete ListenerStarted
Source Data SqlBinding
Type Change Startup


In my orchestration I am doing nothing for receiving Listener start notifications or for Updates and Deletes and I am only interested in taking actions against the insert operation in the table therefore I am checking this in my decide shape. I will devise some mechanism to check if my receive location went up after going down what shall I do.

For now I need to get the new records which are inserted and process them. I am using the WCF SQL Adapter and selecting all the records whose StatusRecord field is set to NEW. NEW is the default value for a new record that is inserted for me to identify the records. I will write in detail in my next post how I am using the WCF SQL Adapter for selecting the records. When I select the records and finish processing them I update the StatusRecord column to READ.

I pass the whole select message response to my helper class where all the processing is done and if the operation is successful I log the results.

Configuring the WCF Custom Adapter properties for Notification

There are again two ways to configure the adapter properties, first you can redefine the properties here or you can directly import the properties from the binding file which was generated by the WCF adapter service wizard. For that you can refer to Configuring a Physical Port Binding Using a Port Binding File on MSDN. I haven’t looked into it but will use when needed.

I will have manual bindings for the WCF Custom adapter. When finished with the orchestration you have to build and deploy the BizTalk application. Then from the BizTalk administration console open the receive port node and create a new receive port. Then create a new receive location. Select the type as WCF-Custom and use the default XML Receive pipeline. Click the configure button to configure the adapter properties.

In the general tab specify the address URI. You can copy paste that from the Binding file generated by the Consume Adapter Service wizard.

In the Other tab specify the username and password for the database. Otherwise you will get user credential error.


Now for the Binding properties go to the Binding Tab and select sqlBinding as the Binding Type. You will see all the binding properties below. We will be interested only in the notification binding properties. Set inboundOperationType as Notification, set the notificationStatement property to the SQL Query. On the basis of this query result set a notification will be sent to the Orchestration. And notifyOnListenerStart property to True if you want to receive the notification when the receive location is enabled. In my case it is false.

MVP title suspended due to NDA

Three days back I went to the BizTalk forums and noticed my MVP tag was removed. I thought of some bug in the forum as the tag was entered after 45-50 days after getting my award so I couldn’t believe that something was wrong with my account. Then I went to my MVP profile and was happy to see that it was still there. I couldn’t login into my profile then certainly I noticed with my email a message saying “SuspendedforNDA-” with my email address and I was completely freaked out.

The NDA (non disclosure agreement) are for MVP’s to abide by the points in the agreement. I did accept the electronic version of the agreement but that agreement is valid upto 3 months which is over now. I was suppose to sign the hard copy of NDA which I was about to receive with my MVP award kit. Unfortunately I didn’t receive it. Why? Again the reason is wierd It was re-routed by my regional MVP lead as he received it from Microsoft. I was given the tracking number of the shipment company but that tracking number was invalid. You must be wondering what a luck I have 😀 I contacted the tracking company the first thing they ask is the tracking number which was invalid and they told me to contact the source.

I sent about 10 emails on the follow up of the award kit but at the end I gave up and since then the end result is here. My MVP title is suspended until I sign the hard copy of the NDA and send it back. There is a global MVP support email address for the MVPs which is the only point of contact other than the regional MVP lead. I was informed that I will receive another MVP award kit as I didn’t receive it. For God’s sake please find another way not that again. I know it will take 2 more months and on the forums where I like the MVP tag will come after another 2 more months. After that my MVP renewal time will come and the cycle will repeat again.

Please God, Please Microsoft help this poor MVP.

%d bloggers like this: