Recently we had to implement batching on the WCF SQL adapter.  Part of the reason for this post is whatever I do on the batch property on the settings of adapter it still wants to stream all polled results in one go.  This means BizTalk or AppFabric will wait until all results are received before starting to process them.  If you have 10,000 rows this could be 15-20 mins.

I came up with a neat solution to prevent this from happening by changing the polling SQL statement.  The below code will select the top 10 records and then update those 10 in a transaction creating the batching that I desire.  To modify the batch of results just change the top statement:

SELECT top 10 * FROM dbo.Table1 WHERE Status= ‘N’;
UPDATE dbo.Table1 SET Status= ‘Y’ FROM (SELECT TOP 10 ID FROM dbo.Table1WHERE Status= ‘N’) b JOIN dbo.Table1bx ON b.ID= b.ID

- John Shaw

Today in BizTalk land I received the following error when I tried to configure BizTalk Standard on a 64 bit server  ”Failed to connect to the SQL database SSODB on SQL Server”

After searching other blogs the reason for the issue is because the SQL types are missing.  You need to run 32-bit and 64-bit regasm.exe to get it fixed:

“C:\Windows\Microsoft.NET\Framework64\v2.0.50727\regasm.exe” “C:\Program Files\Common Files\Enterprise Single Sign-On\SSOSQL.dll”

“C:\Windows\Microsoft.NET\Framework\v2.0.50727\regasm.exe” “C:\Program Files\Common Files\Enterprise Single Sign-On\SSOSQL.dll”

Visual Studio isn’t required for the above commands to work.  Not sure if the above will also fix the SQL Server 2008 R2 BizTalk 2009 compatibility issue…

- John Shaw

All,

After spending hours and reading the following blog http://social.msdn.microsoft.com/Forums/en-US/biztalkr2adapters/thread/0544b0cc-2044-499b-a824-5fefde90e77e.   Big thanks to Dejan and Malcolm.

The fix to get the MQSC adapter working in Windows 2008 64 bit environment with BizTalk perform the following steps:

-  Install MQ 7.0

-  Install MQ 7.0.3 Break Fix

-  Install BizTalk Host Adapters 2.0

-  Put service account of MQ in the local mqm

-  Save the below as a reg file and execute.

John

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\Classes\Wow6432Node\CLSID\{CF8B4D15-5D2B-4c62-90DB-16804D2454B9}]
@=”MQSC Adapter Class”
“AppID”=”{401D2C40-DAA9-4a63-8D42-9BF98B04F616}”

[HKEY_LOCAL_MACHINE\SOFTWARE\Classes\Wow6432Node\CLSID\{CF8B4D15-5D2B-4c62-90DB-16804D2454B9}\BizTalk]
@=”BizTalk”
“TransportType”=”MQSC”
“Constraints”=dword:00003d0b
“ReceiveLocation_PageProv”=”{2DE93EE6-CB01-4007-93E9-C3D71689A280}”
“TransmitLocation_PageProv”=”{2DE93EE6-CB01-4007-93E9-C3D71689A282}”
“OutboundProtocol_PageProv”=”{2DE93EE6-CB01-4007-93E9-C3D71689A283}”
“OutboundEngineCLSID”=”{6811E17F-A4C2-44BB-B4D5-D69962E58E87}”
“InboundEngineCLSID”=”{538F8B3D-EE7B-4459-A123-45CD5C3B671B}”
“InboundTypeName”=”Microsoft.BizTalk.Adapter.Mqsc.MqscReceiver, Microsoft.BizTalk.Adapter.MQSC, Version=7.0.2300.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35, Custom=null”
“InboundAssemblyPath”=”"
“OutboundTypeName”=”Microsoft.BizTalk.Adapter.Mqsc.MqscTransmitter, Microsoft.BizTalk.Adapter.MQSC, Version=7.0.2300.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35, Custom=null”
“OutboundAssemblyPath”=”"
“PropertyNameSpace”=”http://schemas.microsoft.com/BizTalk/2003/mqs-properties”
“ProtocolAlias”=”mqsc://”
“ReceiveHandlerPropertiesXML”=”<CustomProps><AdapterConfig vt=\”8\”/></CustomProps>”
“SendHandlerPropertiesXML”=”<CustomProps><AdapterConfig vt=\”8\”/></CustomProps>”
“ReceiveLocationPropertiesXML”=”<CustomProps><AdapterConfig vt=\”8\”/></CustomProps>”
“SendLocationPropertiesXML”=”<CustomProps><AdapterConfig vt=\”8\”/></CustomProps>”
“AliasesXML”=”<AdapterAliasList><AdapterAlias>mqsc://</AdapterAlias></AdapterAliasList>”
“AdapterMgmtTypeName”=”Microsoft.BizTalk.Adapter.Mqsc.MqscAdapterManagement”
“AdapterMgmtAssemblyPath”=”C:\\Program Files\\Microsoft BizTalk Adapters for Host Systems 2.0\\system\\Microsoft.BizTalk.Adapter.MQSCAdmin.dll”

[HKEY_LOCAL_MACHINE\SOFTWARE\Classes\Wow6432Node\CLSID\{CF8B4D15-5D2B-4c62-90DB-16804D2454B9}\Implemented Categories]

[HKEY_LOCAL_MACHINE\SOFTWARE\Classes\Wow6432Node\CLSID\{CF8B4D15-5D2B-4c62-90DB-16804D2454B9}\Implemented Categories\{7F46FC3E-3C2C-405B-A47F-8D17942BA8F9}]
@=”"

- John Shaw