Contact Us
- Sales: sales@todaysystems.com
- Support: support@todaysystems.com
TB0031 - Use of Microsoft SQL Server Temporary Tables
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.