One Blog for Biztalk Engineers

Duplicate key row in object 'dbo.bts_LogShippingJobs'

Randy Ridgely [MSFT] — Thu, 16 May 2013 18:10:43 GMT

A customer was setting up Log Shipping disaster recovery for their BizTalk Server 2006 R2 environment. http://technet.microsoft.com/en-us/library/aa560961(v=bts.20).aspx They had gotten to the step where they run the stored procedure bts_ConfigureBizTalkLogShipping. They received the error "Msg 2601, Level 14, State 1, Procedure bts_ImportSQLAgentJobs, Line 56 Cannot insert duplicate key row in object 'dbo.bts_LogShippingJobs' with unique index 'CIX_LogShippingJobs'."

It turns out that this customer also had several of their own SQL Agent jobs running on the BizTalk server. As part of configuring the destination environment, we attempt to recover all of the jobs running on the BizTalk server with one exception: we don’t support importing of SQL Agent jobs where the steps use more than one database. The script's logic iterates over each database to be recovered and logs the jobs associated with that database in bts_LogShippingJobs for later recovery. If a job has more than one database association, the script attempts to log it twice. But we never want to recover the same job more than once, so bts_LogShippingJobs doesn't allow duplicate jobs. When the script attempts to log the job the second time, it fails.

In general, we discourage running any jobs on the SQL Server that supports BizTalk Server other than the jobs that ship with the product.

For those customers who choose to run their own jobs and encounter this issue, the solution is to temporarily remove any job(s) associated with multiple databases while setting up the destination recovery environment. You will also need to develop your own recovery plan for any such job(s). Once the destination environment is configured, you can restore the jobs. The following T-SQL will identify jobs associated with multiple databases:

SELECT j.name, COUNT(DISTINCT js.database_name) AS dbcount INTO #tmp FROM msdb.dbo.sysjobsteps js JOIN msdb.dbo.sysjobs j WITH (NOLOCK) ON j.job_id = js.job_id GROUP BY j.name SELECT * FROM #tmp WHERE dbcount > 1 DROP TABLE #tmp

BizTalk EDI Leap Year Fix FAQ

ShaheerA — Sat, 25 Feb 2012 05:28:00 GMT

As you may have heard, there is an issue in the BizTalk 2006 R2 and 2009 EDI Engines that results in a failed EDI transaction if the EDI message contains a date node that has a leap year value - for example 2/29/2012. This issue and hotfix is documented at http://support.microsoft.com/kb/2435900. With 2/29/2012 just days away, this is a big deal and you will want to address this issue in your environment ASAP.

You can also get some great information about this issue at http://blogs.msdn.com/b/biztalkcrt/archive/2012/02/22/edi-leap-year-hotfix-biztalk-2009-and-biztalk-2006-r2.aspx.

I've also compiled a list of questions that have been asked about this issue over the last week and created a detailed FAQ document about this. Check it out below.

Also, to download the pipeline sample mentioned in the FAQ, click here.

(FAQ last updated on 02/29/2012 at 12:22PM Central Time)

Creating partitioned View in the BAM Archiving Database

Mike Shea1 — Mon, 05 Dec 2011 21:33:30 GMT

Carl Nolan of the UK Solution Development Team wrote an excellent blog post on the above topic. He includes a script for creating partitioned views that can be used if modifications to the existing activity items are continuing to be made.

http://blogs.msdn.com/b/mcsuksoldev/archive/2011/11/15/creating-a-partitioned-view-in-the-bam-archiving-database.aspx

When tuning messaging engine threads per cpu lower from defaults can help performance

Mike Shea1 — Mon, 31 Oct 2011 14:13:21 GMT

Say you have a large flatfile in an application that does messaging only. You drop a single file and you notice elevated cpu for the receive host for several minutes. What’s happening in the host is that a single messaging thread is using the ffdasm to disassemble the flat file. This is a very cpu intensive activity

Now you drop 100 large flatfiles. Since messaging engine threads per cpu is set to 20, if you have a two processors, then there will be 40 messaging engine threads created and operating on 40 inbound flatfiles. cpu still goes to 100%, but it takes longer for the first flatfile to be processed because other threads are taking valuable cpu resources.

In testing on a two processor system I was able to get the best end to end performance by specifying that only one messaging engine thread per cpu be used on the receive host when performance testing with 100 files. Tuning messaging engine threads per cpu to a lower value results in faster processing of the first file through the system and additionally faster overall processing.

1 messaging engine thread per cpu on the receive host about 4 minutes to process the first file. 1 hour and 30 minutes for all files

10/21/2011 02:46 PM set messaging engine threads to 1 for full test.txt

10/21/2011 02:50 PM 2,752,368 {FC0AD608-1F76-43A2-B7D5-44DDB66E66DF}.xml

10/21/2011 04:16 PM 2,752,368 {F7241E83-C7F1-428D-A5DD-2582CF8ABE20}.xml

2 messaging engine threads per cpu on the receive host about 4 minutes to process the first file. 1 hour 42 minutes to finish all approx..

10/19/2011 04:29 PM set messaging engine threads to 2 for full test.txt

10/19/2011 04:33 PM 2,752,368 {C29DB82A-98E8-4B41-B917-093DDC708D21}.xml

10/19/2011 05:02 PM 2,752,368 {E9AFFEC2-B60B-446C-9D63-1BBBB2B2FD12}.xml 33^rd file in 34 minutes. Had to stop test early summary

extrapolated.

4 messaging engine threads per cpu on the receive host 10 minutes time to first message and 1 hour and 39 minutes total processing time.

10/19/2011 06:06 PM set messaging engine threads to 4 for full test.txt

10/19/2011 06:16 PM 2,752,368 {E96CD77C-8D24-4BB9-A111-871594B06F3F}.xml

10/19/2011 07:45 PM 2,752,368 {2F2EE1F9-C9F0-44A9-B2D1-1A02BC9F7EAD}.xml

20 messaging engine threads per cpu on the receive host (the default setting) 18 minutes. 1 hour and 42 minutes for all files to be processed.

10/20/2011 09:35 AM set messaging engine threads to 20 for full test.txt

10/20/2011 09:53 AM 2,752,368 {9BA6A42F-A634-4FEF-A29E-F16A774D823F}.xml

10/20/2011 11:17 AM 2,752,368 {262F3035-CACF-423A-BDFA-5EA817D2639B}.xml

50 messaging engine threads per cpu on the receive host 23 minutes before first file is processed. 1 hour 54 minutes for all files to be processed.

10/20/2011 02:56 PM set messaging engine threads to 50 for full test.txt

10/20/2011 03:19 PM 2,752,368 {40BF75AF-3935-4DC1-99C4-048CD61421D1}.xml

10/20/2011 04:50 PM 2,752,368 {5B72CC56-FB61-4A33-9186-3A3F954AC38A}.xml

BizTalk Terminator Not Cleaning Up Caching Items?

ShaheerA — Wed, 24 Aug 2011 20:06:00 GMT

I've been pinged a number of times on this so thought I should blog the workaround and an explanation.

First, let's say MBV shows you something like the following in the Warning and Summary Report:

Or you just notice in MBV that there's a bunch of cache messages in one of the queue tables:

Well, according to my Using BizTalk Terminator to Resolve Issues article, you simply run the Terminate Caching Instances task:

Issue Identified by MBV

Resolution Options

Terminator Resolution Task

Terminator View Task

Root Cause

Orphaned Cache Instances

MBV Integration or Manual Task Selection

Terminate Caching Instances

(in Delete task category)

View Count of Cache Messages in All Host Queues

View Count of Cache Instances in All Hosts

This is due to a known bug and there is a hotfix available. See KBs 944426 & 936536 for details.

That should do it. If it doesn't, make sure you have stopped all the BizTalk hosts (that includes the IIS app pool hosting the BizTalk isolated host if the caching items are there) and try again. (Hey, you shouldn't be running Terminator without stopping all the BTS hosts anyway).

Now, that will definitely do it.

Well... like 99.9% of the time.

Let's say you do all of the above and then run one of those View tasks (or MBV) and notice that Terminator left behind some of those caching instances (and their associated caching messages). This happens even though Terminator claims to have terminated all of them successfully. And rerunning the task doesn't help - Terminator will repeatedly say it successfully terminated those instances but either of the View tasks will show that they're still there.

Ok, so now you're most likely running into a very rare scenario that I've come across a few times.

First, I should point out that the Terminate Caching Instances and Terminate Instances tasks use BizTalk's WMI API to interact with the messagebox - and that's key. I had the opportunity to analyze some data from a customer environment that was running into this issue and it turns out that there are certain times when msgbox logic prevents the stored procs called by WMI from terminating “internal” instances – with caching being considered one of those "internal" types. As far as when exactly the msgbox logic goes down this "rare" path, I've never had access to a repro environment where I could fully debug this so I don't have a good answer for that.

So I was going to write code in Terminator's WMI class to catch this scenario and warn the user that they need to use the workaround to clean up the remaining items but unfortunately the msgbox logic catches the failed call and doesn’t pass that info on to the WMI caller – so there’s no way for Terminator (or any WMI client) to know that some of the instances weren’t deleted. As far as the WMI client is concerned, the stored proc call completed sucessfully so it assumes the instances it asked to be Terminated were actually terminated.

So what's the workaround? Well, don't use WMI for this particular situation. Instead, use Terminator's Hard Termination tasks (Terminate Multiple Instances (Hard Termination) or Terminate Single Instance (Hard Termination).

While I was working on Terminator's WMI class and having BizTalk engineers use Terminator on an internal-only basis within Microsoft, we noticed that on very rare occasions the termination tasks would not terminate something. This would not be a limitation of Terminator and we could reproduce it with any WMI client (including the BTS Admin console). I created the hard termination tasks specifically to handle those scenarios. They bypass BizTalk's normal termination API and use SQL calls to directly interact with the BizTalk msgbox. They can terminate anything (well, so far) - even internal instances. Originally, we just had the Terminate Single Instance (Hard Termination) task to help terminate a one-off instance that just wouldn't terminate any other way. That worked great for those one-off scenarios but I soon realized that sometimes there would be a larger number of instances that needed hard termination. I left the single instance task to give users that functionality and wrote the Terminate Multiple Instances (Hard Termination) task. That allows the user to choose the Host, Class, Status, and the Max number of instances to terminate and will do hard terminates on all items that fit the filter criteria.

The only thing that is painful about the Terminate Multiple Instances (Hard Termination) task is that if you have instances across multiple hosts with various statuses, you will need to run the task for each permutation since it doesn't have the ability to handle, in one execution, multiple hosts and statuses (or classes) like the WMI-based Terminate Instances and Terminate Caching Instances tasks. Since Terminator v2 supports Powershell, I'm hoping at some point to create a powershell-based hard terminate task that can provide this functionality - just haven't had the time to do that yet.

So, in short:

Problem:

The Terminate Caching Instances task is not cleaning up all cache items.

Solution:

Use the Terminate Multiple Instances (Hard Termination) task, choose Caching as the Class Parameter.

You will need to re-run this task for each Host and Status that applies to the cache items you're trying to terminate - MBV or the Terminator View Count of Cache tasks should give you some info in this regard.

BTW, I've seen active, dehydrated, and suspended cache items so you may need to cycle through all of those Statuses.

In general, if you ever find that any of the termination tasks are not terminating what you want, the two Hard Termination tasks (Single and Multiple) are the workaround.

Remember that Hard Termination tasks bypass the normal BTS APIs so should only be used if the normal Terminate Instances or Terminate Caching Instances task is not working - and as always, be careful with Terminator - especially when doing any of the deletion tasks.

MQSC options may not be available in Biztalk Administration console

Dwaine D Gilmer — Tue, 28 Jun 2011 18:18:00 GMT

Recently, we ran into some issues in which the MQSC adapter was not displayed as an option in the BizTalk Administration console, even after the adapter was properly installed on a BizTalk 2009 / Windows 2008 SP1 server. Before outlining the problem and the solution, let’s discuss the MQSC adapter basics first.

MQSC Adapter Basics

The MQSC adapter is only supported running in 32-bit mode on BizTalk 2006 R2, regardless of the operating system being used. On BizTalk 2009 and 2010, the MQSC adapter is supported on Windows 32-bit and Windows 64-bit configurations. In BizTalk 2009, the ability to run in a 64-bit host depends on the version of the IBM Websphere client installed, specifically MQ Client (7.x) or greater. If the goal is to run 64-bit hosts then the 64-bit version of the IBM Websphere client must be installed onto a supported Windows operating system running on a 64-bit processor. For
more information, please follow the installation documentation for the IBM Websphere client and/or the installation documentation for the version of Biztalk being installed.

939202 Update

This update forces the MQSC adapter to run in 32-bit hosts in BizTalk 2006 R2 regardless if the operating system is 32-bit or 64-bit. When 939202 is installed onto any other version of BizTalk (except BizTalk 2006 R2), you may get the “Exception: is not a valid Win32 application. (Exception from HRESULT: 0x800700C1)” exception when running the adapter in 64-bit hosts. Uninstall the 939202 update. If a problems persists, call Microsoft customer support.

MQSC Adapter WMI Installation May Fail

When the MQSC adapter is installed onto BizTalk 2009 / Windows 2008 SP1 (or later SP) or BizTalk 2010 / Windows 2008 R2, it is possible that the WMI install will fail to write the proper registry entries when loading the “Microsoft.BizTalk.Adapter.MQSCAdmin.dll”. The BizTalk MMC console runs as 32-bit MMC.exe process. When the MQSC adapter installation fails, it does not write the correct information to the registry keys used by the MQSC adapter.

To resolve this, add the “MQSC Adapter Class” class information manually into the Wow6432Node node in the registry and then do a repair of the MQSC adapter (again, it is part of the HIS installation). The outline for correcting this problem is as follows:

Install MQ 7.x or greater and all of the appropriate updates (64-bit version).
Install BizTalk Host Adapters 2.0 from the HIS 2009/2010 Server media onto the target BizTalk 2009/2010 server.
Copy the registry information below into a registry file (.reg file) and import it into the HKEY_LOCAL_MACHINE\SOFTWARE\Classes\Wow6432Node\CLSID key.
Run the repair for the BizTalk Host Adapters 2.0 from the HIS 2009/2010 Server media (do not skip this step).
The MQSC adapter should show up in the BizTalk Administration console.

NOTE: Please refer to the appropriate HIS and IBM Websphere client documentation for the most current installation procedures.

REGISTRY ENTRY

"Using Registry Editor incorrectly can cause serious, system-wide problems that may require you to re-install Windows to correct them. Microsoft cannot guarantee that any problems resulting from the use of Registry Editor can be solved. Use this tool at your own risk."

REGISTRY ENTRY

=============

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 (x86)\\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\{80D39129-B68D-4f85-98EF-

151A4E1627F7}]

@=""

NOTE: Confirm that the "AdapterMgmtAssemblyPath" is proper for your installation.

BTARN App Pool Settings for BizTalk 2010

LemMotlo — Sat, 05 Mar 2011 14:58:13 GMT

Here is a some additional information regarding extending BTARN 2010 with new PIP’s. The artifacts generated are going to be .Net 4.0 unless Visual Studio is configured to do otherwise. This means along with setting the application pools for the 32-bit the .Net setting must be set to match.

The error will happen after receiving a document using the new schema. Here is a sample error.

Event Type:    Error
Event Source:    BizTalk Accelerator for RosettaNet
Event Category:    None
Event ID:    4096
Date:        3/3/2011
Time:        4:28:03 PM
User:        N/A
Computer:    BASE08R2-1.northamerica.corp.microsoft.com
Description:
Source module:
RNDisAssembler

Correlation information:

Description:

Receive pipeline rejected incoming message
due to the following RNIF exception:
UNP.SCON.VALERR : A failure occurred while validating the service content.

Details:
The document specification <MyCustomPIP._2A1_MS_V02_00_ProductCatalogInformationNotification> from assembly <MyCustomPIP, Version=1.0.0.0, Culture=neutral, PublicKeyToken=279ae20de2fddcaf> failed to load. Verify the schema for this document specification is deployed and is in the Global Assembly Cache.

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

BTARN and TLS Protocol

LemMotlo — Fri, 10 Sep 2010 13:20:46 GMT

As part of a blog entry back in April I mentioned a problem some folks were having with RNIFSend pages. Here’s the blog link (check the bottom of the post).

http://blogs.msdn.com/b/biztalkcpr/archive/2009/09/04/troubleshooting-btarn-web-pages-and-more-on-btarn-x64.aspx

At the time I could not confirm the problem can happen with Windows 03 (x32 and x64). The issue has been confirmed with the latest service packs/security updates for Windows 03 SP2. The send page will attempt to use TLS first (not SSL 3.0). Also, immediately after the “Client Hello” in the network trace the conversation shows “Encrypted Alert” and the communication ends.

If web page tracing has been added it will show “Underlying connection was closed”. The fix is the same. Add a line of code to the RNIFSend page to force the use of SSL 3.0.

ServicePointManager.SecurityProtocol = SecurityProtocolType.Ssl3; (add this)

//rest of the code

HttpWebRequest outerRequest = null;

ErrorLevel level = ErrorLevel.ProxyToOuterFailure;

The location is not critical. Keeping it close to the other connection related code is a good idea. Copy the existing virtual folders as a backup. Deploy the new send page and test.

BTARN BizTalk 06 R2 SP1

LemMotlo — Fri, 13 Aug 2010 19:13:29 GMT

Looks like some slipped through the cracks on SP1 upgrades with BTARN installed. If the process hangs with the command box open check the setup log. It may look like this.

. . .

[08/13/10 11:42:12] Package Microsoft BizTalk Accelerator for RosettaNet 3.5 is installed.

[08/13/10 11:42:12] Package code is {394EFEF0-99D2-4DBA-9446-0664E62ACEAF}.

[08/13/10 11:42:12] Performing post-installation custom actions. 1 actions to perform.

[08/13/10 11:42:12] Starting process "C:\Documents and Settings\All Users\Application Data\Microsoft\E-Business Servers Updates\Updates\UninstallSP1\Install.exe" ...

The problem is “xcopy” is trying to copy a file and it already exists. This causes a hang condition. The fix is to let the install continue by killing the “xcopy” process in task manager. This should let the install finish. If the process remains in the hung condition after a minute or so kill the “install.exe” process and un-install the SP.

If you have to re-install avoid the issue by deleting the file “C:\WINDOWS\system32\AcceleratorsInterop.dll “. Then run the SP again. Be sure to read the install document with the SP. It has several required manual steps like un-enlisting the BTARN orchestrations.

Proxy Peckin’ Order

LemMotlo — Thu, 22 Jul 2010 12:06:22 GMT

Proxy configuration for BizTalk has always been fairly straight forward. Go to the send point and click the “Configure” button then select the “Proxy” tab. Couldn’t be much easier.

A recent case pointed out what happens when the “Do not use proxy” option is selected. It turns out there is a hierarchy to the proxy assignment.

1	Send Port
2	BizTalk App Config
3	Machine Config
4	local IE user setting

Selecting “Do not use proxy” from the send port does not mean no proxy will be assigned. Some messages were getting a proxy assignment and other were not. This can be caused by logging into the BizTalk box using the account running the BizTalk host. The local box assigns the IE proxy settings to this user. Now one BizTalk box could be different than the others. Not only will the proxy settings be picked up, but also the IE “Automatically detect settings” configuration is also picked up. This can adversely impact BizTalk processing.

The solution is simple. Don’t leave proxy settings to default. Set the BizTalk app config to the appropriate settings. Here are a couple of links to deal with these issues.

http://support.microsoft.com/default.aspx?scid=kb;EN-US;968699 auto detect issue

http://msdn.microsoft.com/en-us/library/kd3cf2ex.aspx Configuring the default proxy settings