It is pretty common to use temporary tables to perform some internal logic inside your SQL queries or scripts. I have seen several times being used, for example, inside Stored Procedures. The problem is that when BizTalk Server needs to call stored procedures, it needs to import the stored procedure XML Schema, and in this process, they try to work out what the returned data will look like, i.e., which columns come back, what data types they are, and so on.
That means if you use a temporary table inside your SQL Script like this:
SELECT * | Column1,Column2
INTO #TempDestinationTable
FROM Source_Table
WHERE Condition
You will probably an error like this one I recently received:
Error while retrieving or generating the WSDL. Adapter message: Retrieval of Operation Metadata has failed while building WSDL at ‘TypedProcedure/dbo/<stored-procedure-name>’
And if you click on Details to get more info, you will see:
Microsoft.ServiceModel.Channels.Common.MetadataException: Retrieval of Operation Metadata has failed while building WSDL at ‘TypedProcedure/dbo/<stored-procedure-name>’ —> System.Data.SqlClient.SqlException: Invalid object name ‘#temp-table’.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
Cause
As I mentioned earlier, when BizTalk Server needs to call stored procedures, it needs to import the stored procedure XML Schema, and in this process, they try to work out what the returned data will look like, i.e., which columns come back, what data types they are, and so on. And, as Greg Low mentioned in his blog post:
“The old way of doing this was to call SET FMTONLY ON. Unfortunately, many applications like Biztalk and versions of Reporting Services still did this, and/or still do this. The same issue happens with LINQ to SQL and other tools that use SQL Metal.
Instead of finding out what’s needed, they fail with an “Invalid object name” error when temporary objects are present.
The problem is that SET FMTONLY causes SQL Server to just return an empty rowset for each statement like a SELECT that it encounters (without executing it), and it has no idea what #SomeComments is until it’s actually executed.“
By the way, this happens if you are using strongly typed schemas. If you see Thiago Almeida’s blog post about the differences between the old and the “new” SQL Adapter:
“One of the common issues developers run into after migrating schemas for stored procedures from the old SQL Adapter to the new is lack of support for generating strongly-typed schemas for stored procedures that create temporary tables. This is one limitation of the new WCF-SQL Adapter that didn’t exist in the old so let us delve into this a little bit more and cover one workaround.
Strongly-typed schemas are the most common way of receiving data from SQL Server into BizTalk solutions. The reason why the adapter fails when there are temporary tables in the stored procedure is because it uses the FMTONLY setting of SQL Server to discover the format of the stored procedure response without actually calling it.“
Solution
There may be other ways to solve this problem, and I’m going to research about it. But one option, as Greg mentions in his blog post, is to “fool” BizTalk regarding those temporary tables by implementing the following workaround:
CREATE PROCEDURE [dbo].[usp_DeleteProducts]
@DeleteProducts DeleteProductTable readonly
AS
BEGIN
DECLARE @NeedToKeepBizTalkHappy bit;
SELECT @NeedToKeepBizTalkHappy = 1 WHERE 1 = 1;
SET NOCOUNT ON;
IF @NeedToKeepBizTalkHappy IS NULL SET FMTONLY OFF;
CREATE TABLE #deleteAddresses (
AddressID INT
);
IF @NeedToKeepBizTalkHappy IS NULL SET FMTONLY ON;
SELECT ....
INSERT #deleteAddresses
SELECT AddressID
FROM dbo.MyTable
WHERE ProductID IN (...);
...
DROP TABLE #deleteAddresses
END
Hope you find this helpful! So, if you liked the content or found it useful and want to help me write more, you can buy (or help me buy) my son a Star Wars Lego!
