Recently I noticed some odd behavior when I was troubleshooting a SQL integration scenario with BizTalk 2010. I was using WCF-Custom adapter to perform Typed-Polling that executed a stored procedure. This stored procedure was using dynamic SQL to fetch the data because it is targeting multiple tables with one generic stored procedure.
In this blog post I will tell you how we implemented this scenario as well as where the adapter was failing when polling.
Next to that I will talk about some “problems” with the receive location and the adapter.
I will finish with some small hints that made our development easier.
Scenario
In this simplified scenario we have an application that is polling on two tables called ‘tbl_Shipment_XXX’ where XXX is the name of a warehouse. Each warehouse will have a corresponding receive location that will poll the data that is marked as ready to be processed.
This is performed by using a stored procedure called ‘ExportShipments’ which requires the name of the target warehouse and will use the proceed in the following steps – Lock data as being processed, export data to BizTalk & mark as successfully processed.
Creating our polling statement
In our polling statement we will execute our generic stored procedure. This procedure will simply mark our data as being processed, execute a function that will return a SQL statement as a NVARCHAR(MAX). Afterwards we will execute the statement & mark the data as processed.
CREATE PROCEDURE ExportShipments
@Warehouse nvarchar(10)
AS
BEGIN
SET NOCOUNT ON;
— MARK DATA AS LOCKED
DECLARE @lockData NVARCHAR(MAX);
SET @lockData = N’UPDATE [ShopDB].[dbo].[tbl_Shipment_’ + @Warehouse + ‘] SET [STATUS_ID] = 7 WHERE [STATUS_ID] = 1′;
EXEC(@lockData);
— EXTRACT DATA
DECLARE @exportData NVARCHAR(MAX);
SET @exportData = [ShopDB].[dbo].[ComposeExportShipmentSelect] (@Warehouse)
EXEC(@exportData);
— MARK DATA AS PROCESSED
DECLARE @markProcessed NVARCHAR(MAX);
SET @markProcessed = N’UPDATE [ShopDB].[dbo].[tbl_Shipment_’ + @Warehouse + ‘] SET [STATUS_ID] = 10 WHERE [STATUS_ID] = 7’;
EXEC(@markProcessed);
END
In our function we will compose a simple SELECT-statement where we fill in the name of our warehouse.
CREATE FUNCTION ComposeExportShipmentSelect
(
@Warehouse nvarchar(10)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
— DECLARE SELECT-STRING
DECLARE @result NVARCHAR(MAX);
— COMPOSE SELECT STATEMENT
SET @result = ‘SELECT [ID], [ORDER_ID], [WAREHOUSE_FROM], [WAREHOUSE_TO], [QUANTITY] FROM [ShopDB].[dbo].[tbl_Shipment_’ + @Warehouse + ‘] WHERE [STATUS_ID] = 7’;
— RETURN SELECT STRING
RETURN @result
END
I chose to seperate the composition of the SELECT-statement in a function because we were using a lot of JOIN’s & UNION’s and I wanted to seperate this logic from the stored procedure for the sake of code readability.
Although our scenario is very simple we will use a function to illustrate my problem.
Generating the Types-polling schema
Now that everything is set in our database we are ready to generate our Typed-Polling schema for the WarehouseA-polling to our BizTalk project.
1. Right-click on your project and select Add > Add generated items… > Consume Adapter Service
2. Select the SqlBinding and click “Configure”
3. Fill in the Server & InitialCatalog and a unique InboundId. The InboundId will be used for in the namespace of your schema and URI of your receive location. Each receive location requires its own schema. I used “WarehouseA” since I am creating the schema for polling on “tbl_Shipments_WarehouseA”.
4. Configure the binding by selecting “TypedPolling” as the InboundOperationType and execute our stored procedure as PollingStatement with parameter “WarehouseA”. (Note that we are not using ambient transactions)
5. Click Connect,select the “Service” as contract type and select “/” as category. If everything is configured correctly you can select TypedPolling and click Properties that will show you the metadata. If this is the case click Add and a schema will be generated.
The wizard will create the following schema with a sample configuration for your receive location.
It’s all about metadata
The problem here is that executing the dynamic SQL doesn’t provide the required metadata to BizTalk in order to successfully generate a schema for that result set.
I solved this by replacing the function ‘ComposeExportShipmentSelect’ with a new stored procedure called ‘ExportShipment_AcquireResults’.
In this stored procedure will execute the dynamic SQL and insert the result set into a TABLE and return it to the caller. This tells BizTalk what columns the result set will contain and of what type they are.
CREATE PROCEDURE ExportShipment_AcquireResults
@Warehouse nvarchar(10)
AS DECLARE @result
TABLE(
[ID] [INT] NOT NULL,
[ORDER_ID] [NVARCHAR](50) NOT NULL,
[WAREHOUSE_FROM] [NVARCHAR](20) NOT NULL,
[WAREHOUSE_TO] [NVARCHAR](18) NULL,
[QUANTITY] [INT] NOT NULL
)
BEGIN
— DECLARE SELECT-STRING
DECLARE @select NVARCHAR(MAX);
— COMPOSE SELECT STATEMENT
SET @select = ‘SELECT [ID], [ORDER_ID], [WAREHOUSE_FROM], [WAREHOUSE_TO], [QUANTITY] FROM [ShopDB].[dbo].[tbl_Shipment_’ + @Warehouse + ‘] WHERE [STATUS_ID] = 7’;
— EXECUTE SELECT AND INSERT INTO RESULT
INSERT INTO @result
(
[ID],
[ORDER_ID],
[WAREHOUSE_FROM],
[WAREHOUSE_TO],
[QUANTITY]
)
EXEC(@select);
— RETURN RESULT SET
SELECT * FROM @result;
END
Our generic polling stored procedure simply execute our new stored procedure.
BEGIN
SET NOCOUNT ON;
— MARK DATA AS LOCKED
DECLARE @lockData NVARCHAR(MAX);
SET @lockData = N’UPDATE [ShopDB].[dbo].[tbl_Shipment_’ + @Warehouse + ‘] SET [STATUS_ID] = 7 WHERE [STATUS_ID] = 1′;
EXEC(@lockData);
— EXTRACT DATA
DECLARE @exportData NVARCHAR(MAX);
EXEC [ShopDB].[dbo].[ExportShipment_AcquireResults] @Warehouse
— MARK DATA AS PROCESSED
DECLARE @markProcessed NVARCHAR(MAX);
SET @markProcessed = N’UPDATE [ShopDB].[dbo].[tbl_Shipment_’ + @Warehouse + ‘] SET [STATUS_ID] = 10 WHERE [STATUS_ID] = 7’;
EXEC(@markProcessed);
END
When we regenerate our schema the problem should be resolved and your schema looks like this –
(Note that the new filename include your InboundId)
Why not use a #tempTable?
You can also achieve this by setting FMTONLY OFF and using a temporary table but this will not work in every scenario.
CREATE PROCEDURE ExportShipments
@Warehouse nvarchar(10)
AS
BEGIN
SET NOCOUNT ON;
— MARK DATA AS LOCKED
DECLARE @lockData NVARCHAR(MAX);
SET @lockData = N’UPDATE [ShopDB].[dbo].[tbl_Shipment_’ + @Warehouse + ‘] SET [STATUS_ID] = 7 WHERE [STATUS_ID] = 1′;
EXEC(@lockData);
— EXTRACT DATA
DECLARE @exportData NVARCHAR(MAX);
SET @exportData = [ShopDB].[dbo].[ComposeExportShipmentSelect] (@Warehouse)
— CREATE TEMP TABLE
SET FMTONLY OFF;
CREATE TABLE #tempTable
(
[ID] [INT] NOT NULL,
[ORDER_ID] [NVARCHAR](50) NOT NULL,
[WAREHOUSE_FROM] [NVARCHAR](20) NOT NULL,
[WAREHOUSE_TO] [NVARCHAR](18) NULL,
[QUANTITY] [INT] NOT NULL
)
— INSERT SELECT RESULTS INTO TEMP TABLE
INSERT INTO #tempTable
EXEC(@exportData);
SET FMTONLY ON;
— MARK DATA AS PROCESSED
DECLARE @markProcessed NVARCHAR(MAX);
SET @markProcessed = N’UPDATE [ShopDB].[dbo].[tbl_Shipment_’ + @Warehouse + ‘] SET [STATUS_ID] = 10 WHERE [STATUS_ID] = 7’;
EXEC(@markProcessed);
— RETURN RESULT
SELECT * FROM #tempTable;
END
We were using transactions on SQL-level and Try/Catch statements but this conflicted with the FMTONLY and resulted in a Severe error in the event log. Also this didn’t make any sense at all since we seperated the SELECT composition to a seperate function/stored procedure and the definition of the result set should be defined there.
If you want to read more about #tempTables, I recommend this post.
PollingDataAvailableStatement & no ambient transactions
With our schema generated I deployed my application to my machine and started creating the receive locations for the polling on my database. The configuration is pretty easy – No ambient transactions, Typed polling, pollingstatement is our stored procedure and specify a SELECT in the PollDataAvailableStatement to check if we need to run the stored procedure.
Apparently the PollDataAvailableStatement is only used when you enable ambient transactions according to this article.
Problem here is that when you clear out the PollDataAvailableStatement and start your receive location it will be disabled automatically with the following error –
PollDataAvailableStatement seems to be a mandatory field although it is not being used, I easily fixed it with “SELECT 0″.
Empty result sets & TimeOutExceptions
Later on the project we had to move from transactions on SQL-level to ambient transactions and our PollDataAvailableStatement was consulted before it ran the stored procedure.
In our stored procedure we used the found data to perform cross-references and return a set of data when required so it is possible that it is not required to return a set.
We started experiencing locks on our tables and TimeOutExceptions occured without any obvious reason.
It seemed that the adapter had a bug: when the adapter has found data it is required to return a result to BizTalk, if not it will lock SQL resources and lock the tables.
This issue can be resolved by installing the standalone fix (link) or BizTalk Adapter Pack 2010 CU1 (link)
Tips & Tricks
During the development of this scenario I noticed some irregularities when using the wizard, here are some of the things I noticed –
- It is a good practices to validate the metadata of the TypedPolling-operation before adding it. If something is misconfigured or your stored procedure is correct you will receive an error with more information.
If this is the case you should click OK instead of clicking the X because otherwise the wizard will close. The same error might occur when you finalize the wizard, then it is important to click cancel instead of ok or the wizard will close automatically.
- If your wizard closes for some reason it will remember the configuration of the previous wizard when you restart it and you can simply connect, select the requested operation and request the metadata or finish the wizard.
It might occur that this results in a MetadataException that says that the PollingStatement is empty and therefor invalid.This is a bug in the wizard where you always need to open the URI configuration although it still remembers your configuration. You don’t need to change anything, just open & close and the exception is gone.
Conclusion
In this blog post I highlighted the problem where I was unable to generate a schema for my stored procedure by only executing the result of my function. I also illlustrated how I fixed it and why I didn’t use FMTONLY and a temporary table.
Next to the generation of our schemas I talked about the problems with the receive location where it required a PollDataAvailableStatement even when it was not used and the locking of our tables because our stored procedure wasn’t returning a result set. Last but not least I gave two examples of common irregularities when using the wizard and how you can bypass them.
For me it is important to write your SQL scripts like you write your code – Use decent comments, seperate your procedure into subprocedures & functions according to the separation of concern.
While writing you scripts everything might sound obvious but will it in a couple of months? And how about your colleagues?
All the scripts for this post, incl. DB generation, can be found here.
Thank you for reading,
Tom.
Subscribe to our RSS feed