drop orphaned temp table 1

[evaleah]

I am trying to alter a stored proc that has two temp tables in it. I ran it once to test it and it was taking too long so I stopped the process. That left two orphaned tables in the db. Now when I try to ALTER PROCEDURE it tells me those objects already exists.

I have tried searching all objects from all databases on the name of those tables and it keeps finding nothing. I rebooted the server, same thing.

Does anyone have any idea how to get rid of an orphaned temp table????

Thank you!!!!!!!!!!!

 

[LogicalmanUS]

evaleah,

Open Query Analyzer, find the name of the temp tables, and execute the DROP TABLE statements against each.

Example:

If table names are #Tab1 and #Tab2, then.

DROP TABLE #Tab1
DROP TABLE #Tab2

The references to the temp tables (as with all temp objects, are stored in the TempDB. You need not necessarily run the above script against that database though, as SQL understands that any objects beginning with # is a temp object.

Be careful to get the right number of pound signs though. A single # means the object was created for one thread, single session only (that means that once that instance of the SProc is finished, the table is dropped, and it's scope is contained within the SProc. Using ## means that once the object is created, other threads can access the data (therefore it is visible outside of that thread) and it is not single session only, as it will remain until the last thread accessing it ends, at which time it will be dropped.

Remember to log in the QA with an account that has sufficient priviledges (e.g. sa)

Logicalman

 

[evaleah]

I have searched TempDB, there is no sign of my two temp tables. I have tried dropping them, it says they do not exist in the system catalog. I have turned off the PC overnight, what else could kill the thread??? I am still getting the message that they exist when I try to alter the procedure.

Just as a note: I *can* execute the proc. That works. I just can't alter it.

Please, I am way stuck here. I don't want to keep having to rename my tables to retest the proc.

Just in case it is my Proc that is the issue, here it is:
ALTER PROCEDURE spFindPub
@author varchar(20) = NULL,
@year smallint = NULL,
@inter bit = NULL,
@intra bit = NULL,
@inactive bit = NULL,
@program int = NULL
AS

DECLARE @authorid bigint

BEGIN TRAN

IF @program is NULL
BEGIN
SELECT PublicationID
INTO #PubProg
FROM tb_Publication
END

If @program is NOT NULL
BEGIN
SELECT PublicationID,
Program =
(SELECT Program
FROM vwPrograms
WHERE vwPrograms.ProgramID = tb_PublicationProgram.ProgramID)
INTO #PubProg
FROM tb_PublicationProgram
WHERE ISNULL(tb_PublicationProgram.ProgramID,'') = CASE
WHEN @program is NULL THEN ISNULL(tb_PublicationProgram.ProgramID,'')
ELSE @program END
END
If @@error <> 0
begin
RAISERROR 50000 'Error selecting Program info.'
ROLLBACK TRAN
GOTO end_of_batch
end


IF @author is NULL
BEGIN
SELECT tb_Publication.PublicationID
INTO #PubAuthor
FROM tb_Publication
END

IF @author is NOT NULL
BEGIN
SELECT tb_PublicationAuthor.PublicationID
INTO #PubAuthor
FROM tb_PublicationAuthor
INNER JOIN tb_Author ON
tb_PublicationAuthor.AuthorID = tb_Author.AuthorID
WHERE ISNULL(tb_Author.AuthorName,'') LIKE CASE
WHEN @author is NULL THEN ISNULL(tb_Author.AuthorName,'')
ELSE @author END
END
If @@error <> 0
begin
RAISERROR 50000 'Error selecting Author info.'
ROLLBACK TRAN
GOTO end_of_batch
end


SELECT
tb_Publication.PublicationID,
tb_Publication.Title,
tb_Publication.PubYear,
tb_Publication.Journal,
#PubProg.Program,
ProgramInvolvement = CASE
WHEN InterProgramatic = 1 THEN 'Inter'
WHEN IntraProgramatic = 1 THEN 'Intra'
ELSE ' ' END
FROM
tb_Publication
LEFT JOIN #PubProg ON
#PubProg.PublicationID = tb_Publication.PublicationID
WHERE
tb_Publication.PublicationID in
(SELECT #PubProg.PublicationID FROM #PubProg)
AND
tb_Publication.PublicationID in
(SELECT #PubAuthor.PublicationID FROM #PubAuthor)
AND
ISNULL(PubYear,'') = CASE
WHEN @year is NULL THEN ISNULL(PubYear,'')
ELSE @year END
AND
ISNULL(InterProgramatic,'') = CASE
WHEN @inter is NULL THEN ISNULL(InterProgramatic,'')
ELSE @inter END
AND
ISNULL(IntraProgramatic,'') = CASE
WHEN @intra is NULL THEN ISNULL(IntraProgramatic,'')
ELSE @intra END
AND
ISNULL(Inactive,'') = CASE
WHEN @inactive is NULL THEN ISNULL(Inactive,'')
ELSE @inactive END
ORDER BY tb_Publication.PublicationID
If @@error <> 0
begin
RAISERROR 50000 'Error selecting Publication'
ROLLBACK TRAN
GOTO end_of_batch
end

DROP TABLE #PubAuthor
DROP TABLE #PubProg

COMMIT TRAN

end_of_batch:

 

[evaleah]

Actually, I figured out the error issue. It is that I cannot have more than one reference to &quot;INTO #temptable&quot;.

So, I have a new question.... Is there anyway to do this with the if statement so it fills the temp one way if a thing is null and another if is it not?

Thank you!!!!!!!!!!!!!

 

[JamesLean]

Try explicitly creating the table at the top and then use INSERT statements to fill it with data rather than SELECT...INTO:

CREATE TABLE #temp (col1 int, col2 int)

IF <condition>
BEGIN
  INSERT #temp
  SELECT col1, col2
  FROM table1
END
ELSE
BEGIN
  INSERT #temp
  SELECT col1, col2
  FROM table2
END

--James

 

[evaleah]

James,

Thank you so much. I have marked your post as helpful. That was *exactly* what I needed.

Eva