Temporary Tables - SQL Stored Procedures
-
Hi After using Temporary table, you need to drop the table before using it again. Then you will not get that error.
Harini
Harini Krishnaswamy wrote:
After using Temporary table, you need to drop the table before using it again. Then you will not get that error.
It's not like that harini, Actually I am dropping the table after use Take the following situation 1. I created temporary table in stored procedure 2. If two users are accessing the procedure at same time it will show error. Because the first called procedure already created temporary table and it is not yet finished it's execution to drop the table. At the same time second procedure is also trying to create the same table and it will show the error. I need to overcome this.
printf("Navaneeth!!") www.w3hearts.com
-
What is your opinion about using temporary tables in SQL Server stored procedure. Is it a good practice ? If yes consider the following 1. I am using temporary tables in stored procedure and accessing this procedure from ASP.NET page. If more than one user came to the page at same time, it is showing table already exist error. But I have read that SQL Server will attach some timestamp or some session value with the temporary table name what we given. Then there is no chance or getting this error. But still I am getting the error. Where am I going wrong ? To solve this error I am passing the session id from page and appending that with the table name. Is it the rite way to overcome this problem ? If no 1. Then what is the alternate solution for temporary tables ? I am totally :confused:
printf("Navaneeth!!") www.w3hearts.com
are you creating the temporary in "tempdb" ? Local temporary (#table_name) tables are for current session; global temporary (##table_name)tables for all sessions. Temporary tables are automatically dropped when they go out of scope. 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. 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.
Regards KP
-
What is your opinion about using temporary tables in SQL Server stored procedure. Is it a good practice ? If yes consider the following 1. I am using temporary tables in stored procedure and accessing this procedure from ASP.NET page. If more than one user came to the page at same time, it is showing table already exist error. But I have read that SQL Server will attach some timestamp or some session value with the temporary table name what we given. Then there is no chance or getting this error. But still I am getting the error. Where am I going wrong ? To solve this error I am passing the session id from page and appending that with the table name. Is it the rite way to overcome this problem ? If no 1. Then what is the alternate solution for temporary tables ? I am totally :confused:
printf("Navaneeth!!") www.w3hearts.com
DooDooo wrote:
What is your opinion about using temporary tables in SQL Server stored procedure. Is it a good practice ?
They can be. Once, by the introduction of temporary tables, reduced the query time from 20 minutes down to 7 seconds.
DooDooo wrote:
If more than one user came to the page at same time, it is showing table already exist error.
Are you using the same connection for all pages in you ASP.NET application. You should not share a connection between page requests. A connection object should be created, opened, the query performed then the connection closed and allowed to go out of scope. The connection should not be stored anywhere for later use - only the connection string is needed.
Upcoming events: * Edinburgh: Web Security Conference Day for Windows Developers (12th April) * Glasgow: Introduction to AJAX (2nd May), SQL Server, Mock Objects My website
-
DooDooo wrote:
What is your opinion about using temporary tables in SQL Server stored procedure. Is it a good practice ?
They can be. Once, by the introduction of temporary tables, reduced the query time from 20 minutes down to 7 seconds.
DooDooo wrote:
If more than one user came to the page at same time, it is showing table already exist error.
Are you using the same connection for all pages in you ASP.NET application. You should not share a connection between page requests. A connection object should be created, opened, the query performed then the connection closed and allowed to go out of scope. The connection should not be stored anywhere for later use - only the connection string is needed.
Upcoming events: * Edinburgh: Web Security Conference Day for Windows Developers (12th April) * Glasgow: Introduction to AJAX (2nd May), SQL Server, Mock Objects My website
Colin Angus Mackay wrote:
re you using the same connection for all pages in you ASP.NET application
Never. I use separate connection. What I mean is when more than one users coming to same page it is showing error. Like I have one aspx page, let it be a.aspx. If you and me are accessing the page at same time it is showing the error. In page load I am calling one procedure that will make temporary table. Then I am getting error. What's your opinion on passing the session id from page to storedprocedure and creating a table in that name. ?
printf("Navaneeth!!") www.w3hearts.com
-
are you creating the temporary in "tempdb" ? Local temporary (#table_name) tables are for current session; global temporary (##table_name)tables for all sessions. Temporary tables are automatically dropped when they go out of scope. 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. 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.
Regards KP
Thanks Krish I am using ##temp. So as you told it will be treated as global temporary table. Is it ? From my procedure I am calling 10 sub procedures. In all these procedures I am using this temporary table. So when will it's scope ends ? After finishing the procedure execution, it's not dropping the table automatically.
printf("Navaneeth!!") www.w3hearts.com
-
Colin Angus Mackay wrote:
re you using the same connection for all pages in you ASP.NET application
Never. I use separate connection. What I mean is when more than one users coming to same page it is showing error. Like I have one aspx page, let it be a.aspx. If you and me are accessing the page at same time it is showing the error. In page load I am calling one procedure that will make temporary table. Then I am getting error. What's your opinion on passing the session id from page to storedprocedure and creating a table in that name. ?
printf("Navaneeth!!") www.w3hearts.com
DooDooo wrote:
What's your opinion on passing the session id from page to storedprocedure and creating a table in that name. ?
I don't have an opinion on that topic. I've never tried it. Off the top of my head I can't see any difficulties with it.
Upcoming events: * Edinburgh: Web Security Conference Day for Windows Developers (12th April) * Glasgow: Introduction to AJAX (2nd May), SQL Server, Mock Objects My website
-
Harini Krishnaswamy wrote:
After using Temporary table, you need to drop the table before using it again. Then you will not get that error.
It's not like that harini, Actually I am dropping the table after use Take the following situation 1. I created temporary table in stored procedure 2. If two users are accessing the procedure at same time it will show error. Because the first called procedure already created temporary table and it is not yet finished it's execution to drop the table. At the same time second procedure is also trying to create the same table and it will show the error. I need to overcome this.
printf("Navaneeth!!") www.w3hearts.com
Hope you are trying to create a table not in tempdb. Check whether the tbale is already present before executing "CREATE TABLE" i.e. IF NOT EXISTS (SELECT * FROM sysobjects WHERE name = 'tblA') CREATE TABLE tblA (col1 varchar(4), col2 ........)
Regards KP
-
DooDooo wrote:
What's your opinion on passing the session id from page to storedprocedure and creating a table in that name. ?
I don't have an opinion on that topic. I've never tried it. Off the top of my head I can't see any difficulties with it.
Upcoming events: * Edinburgh: Web Security Conference Day for Windows Developers (12th April) * Glasgow: Introduction to AJAX (2nd May), SQL Server, Mock Objects My website
Colin Angus Mackay wrote:
Off the top of my head I can't see any difficulties with it.
Thank you
printf("Navaneeth!!") www.w3hearts.com
-
Colin Angus Mackay wrote:
Off the top of my head I can't see any difficulties with it.
Thank you
printf("Navaneeth!!") www.w3hearts.com
-
Thanks Krish I am using ##temp. So as you told it will be treated as global temporary table. Is it ? From my procedure I am calling 10 sub procedures. In all these procedures I am using this temporary table. So when will it's scope ends ? After finishing the procedure execution, it's not dropping the table automatically.
printf("Navaneeth!!") www.w3hearts.com