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

When attempting to use the SQL Connector in the XMDesignerIs used to configure XMPro BPM solutions. 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.