by community-syndication | Nov 2, 2011 | BizTalk Community Blogs via Syndication
Many people use TypedPolling when they are working with BizTalk and wanting to poll data from the SQL Server and publish it to BizTalk, but there is another way where you use notification.
This sample show how to use notification with the WCF-SQL adapter and BizTalk. It shows that you can use notifications without orchestrations to call a stored procedure in a SQL Server database (Adventureworks) and send output from this stored procedure into the file system. The execution of the stored procedure is only done when BizTalk receives a notification from the SQL Server about something has changed in a table.
The first thing that is needed is that the SQL Server you are working with has SQL Broker installed and it is configured for the database that you want to poll data from:
The next thing to do is to create a notification schema in your Visual Studio BizTalk project:
This will give you this schema:
The schema has 3 elements:
You should promote at least info and source element, so that you can use these fields for routing.
The next thing you need is to have a stored procedure that you can call to get the data from the SQL Server:
This procedure is just pretty simple and normally you would have some kind of logic to find out which rows that hasn’t been polled from the table yet. It could be a datetime field, a boolean field or something else. There are plenty of examples of this on the net.
You then need to create a schema for this procedure, so that BizTalk knows how to call the procedure:
The next thing you need is a simple map to create the request message that is send to the wcf-sql adapter, so that it knows that you want to execute this procedure:
Now you just need to get the Visual Studio project build and deployed to the BizTalk Server.
The BizTalk Server now needs to be configured to use the schemas and map that we have created in Visual Studio. The first thing is to setup a Receive Port and Location (remember to set the pipeline to XMLReceive):
The 3 properties that you want to set are (there are also some that you need to connect to the SQL Server, but you most likely know how to do that):
- The “InboundOperationType” needs to be set to “Notification”
- The NotificationStatement must have a select statement for the table you want to get notifications on “SELECT [DepartmentID] FROM [HumanResources].[Department]” in my example
- NotifyOnListenerStart must be True or False. If it is True it will give you a message when the receive port starts
The next thing you need is a request/response port where we send a request to the SQL Server that we want to execute a stored procedure:
Set the outbound map that we created:
Set the filter to route everything we get from the receive port (notification). Normally you would use the promoted properties in the notification schema to filter exactly when you want to poll and everything else in a garbage port (you always need a subscriber):
Now we just need a send port to subscribe to the output that the stored procedures returns. In this case it is just a file port, but it could be anything like a LOB system:
That is it. We have created content based routing based on the WCF-SQL adapters notifcation feature. The advantage is you get to poll the data from you SQL Server at the moment the insert/update and you don’t need to poll for available data every 30 seconds even if there isn’t any data. The disadvantage is that you will get some garbage notifications that you need to handle somehow and I don’t think I would use this method on high tables with a lot of transactions as you will get a lot of notifications on every event.
Code available at: http://code.msdn.microsoft.com/WCF-SQL-BizTalk-notificatio-2d0d4b7e
by community-syndication | Nov 2, 2011 | BizTalk Community Blogs via Syndication
Past few weeks I have contributed the following samples to MSDN Code Gallery:
- BizTalk Table Operations – Messaging
This sample will show how to perform simple table operations using WCF-SQL Adapter a part of BizTalk Server Adapter Pack. The Sample provided by Microsoft shows only Select statement operation. This sample will show all the other operations. It is based on the BizTalk Adapter Pack 2010 WCF-Custom with sqlBinding Table Operations through Messaging solution post.
- Polling SQL Azure Table – BizTalk Adapter Pack 2010
This sample will show polling a SQL Azure database table. It is based on Polling SQL Azure Database Table with WCF-SQL Adapter blog post.
- Sample – Table Operations on Oracle 11g XE with OracleDbBinding
This sample will show all the Oracle Table operations (in comparison with SQL, but there a few differences!). It is based on Table Operation on Oracle 11g XE with OracleDbBinding blog post.
- WCF-SQL Adapter Stored Procedure : SQL Azure
This sample will make use of BizTalk Adapter Pack as well and call Stored-Procedure in Azure. Sample is based on SQL Azure Stored Procedure blog post.
- ExecuteReader, ExecuteNonQuery, ExecuteScalar Operation with BizTalk Messaging
This sample will demonstrate three query operations through messaging in stead of using the WCF Service Model. BizTalk Server 2010 Adapter Pack Samples show only invoke ExecuteReader operation in SQL Server using ,NET. This sample shows all operations through messaging.
Cheers!
by community-syndication | Nov 2, 2011 | BizTalk Community Blogs via Syndication
Past few weeks I have contributed the following samples to MSDN Code Gallery:
- BizTalk Table Operations – Messaging
This sample will show how to perform simple table operations using WCF-SQL Adapter a part of BizTalk Server Adapter Pack. The Sample provided by Microsoft shows only Select statement operation. This sample will show all the other operations. It is based on the BizTalk Adapter Pack 2010 WCF-Custom with sqlBinding Table Operations through Messaging solution post.
- Polling SQL Azure Table – BizTalk Adapter Pack 2010
This sample will show polling a SQL Azure database table. It is based on my Polling SQL Azure Database Table with WCF-SQL Adapter post.
- Sample – Table Operations on Oracle 11g XE with OracleDbBinding
This sample will show all the Oracle Table operations (in comparison with SQL, but there a few differences!). It is based on my Table Operation on Oracle 11g XE with OracleDbBinding post.
- WCF-SQL Adapter Stored Procedure : SQL Azure
This sample will make use of BizTalk Adapter Pack as well and call Stored-Procedure in Azure. Sample is based on my SQL Azure Stored Procedure blog post.
- ExecuteReader, ExecuteNonQuery, ExecuteScalar Operation with BizTalk Messaging
This sample will demonstrate three query operations through messaging in stead of using the WCF Service Model. BizTalk Server 2010 Adapter Pack Samples show only invoke ExecuteReader operation in SQL Server using ,NET. This sample shows all operations through messaging.
Cheers!
by community-syndication | Nov 1, 2011 | BizTalk Community Blogs via Syndication
This is my article that I published in the magazine “Programar” which can be found here (in Portuguese) and that I now decided to share with you in English Much has been said already about the BizTalk Server platform, what it is, and the advantages offered to the organizations. For the more distracted ones, BizTalk […]
Blog Post by: Sandro Pereira
by community-syndication | Nov 1, 2011 | BizTalk Community Blogs via Syndication
This post continues a series where I revisit the classic Enterprise Integration Patterns with a cloud twist. So far, I’ve introduced the series and looked at the Shared Database pattern. In this post, we’ll look the second pattern: remote procedure invocation. What Is It? One uses this remote procedure call (RPC) pattern when they have […]
Blog Post by: Richard Seroter
by community-syndication | Nov 1, 2011 | BizTalk Community Blogs via Syndication
I guess you have, at some point, installed a certificate for the the BizTalk service user. The ability for BizTalk to use certificate to encrypt/decrypt messages is very powerful in some scenarios.
The recommended way of doing this is to simply log on to the machine as the service account. This is not always possible due to policies and restrictions at the client; so an intense mail conversation might ensue. It is usually resolved in some way.
One way to resolve it might be to try to us “runas” to execute mmc as the BizTalk service user.
runas /profile /user:BtsUsr "C:\Windows\SysWOW64\mmc.exe"
However, this is not possible as the following error is presented: “740: The requested operation requires elevation.”
The solution to this might be to disable the UAC on the machine, but that is usually not possible either.
The solution I found to be the best is the following:
1. Add the BizTalk Service User the Local Admin group.
2. Then navigate to the appropriate system-folder (System32 or SysWow64).
3. Find and select MMC.exe, hold down shift and right-click to get the “Run as different user” option.
4. Choose to run as different user and supply the credentials for the BizTalk Service user.
6. Click Ok to elevate the process and boom you are in!
Of course you have to remove the service account from the Local Admin group as soon as possible after installation.
The strange thing is that the “runas”-approach still does not work but who cares.
Blog Post by: Mikael Sand
