BizTalk 2016 64 Bit and SQL Adapter

Intro

In this post I'll tell you about our experiences using BizTalk 2016 64 bit and the standard SQL adapter together. It's not exactly rocket science but hopefully it may save a few people a few minutes.

So, here's the environment:

BizTalk:

Windows Server 2016 64 bit running BizTalk Server Enterprise 2016 64 bit with Feature Pack 2

BizTalk SQL Server:

Windows Server 2016 64 bit running SQL Server 2016 Enterprise

BizTalk Host Instances:

One 64 bit In-Process host

Scenario:

We are using the SQL adapter packaged with BizTalk Server to receive data from our SQL server using a stored procedure. When executing the query we receive the following error:

The receive location "Receive Blah" with URL "SQL://blahserver/blahdb/blahquery" is shutting down. Details:"The receive adapter on this receive location is not supported on 64-bit host instances. Please make sure that the receive adapter is associated with a 32-bit only handler. ".

Fix:

This is one of the few occassions when BizTalk gives you an exception which is short, explicit and gives you all the information you need. Simply create an extra host in BizTalk Administration Console which has the 32 bit checkbox checked. Add the host instance to go with it. Then go into the Adapers section and look at the properties of the SQL adapter. Change the default host to your new 32 bit host and then restart both host instances. Your adapter will now work perfectly.