Number: TB0031

Availability: WINDOWS as of 8.02.01

Introduction

A problem has been identified where MSSQL Local Temporary Tables (#temptable) are only accessible for single SQL commands within BuildProfessional. Subsequent commands display an "invalid object name" error.

Background

Temporary tables are automatically dropped when they go out of scope, which is occurring here. By utilising stored procedures, you can ensure that the temporary table is accessible for all statements within that procedure. A local temporary table created in a stored procedure is dropped automatically when the stored procedure completes. The table can be referenced by any nested stored procedures executed by the stored procedure that created the table . The table cannot be referenced by the process which called the stored procedure that created the table.

Example of Temporary Table Utilisation within BuildProfessional

Create a procedure (proc1) from MSSQL Query Analyser:

CREATE PROCEDURE proc1
AS
SELECT field5 into #temptest1 from table1 where field6='BB'
INSERT INTO test1 SELECT field5 from #temptest1

Call the stored procedure from within BuildProfessional:

SQL {call "proc1"}

NOTE: Refer to the Microsoft SQL Server documentation for advanced details on passing parameters to/from stored procedures.