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:

If we call this procedure, it works as expected:

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

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:

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:

Hopefully that will keep Biztalk and other applications happy.

2017-04-11