Tallan's Technology Blog

Tallan's Top Technologists Share Their Thoughts on Today's Technology Challenges

Changing the Error Threshold for the SQL Adapter

Recently, a client of ours was running into a problem with our SQL Receive Locations being automatically disabled by BizTalk 2006. This happened whenever the IT admins took down the SQL 2000 servers and/or the network for maintenance.

Some background: BizTalk 2K6 is used in an integration solution that requires querying a SQL 2K database for new records. Due to requirements of the system, the data needs to be pulled every 15 seconds.

The problem is when BizTalk receives 5 errors in a row trying to retrieve data from the SQL data source – when this happens, BizTalk will disable the receive location and generate an error in the event log.

Since we poll the SQL server every 15 seconds, this results in the receive location being disabled anytime the server or network is down for over a minute. And since the client hasn’t implemented event log monitoring, an entire weekend would go by before anyone would notice that the data was piling up in at the source.

I did some research and eventually found where BizTalk stored the setting for the error threshold. The following describes how to get to the setting in BTS 06.

1. Open the Admin console and drill-down to BizTalk Group > Platform Settings > Adapters > SQL

2. Select the HostName in the MMC Console that corresponds to your receive locatio (the direction should say “Receive”.) Right-click the Host (and not the SQL tree-view node) and select Properties

SQL Adapter Hosts

3. On the following dialog box, select the Properties button.

SQL Adapter Handler Properties

4. Change the setting for the Error Threshold property. Click OK/Apply, restart the respective BizTalk host instances and you are done.

SQL Transport Properties

Note: This setting is global to the BizTalk host you selected in step 2.

2 Comments. Leave new

Another way to solve this particular problem would be to disable all of the receive locations and re-enable them. Painful right? I’m not sure if this is supported by MS, but if you have access to the Configuration Database, you can update the Receive Location table directly: UPDATE BizTalkMgmtDb.dbo.adm_ReceiveLocation SET Disabled = -1|0

hi, where to configure this taht we are getting data from every 15 seconds as you wrote in your post , please tell.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>