ASP & Temporary Tables - Create OK, then "Not Found"

Hi

Classic ASP and SQL 2005. In my ASP I issue a create table statement:

objConn.Execute "CREATE TABLE #PCUserIDs (UserID int NOT NULL, EKM
numeric (6, 2) NULL, NKM numeric (6, 2) NULL)", , adCmdText +
adExecuteNoRecords

Then, later on I attempt to access it from within a stored procedure:

objConn.Execute "EXEC usp_Search_OvernightRates", , adCmdText +
adExecuteNoRecords

and I am told that #PCUserIDs does not exist:

Invalid object name '#PCUserIDs'.

Can anyone help? When I use profiler I see an Audit Logout and Audit Login.
This shouldn't be happening, as the connection is not closed or anything.

Regards
David

Re: ASP & Temporary Tables - Create OK, then "Not Found"

Bookham Measures wrote:
> Hi
>
> Classic ASP and SQL 2005. In my ASP I issue a create table statement:
>
> objConn.Execute "CREATE TABLE #PCUserIDs (UserID int NOT NULL, EKM
> numeric (6, 2) NULL, NKM numeric (6, 2) NULL)", , adCmdText +
> adExecuteNoRecords
>
> Then, later on I attempt to access it from within a stored procedure:
>
> objConn.Execute "EXEC usp_Search_OvernightRates", , adCmdText +
> adExecuteNoRecords
>
> and I am told that #PCUserIDs does not exist:
>
> Invalid object name '#PCUserIDs'.
>
> Can anyone help? When I use profiler I see an Audit Logout and Audit
> Login. This shouldn't be happening, as the connection is not closed
> or anything.
>
By "later on", do you mean "later on in the same page"? Can you
reproduce the problem by doing this?

objConn.Execute "CREATE TABLE #PCUserIDs (UserID int NOT NULL, EKM
numeric (6, 2) NULL, NKM numeric (6, 2) NULL)", , adCmdText +
adExecuteNoRecords
objConn.Execute "EXEC usp_Search_OvernightRates", , adCmdText +
adExecuteNoRecords

How about by doing this?
objConn.Execute "CREATE TABLE #PCUserIDs (UserID int NOT NULL, EKM
numeric (6, 2) NULL, NKM numeric (6, 2) NULL)", , adCmdText +
adExecuteNoRecords
objConn.Execute "Insert #PCUserIDs Values(1,238,3)", , adCmdText +
adExecuteNoRecords



--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.