Problem referencing a global temporary table

I'm having difficulty referencing a global temporary table in a stored procedure. My stored procedure will execute correctly for days on end, but then all of a sudden it will stop working and I will get messages like:

Invalid object name '##TableName'.

and

Cannot drop the table '##TableName, because it does not exist in the system catalog.

==========================================================================

In my stored procedure I am first creating my temporary table using the syntax below. I need to create a seed value from a parameter, so that is why I am using the SET @.SQL and EXEC(@.SQL) statements:

SET @.SQL =
'CREATE TABLE ##TableName (
[Value1] [varchar] (11) ,
[Value2] [numeric](13, 0) ,
[Value3] [varchar] (30) ,
[Value4] [varchar] (30) ,
[Value5] [int] IDENTITY (' + CAST(@.Variable AS VARCHAR(10)) + ', 1) NOT NULL )'
EXEC(@.SQL)

I then begin a transaction and perform an INSERT INTO statement into this temp table, and then later perform a SELECT statement, and finally a DROP statement.

Any ideas or can anyone point me in the right direction as to why this works SOME of the time?

Just top make sure that you know that a global temporary table can be only at one time per server. So if any other users also executes the procedure and drops the "global" temporary table, it won′t be accessible in the other session anymore. Temp Table with one dash '#' are session specific, so can created in every session. Global ones, with two dashes '##' are global created. Every user sees them and can manipulate (or even drop) them.

So I guess any other process dropped your global temporary table.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de
|||Thank you for the info. I am aware of the difference between the global-specific temp table vs. the session-specific temp table. In fact, I chose to create the global-specific temp table so that I can reference the table after the EXEC(@.SQL) statement runs.
|||

It is hard to tell what might be wrong with the code without seeing some sort of repro script. Note that if there are multiple references to a global temporary table for example, SQL Server will delete it automatically after all users referencing the table have disconnected from the server. This can lead to unpredictable behavior if you use multiple connections but doesn't seem to be the case here. In any case, you can modify the code to use just a temporary table instead by doing following:

create table #.... (

columns except the identity column

)

exec ('alter table # add ... identity column with seed')

insert into #...

select * from #...

This approach doesn't require any dynamic SQL except for the few DDLs and it is easier to read/debug also.

|||

hi!

i used some temporary table in store procedure (sqlserver 2005)

our team have report software calisto .

the calisto use crystal and reports which use

this store procedure .

because of that,

we have list of many temporary table with the same name

#dbo.sug_name ... ,#dbo.sug_name ... ,......

in the system database .

what could be the reason for that and how can we drop it ?

Msg 3701, Level 11, State 5, Line 2

Cannot drop the table '#sug_name', because it does not exist or you do not have permission."

Problem referencing a global temporary table

I'm having difficulty referencing a global temporary table in a stored procedure. My stored procedure will execute correctly for days on end, but then all of a sudden it will stop working and I will get messages like:

Invalid object name '##TableName'.

and

Cannot drop the table '##TableName, because it does not exist in the system catalog.

==========================================================================

In my stored procedure I am first creating my temporary table using the syntax below. I need to create a seed value from a parameter, so that is why I am using the SET @.SQL and EXEC(@.SQL) statements:

SET @.SQL =
'CREATE TABLE ##TableName (
[Value1] [varchar] (11) ,
[Value2] [numeric](13, 0) ,
[Value3] [varchar] (30) ,
[Value4] [varchar] (30) ,
[Value5] [int] IDENTITY (' + CAST(@.Variable AS VARCHAR(10)) + ', 1) NOT NULL )'
EXEC(@.SQL)

I then begin a transaction and perform an INSERT INTO statement into this temp table, and then later perform a SELECT statement, and finally a DROP statement.

Any ideas or can anyone point me in the right direction as to why this works SOME of the time?

Just top make sure that you know that a global temporary table can be only at one time per server. So if any other users also executes the procedure and drops the "global" temporary table, it won′t be accessible in the other session anymore. Temp Table with one dash '#' are session specific, so can created in every session. Global ones, with two dashes '##' are global created. Every user sees them and can manipulate (or even drop) them.

So I guess any other process dropped your global temporary table.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de|||Thank you for the info. I am aware of the difference between the global-specific temp table vs. the session-specific temp table. In fact, I chose to create the global-specific temp table so that I can reference the table after the EXEC(@.SQL) statement runs.|||

It is hard to tell what might be wrong with the code without seeing some sort of repro script. Note that if there are multiple references to a global temporary table for example, SQL Server will delete it automatically after all users referencing the table have disconnected from the server. This can lead to unpredictable behavior if you use multiple connections but doesn't seem to be the case here. In any case, you can modify the code to use just a temporary table instead by doing following:

create table #.... (

columns except the identity column

)

exec ('alter table # add ... identity column with seed')

insert into #...

select * from #...

This approach doesn't require any dynamic SQL except for the few DDLs and it is easier to read/debug also.

|||

hi!

i used some temporary table in store procedure (sqlserver 2005)

our team have report software calisto .

the calisto use crystal and reports which use

this store procedure .

because of that,

we have list of many temporary table with the same name

#dbo.sug_name ... ,#dbo.sug_name ... ,......

in the system database .

what could be the reason for that and how can we drop it ?

Msg 3701, Level 11, State 5, Line 2

Cannot drop the table '#sug_name', because it does not exist or you do not have permission."

Problem INSERTING duplicate Rows

I'm trying to populate a temporary table with unique data, however my
populating query produces duplicated data. I've tried using a sub query
(i.e. querying the temp table during the INSERT to see if the data
already exists), but that doesn't stops the primary key violation
error. I've included the code below. tblSearchProduct stores a list of
words, along with the ID of the product title that word appears in. I'm
doing a search on products with certain words in the titles, so
therefore don't want the same product to appear multiple times. Any
suggestions? Ideally I want to tell SQL Server to just ignore the error
and move onto the next record in the result set, but I'm not sure how
to achieve that.
CREATE TABLE #tResults (
Position int NOT NULL identity(1,1),
[ID] varchar(50) NOT NULL,
PRIMARY KEY([ID])
)
INSERT INTO #tResults([ID])
SELECT s.[ID]
FROM
tblSearchProduct s
INNER JOIN tblContractProduct c
ON s.ID = c.ContractProductID
INNER JOIN tblProduct p
ON p.ProductID = c.ProductID
WHERE 'the' LIKE Word+'%' OR Word LIKE 'the%'
AND [ID] NOT IN (SELECT [ID] FROM #tResults)
ORDER BY ProductNameand...@.thevalley.f9.co.uk wrote:
> I'm trying to populate a temporary table with unique data, however my
> populating query produces duplicated data. I've tried using a sub
query
> (i.e. querying the temp table during the INSERT to see if the data
> already exists), but that doesn't stops the primary key violation
> error. I've included the code below. tblSearchProduct stores a list
of
> words, along with the ID of the product title that word appears in.
I'm
> doing a search on products with certain words in the titles, so
> therefore don't want the same product to appear multiple times. Any
> suggestions? Ideally I want to tell SQL Server to just ignore the
error
> and move onto the next record in the result set, but I'm not sure how
> to achieve that.
> CREATE TABLE #tResults (
> Position int NOT NULL identity(1,1),
> [ID] varchar(50) NOT NULL,
> PRIMARY KEY([ID])
> )
> INSERT INTO #tResults([ID])
>
> SELECT s.[ID]
> FROM
> tblSearchProduct s
> INNER JOIN tblContractProduct c
> ON s.ID = c.ContractProductID
> INNER JOIN tblProduct p
> ON p.ProductID = c.ProductID
> WHERE 'the' LIKE Word+'%' OR Word LIKE 'the%'
> AND [ID] NOT IN (SELECT [ID] FROM #tResults)
> ORDER BY ProductName
Try the following select stmt.

> SELECT distinct s.[ID]
> FROM
> tblSearchProduct s
> INNER JOIN tblContractProduct c
> ON s.ID = c.ContractProductID
> INNER JOIN tblProduct p
> ON p.ProductID = c.ProductID
> WHERE 'the' LIKE Word+'%' OR Word LIKE 'the%'
> AND [ID] NOT IN (SELECT [ID] FROM #tResults)
> ORDER BY ProductName|||SELECT distinct s.[ID]
FROM
tblSearchProduct s
INNER JOIN tblContractProduct c
ON s.ID = c.ContractProductID
INNER JOIN tblProduct p
ON p.ProductID = c.ProductID
WHERE 'the' LIKE Word+'%' OR Word LIKE 'the%'
AND [ID] NOT IN (SELECT [ID] FROM #tResults)
ORDER BY ProductName|||for some reason the update query is not appearing, anyway try distinct
after select, like
select distinct s.[ID]|||INSERT INTO #tResults([ID])
SELECT DISTINCT S.[id]
FROM ... etc
You don't need ORDER BY. It does nothing useful for you.
David Portas
SQL Server MVP
--|||This is not a RDBMS design at all, but a sequential file system in a
bad disguise. Stop using the "universal magical" VARCHAR(50) when you
know it is oversized. Stopping putting silly redundant "t_" and "tbl_"
prefixes on table names in violation of ISO-11179. Stop using IDENTITY.
Why did you put an ORDER BY on an INSERT INTO statement? Tables are
not sequential files; they have no ordering.
CREATE TABLE #Results
(product_id CHAR(13) NOT NULL PRIMARY KEY); -- I'll guess you use EAN
INSERT INTO #Results(product_id)
SELECT DISTINCT S.product_id
FROM SearchProducts AS S,
ContractProducts AS C,
Products AS P
WHERE P.word LIKE '% the %'
AND P.product_id = C.product_id
AND S.product_id = C.product_id;
But now I have to ask, why are SearchProducts, ContractProducts and
mere plain old Products logically different? I'll bet they are all the
same kind of entities, but you just put them into different tables
based on some status. This how we did it with punch cards and tape
files. Likewise, why is the same data element called ID, product_id
and contract_product_id in the same schema?
I think you need some serious help with database design.