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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Bookham Measures - 19 Dec 2007 18:33 GMT

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

Reply to this Message

Bob Barrows [MVP] - 19 Dec 2007 18:55 GMT

> Hi
>
[quoted text clipped - 16 lines]
> 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

Signature

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.

Reply to this Message

Webmaster Forum / ASP / Database Access / December 2007

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