SQL Connector – invalid object name ‘#temp’ when reading input/output properties

How Can We Help?

< Back

When attempting to use the SQL Connector in the XMDesigner may see the following (or similar) error message: invalid object name #temp.

This error will occur in a very specific scenario:

You are attempting to map the SQL Connector to a stored procedure and the stored procedure in question makes use of a temp table (or tables)

This error is caused by a limitation in SQL server, but can thankfully be easily avoided, in one of two ways:

Table Variable

The ideal solution when you encounter this issue is to make use of table variables (@temp) in your SQL Stored Procedure, and not temp tables (#temp).

Disable FMTONLY

Temp tables perform significantly faster than table variables, especially when processing large volumes of data. If the use of a table variable is not appropriate (possibly due to performance reasons), add the following statement to the start of your SQL Stored Procedure, before the temp table is addressed or referenced:

if 1=0
SET FMTONLY OFF

If you do use this approach to address your issue, please ensure that your stored procedure will execute successfully when all input parameters are passed a null value. You can verify this directly in SQL Server Management Studio.

Comments are closed.

This is the legacy version of the XMPro Documentation site. For the latest XMPro documentation, please visit documentation.xmpro.com

X