The old SQL adapter has been thoroughly removed on BizTalk Server 2020. Fortunately it's possible for us to use WCF-SQL adapter's XmlPolling functionality to replace SQL adapter on receive locations without changing and deploying new schemas.
Let's use the SQL receive location in RosettaNet DoubleAction as an example.
Below is the old native SQL receive location:
To make WCF-SQL adapter retrieve the same xml document for you, its binding needs to be configured as the following:
A small issue here is that WCF-SQL XmlPolling may add an extra blank namespace to parent node(i.e: MessagesToLOB here) and results in schema validation error.
In this case, a simple solution is to modify your SQL query or stored procedure(e.g: PipAutomationGetAction in this sample) and add a default namespace to the output Xml's parent node (i.e: MessagesToLOB here):
ALTER PROCEDURE [dbo].[PipAutomationGetAction]
AS
BEGIN TRANSACTION
DECLARE @tempGUID nvarchar(36)
SELECT TOP 1 @tempGUID = MessageID FROM MessagesToLOB
WHERE Delivered = 0 AND MessageCategory = 10
ORDER BY TimeCreated ;
WITH XMLNAMESPACES (DEFAULT 'http://DoubleAction.CustomSchema')
SELECT PIPInstanceID,DestinationPartyName,SourcePartyName,PIPCode,PIPVersion,ServiceContent FROM MessagesToLOB
WHERE MessageID = @tempGUID
For xml auto
UPDATE MessagesToLOB SET Delivered = 1 WHERE MessageID = @tempGUID
COMMIT TRANSACTION