Avoiding invalid object name errors with temporary tables for BizTalk, Reporting Services and apps using SET FMTONLY

When applications need to call stored procedures, they try to work out what the returned data will look like ie: which columns come back, what data types are they, etc.

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. Let’s look at an example. We’ll start with a stored procedure that just grabs some trivial data and returns it, but puts in into a temporary table first:

image

If we call this procedure, it works as expected:

image

If, however, we try to use SET FMTONLY ON, it fails miserably:

image

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.

This is a common problem with configuring Biztalk to work with SQL Server T-SQL stored procedures. There are other ways of manually configuring Biztalk to avoid this but everyone using it just seems to complain that it doesn’t work with stored procedures, or at least not with those that contain temporary tables. One solution is to use table variables instead. SET FMTONLY is happy with those.

Another option is to try to fool Biztalk (or other application). Consider this procedure instead:

image

We declare some code that will only run at run time, and manually manipulate the SET FMTONLY state around the creation of the temp table. Then the outcome is as expected:

image

Hopefully that will keep Biztalk and other applications happy.

One thought on “Avoiding invalid object name errors with temporary tables for BizTalk, Reporting Services and apps using SET FMTONLY”

  1. Nice article as always Greg. For some context, and  to give the punters an idea of the high esteem (not) in which SET FMTONLY is now held, even at Microsoft, BOL says:
    "…Note
    Do not use this feature. This feature has been replaced by sp_describe_first_result_set (Transact-SQL), sp_describe_undeclared_parameters (Transact-SQL), sys.dm_exec_describe_first_result_set (Transact-SQL), and sys.dm_exec_describe_first_result_set_for_object (Transact-SQL)…." (https://docs.microsoft.com/en-us/sql/t-sql/statements/set-fmtonly-transact-sql)
    I’m amused that they still describe it as a "feature"; I yearn for the day that it is finally banished from the SQL Server ecosystem forever. 🙂
    Thanks again.

Leave a Reply

Your email address will not be published. Required fields are marked *