Wednesday, May 11, 2011

SQL Receive Adapter: Multiple tables in different Database

Last week I had a simple scenario where BizTalk Server will use a SQL Receive Adapter and poll a SQL Stored Procedure(SP) which will return the rows from multiple tables spread across 2 different database.
Initially I thought the only catch out here will be to grant rights across all tables for the same User Account with which Biztalk polls the SP. Following were the last 2 lines of my proc.
SET @ReturnXML=SELECT  * FROM ABC INNER JOIN DB2.dbo.XYZ as XYZ ON ABC.FIELD1=XYZ.FILED2 WHERE ABC.UpdateID=@UpdateID FOR XML AUTO,ELEMENTS
Select @ReturnXML
The SP worked perfect from SQL Query window but I started getting the following error when called by BizTalk SQL Receive Adapter.
Event ID: 5740
Description:The adapter "SQL" raised an error message. Details "HRESULT="0x      7a" Description="Description not provided"

I thought I am missing some access rights on the 'XYZ' table, I granted all possible rights for the user on the table but still the same issue. After struggling for 2 days I thought to concentrate only on what the SP returned in the last 2 lines shown above.I felt that BizTalk is having an issue with the data returned from SP as it wont care what you do in the SP.BizTalk is only interested in the results(rows) returned,So I removed the variable(@ReturnXML) and it worked. So SP now returns the SQL Query directly.
SELECT  * FROM ABC INNER JOIN DB2.dbo.XYZ as XYZ ON ABC.FIELD1=XYZ.FILED2 WHERE ABC.UpdateID=@UpdateID FOR XML AUTO,ELEMENTS

Hope it helps you.

No comments:

Post a Comment