Geeks With Blogs
Edmund Zhao's BizTalk abc We share, We learn

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

Posted on Sunday, September 4, 2011 6:36 PM | Back to top


Comments on this post: Temp table in strongly typed stored procedure not supported by WCF SQL adapter

# re: Temp table in strongly typed stored procedure not supported by WCF SQL adapter
Requesting Gravatar...
thanks alot
Left by kris on Dec 12, 2011 1:56 PM

# re: Temp table in strongly typed stored procedure not supported by WCF SQL adapter
Requesting Gravatar...
Seriously.. nice work!

The idea of forcing applications (like VS) that are parsing the SP in FMTONLY ON mode to switch it off during the temp table creation and then back on for the rest of the query was the final link I needed. Leaving FTMONLY OFF was causing VS to crash due to the query result sets being too large
Left by Craig on Sep 26, 2012 3:22 AM

# re: Temp table in strongly typed stored procedure not supported by WCF SQL adapter
Requesting Gravatar...
This problem had me totally stumped.

Thanks so much for this!
Left by Tim OBrien on Sep 30, 2014 10:40 AM

# An extra error is coming up
Requesting Gravatar...
I did as suggested but now an extra error is coming up :
Could not find prepared statement with handle 0 error is coming up
Left by Shubham on Jul 28, 2015 8:59 AM

Your comment:
 (will show your gravatar)


Copyright © Edmund Zhao | Powered by: GeeksWithBlogs.net