If your orchestration is heavily rely on WCF SQL adapter based on SQL stored procedures, you may often encounter an issue related to strongly-typed schema and receive an error which is similar to the following.
The adapter failed to transmit message going to send port "WcfSendPort" with URL "mssql://sqlserver//database?". It will be retransmitted after the retry interval specified for this Send Port. Details:"System.Data.SqlClient.SqlException: Invalid object name '#Temp'.
This is because of the limitation of BizTalk Adapter Pack 2.0, it doesn't support temp table in stored procedure if you need to generate typed-procedure schema. You can use table variable to replace temp table, but it won't work if you use dynamic SQL, or the table is not small. But don't worry, there is a easy workaround, just make sure your stored procedure contain the following contents.
--Ensure FMTONLY OFF just before creating temporary table
DECLARE @CheckFmt bit;
SELECT @CheckFmt = 0 WHERE 1=1
IF @CheckFmt IS NULL
SET FMTONLY OFF;
--Create temporary table
IF object_id('tempdb.dbo.#Temp') IS NOT NULL
DROP TABLE #Temp;
Create table #Temp(
...............
)
--Ensure FMTONLY is turned back on if it was on before
IF @CheckFmt IS NULL
SET FMTONLY ON;
--Feel free to do anything on the temp table here
--Make sure you drop it at the end of your stored procedure
IF object_id('tempdb.dbo.#Temp') IS NOT NULL
DROP TABLE #Temp;
If you want to know more details about this limitation as well as some other interesting stuff about WCF SQL adapter, a very good article here is recommanded to you for reading.
...Edmund