select * from ##tablename

I am executing the below scrip " select * from ##tablename" getting error as :-Invalid object.

How can i access this temporary table
more ▼

asked Nov 28 '11 at 02:41 AM in Default

jhunu gravatar image

90 25 29 31

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

A double # table is a global temporary table, and will cease to exists when all connections have stopped referencing it and the session that created the table has ended.

If you try to access the table outside of these conditions, it will no longer exist.
more ▼

answered Nov 28 '11 at 02:45 AM

Kev Riley gravatar image

Kev Riley ♦♦
50.8k 45 49 76

i am accessing in same connection.
Nov 28 '11 at 03:11 AM jhunu
can you post the script?
Nov 28 '11 at 03:21 AM Kev Riley ♦♦
(comments are locked)
10|1200 characters needed characters left
Check the existence of your global temp table using below query. SELECT * FROM tempdb..sysobjects where name like '%tablename%'
more ▼

answered Dec 10 '11 at 01:27 AM

salum gravatar image

213 1

Better to use SELECT OBJECT_ID('tempdb..[##myTempTableName]'). You will receive the correct ID or NULL if table doesn't exists. The like can return multiple objects if you will search a local temp table, but also global with the same name exists or any other table with table name starting as your table exists.
Dec 13 '11 at 01:15 PM Pavel Pawlowski
(comments are locked)
10|1200 characters needed characters left

i think there are two table, one is local temp table which we create like #table and another is ##Table which is global temp table concept.

local will be accesible to you only while ## global to all user who are on server till the time session not end by creator.
more ▼

answered Dec 13 '11 at 03:35 AM

Sri 1 gravatar image

Sri 1
11 9 9 10

you can more search the topic on google.
Dec 13 '11 at 03:35 AM Sri 1
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Nov 28 '11 at 02:41 AM

Seen: 1340 times

Last Updated: Nov 28 '11 at 02:45 AM