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.

BAM Portal Activity Search: "This action cannot be performed because one or more database(s) appears corrupted"

Hi,

I noticed this error when I tried to search on an Activity in the BAM Portal.

I googled and noticed a good description about this issue by Gilles but it did not help me resolve the problem.

http://blogs.msdn.com/b/gzunino/archive/2006/07/12/663790.aspx

So I thought of fixing it in the BAM (BAMPrimaryImport)database as it clearly said about duplicate entry. I searched on the ActivityID and found only 1 record in "bam_ActivityName_active" table and I also found 1 record in the "bam_ActivityName_completed" Activity table.
SELECT * FROM [BAMPrimaryImport].dbo.bam_ActivityName_active where ActivityID='{42B99A00-4A43-40D4-8F6F-183851984197}'

So I thought that its not a duplicate issue as the Error says as I saw only 1 entry in each tables.

But I was wrong.

The issue is that there should not be any pending records in the Active tables if  the Activity is completed ie. if there exist a record in _Completed table for the same ActivityID.

In my case I intiated an Activity(BeginActivity) and Biztalk failed while updating the Actvity(UpdateActivity)and after that I completed the Activity(EndActivity). So there was an Orphan record in the _active table.

So I deleted all the records from _active table and it worked fine.

DELETE FROM [BAMPrimaryImport].dbo.bam_ActivityName_active

So this is what BizTalk means by Duplicate records.

"There should not be a record in the _active table if the activity has a record in the _completed table for the same ActivityID.If it exist then its a duplicate Error."

Error in Eventlog was as follows:
Event Type: Error
Event Source: BAM Portal
Description:
(BAMPortal.PortalApplication) Void LogAllErrors(System.Exception[]): System.Web.HttpUnhandledException: Exception of type 'System.Web.HttpUnhandledException' was thrown. ---> System.Web.Services.Protocols.SoapException: System.Web.Services.Protocols.SoapException: There are instances with duplicate ID '{42B99A00-4A43-40D4-8F6F-183851984197}' in activity 'xxx'. The duplicates must be removed from the database to fix this problem.
   at Microsoft.BizTalk.Bam.WebServices.Query.BamQueryService.GetInstanceData(String viewName, String activityName, InstanceQuery query, Int32 timeout)
   at System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(SoapClientMessage message, WebResponse response, Stream responseStream, Boolean asyncCall)
   at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters)
   at Microsoft.BizTalk.Bam.WebServices.QueryService.BamQueryService.GetInstanceData(String viewName, String activityName, InstanceQuery query, Int32 timeout)
   at Microsoft.BizTalk.Bam.Portal.DataAccess.QueryResultsObjectDataSource.ExecuteQuery(SQLQuery query)
   at Microsoft.BizTalk.Bam.Portal.DataAccess.QueryResultsObjectDataSource.EnsureData(SQLQuery query)
   at Microsoft.BizTalk.Bam.Portal.DataAccess.QueryResultsObjectDataSource.QueryResults(SQLQuery query)
   at BAMPortal.Search_aspx.ExecuteQuery()
   at BAMPortal.Search_aspx.executeQueryButton_Click(Object sender, CommandEventArgs e)
   at System.Web.UI.WebControls.Button.OnCommand(CommandEventArgs e)
   at System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument)
   at System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument)
   at System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument)
   at System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData)
   at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
   --- End of inner exception stack trace ---
   at System.Web.UI.Page.HandleError(Exception e)
   at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
   at System.Web.UI.Page.ProcessRequest(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
   at System.Web.UI.Page.ProcessRequest()
   at System.Web.UI.Page.ProcessRequestWithNoAssert(HttpContext context)
   at System.Web.UI.Page.ProcessRequest(HttpContext context)
   at ASP.pages_search_aspx.ProcessRequest(HttpContext context)
   at System.Web.HttpApplication.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute()
   at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously)


Happy Talking Biz. ;)