May 15, 2011 at 7:31 am
Hello Everyone,
In a asp.net WebForm, based on MSSQL 2008, i called a Procedure, in which it is declared and filled a #Temp_Table.
Then when the user check / change data in the form and press the Continue button, the form do a select on the #Temp_Table and it will return the error:
Invalid object name '#Temp_Table'.
In Oracle there is the global temporary table that can be used per session and on which it is possible to do DML statements. In MS SQL instead, despite the existence of global temporary table, on it I can not do DML statements and this makes it unusable in my case.
What can I use?
Thanx a 1000
May 15, 2011 at 7:46 am
A local temp table is valid as long as the original connection exists and is visible to their creator. It is also possible to apply DML statements as long as the original connection exists.
I'd guess the connection is closed and reopened leading to the temp table getting deleted in between.
I don't think the issue is because of the temp table concept itself but rather related to the method being used. Since it's a .net issue I recommend to search a .net forum for a related issue. I'm sure there is a neasy solution.
May 15, 2011 at 8:53 am
You can't reference a Local #Temp Table outside the current scope Stored Procedure, etc.
In SQL Server you can create Global Temp Table specify and you can reference it outside of the SP but it is available to all sessions.
I local temp table is with one pound sign #Temp and a global temp table has two pound signs ##Temp.
http://databases.aspfaq.com/database/should-i-use-a-temp-table-or-a-table-variable.html
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 15, 2011 at 11:35 am
Hi Lutz,
Thank you for your replay. I confirm your guessing:
"I'd guess the connection is closed and reopened leading to the temp table getting deleted in between."
Problem is, I can't use a global temporary table because I can't perform DML statements on it. So, what will I use? Will I create and drop afterthat a permanent table using the sessionId as table name? I'm just afraid that will effect badly performance.
Any suggestion?
Thank you
May 15, 2011 at 11:42 am
Marc Bizzar (5/15/2011)
"I'd guess the connection is closed and reopened leading to the temp table getting deleted in between."
Perhaps I'm not understanding you correctly but the issue has to do with scope of the temporary table.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 15, 2011 at 11:51 am
Welsh Corgi (5/15/2011)
Marc Bizzar (5/15/2011)
"I'd guess the connection is closed and reopened leading to the temp table getting deleted in between."Perhaps I'm not understanding you correctly but the issue has to do with scope of the temporary table.
More likely it's that the connection is closed after the temp table is populated (hence resulting in it being dropped) and the new connection expecting it to be there. ASP is typically a disconnected model. Connect, do something, disconnect, return page.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 15, 2011 at 11:51 am
Maybe it's my fault. I'll ry to be more clear. What I'm trying to achieve is:
I need to based a WebForm, that can be used by multiple users simultaneously, on the "same" table. However this table is only a temporary table, used to check and modify data that will be posted in other permanent tables.
Marco
May 15, 2011 at 11:55 am
Marc Bizzar (5/15/2011)
Hello Everyone,In a asp.net WebForm, based on MSSQL 2008, i called a Procedure, in which it is declared and filled a #Temp_Table.
Then when the user check / change data in the form and press the Continue button, the form do a select on the #Temp_Table and it will return the error:
Invalid object name '#Temp_Table'.
Are you attempting to reference #Temp_Table from within or outside the Stored Procedure when you get the error "Invalid object name '#Temp_Table'"?
Has your .NET code completed execution of the Stored Procedure when you get the error?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 15, 2011 at 11:58 am
Welsh Corgi (5/15/2011)
Marc Bizzar (5/15/2011)
"I'd guess the connection is closed and reopened leading to the temp table getting deleted in between."Perhaps I'm not understanding you correctly but the issue has to do with scope of the temporary table.
Actually, Marc quoted a statement I made earlier.
Straight from BOL:
Local temporary tables are visible only to their creators during the same connection to an instance of SQL Server as when the tables were first created or referenced. Local temporary tables are deleted after the user disconnects from the instance of SQL Server.
You're right, it has to do with the scope of the temp table. And the scope is per connection.
@marc: regarding your latest comment: I can't see any significant performance impact between creating a temp table vs. a permanent table (other than the influence of the physical table being written to disc whereas the chances of a temp table to stay in memory are much higher). In such a scenario I would vote for a physical table. You might not even drop it at the end and recreate it each and every time. Make it a permanent table with an additional column to identify the rows belonging to your session and delete those rows once you're done.
May 15, 2011 at 12:05 pm
A quote from:
http://stackoverflow.com/questions/892351/sql-server-2005-and-temporary-table-scope
A local temporary table that is created in a stored procedure is dropped when the procedure ends; other stored procedures, or the calling process, can't see them.
Other local temporary tables are dropped when the session ends.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 15, 2011 at 12:06 pm
I will do what Luz suggested.
Thank you to all of you guys.
Really appriciated.
Marc
May 15, 2011 at 12:35 pm
Luz provided you with a good solution.
Per BOL, information about temporary tables:
Temporary tables are automatically dropped when they go out of scope, unless explicitly dropped by using DROP TABLE:
A local temporary table created in a stored procedure is dropped automatically when the stored procedure is finished.
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 that called the stored procedure that created the table.
All other local temporary tables are dropped automatically at the end of the current session.
Global temporary tables are automatically dropped when the session that created the table ends and all other tasks have stopped referencing them. The association between a task and a table is maintained only for the life of a single Transact-SQL statement. This means that a global temporary table is dropped at the completion of the last Transact-SQL statement that was actively referencing the table when the creating session ended.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply