Temporary table with coldfusion

[numbered]

I'm trying to work with temporary tables using MS sql and coldfusion. It's not working the way it should.

<cfquery name="tempTableCreate" datasource="#application.dataSource#">
CREATE TABLE ##table(
id int IDENTITY(1,1) PRIMARY KEY,
value1 smalldatetime NULL,
value2 varchar(5) NULL)
</cfquery>

The problem is that the same temp table exists for all sessions. I need it to be unique for each session. It's only suppose to be active for as long as the connection is live. In coldfusion you don't close a connection. How do I use temp tables with coldfusion?

Thank you,
Seven

 

[JayKusch]

I beleive your CREATE TABLE is creating a global and not a local table. Try takeing one of the # off as in ...

<cfquery name="tempTableCreate" datasource="#application.dataSource#">
CREATE TABLE #table(
    id int IDENTITY(1,1) PRIMARY KEY, 
    value1 smalldatetime NULL, 
    value2 varchar(5) NULL)
</cfquery>

Thanks

J. Kusch

 

[numbered]

I think that might be the problem too but on a .cfm page you cannot have just a single #(pound sign) you have to write it with 2 pound signs, ##tempTable...which will output #tempTable. ####tempTable will output ##tempTable.

Maybe sql is reading it as ## and coldfusion is seeing it as #. I might need to post this on the coldfusion board.

Thanks for the input

 

[Pattycake245]

Instead of creating the table in Cold Fusion, why don't you create the query in a stored procedure and then call the stored procedure through Cold Fusion. Your method creates server overhead because the query ends up being compiled on the Cold Fusion side and then again on the SQL side.

<cfquery name="Create_table"
datasource="#application.dataSource#"
username="#UserName#"
password="#Password#">
exec create_table
</cfquery>

Tim