Share via

BizTalk Server: WCF-SQL Adapter - Polling SQL Stored Procedure containing Temp Table

  • Article

This Wiki is based on Forum Thread: BizTalk receive location which hits the SQL stored procedure

Problem

Have a BizTalk Receive Location which calls a SQL stored procedure using WCF-SQL Adapter. The stored procedure it calls has the creation of a temp table and inserting data in it. When we enable the Receive Location it doesn't get enabled and throws an error “invalid object name”.

Root Cause

A temp table created within the stored procedure is invisible to BizTalk, that is why it considers it an invalid object.

Solution

Create a table type variable like the below example and then use it as temp table in a stored procedure.

DECLARE @MyTempTable TABLE
(       
       Field1 varchar(10)
      ,Field2 varchar(10)
      ,Field3 varchar(10)        
)

See Also

Another important place to find an extensive amount of BizTalk related articles is the TechNet Wiki itself. The best entry point is BizTalk Server Resources on the TechNet Wiki.