We help IT Professionals succeed at work.

#Temp Table Problem

Mark Wilson
Mark Wilson asked
on
Medium Priority
204 Views
Last Modified: 2010-03-20
DECLARE @TxtPtr binary(16), @applicantid integer, @Data varchar(8000), @Offset integer
SET              NOCOUNT ON CREATE TABLE #Temp(applicantid integer, RTF text) DECLARE YourCursor CURSOR FOR
                          SELECT     applicantid
                           FROM         gradrec_coverletter_t
Hi,

I have been using the following code successfully for a year.

Now I keep getting the error There is already an object named '#temp' in the database.

Is there something in the code I need to change?

I have looked in the tempdb database on the server, under temporary tables and theres about 7 instances of #temp table. I tried changing the temp tablename to #letters but still got the same error only with #letters in it.

Is there anyway I can resolve the error?

Thanks


OPEN YourCursor FETCH NEXT
FROM         YourCursor
INTO            @applicantid WHILE @@FETCH_STATUS = 0 BEGIN INSERT #Temp(applicantid, RTF)
VALUES     (@applicantid, '')
                          SELECT     @TxtPtr = TEXTPTR(RTF), @OffSet = 0
                           FROM         #Temp
                           WHERE     applicantid = @applicantid WHILE @OffSet IS NOT NULL BEGIN
                                                      SELECT     @Data = CAST(CAST(SUBSTRING(document_im, @Offset + 1, 8000) AS varbinary(8000)) AS varchar(8000))
                                                       FROM         gradrec_coverletter_t
                                                       WHERE     applicantid = @applicantid IF Len(@Data) > 0 BEGIN UPDATETEXT #Temp.RTF @TxtPtr @Offset NULL @Data
SET              @OffSet = @OffSet + 8000 END ELSE BEGIN
SET              @OffSet = NULL END END FETCH NEXT
FROM         YourCursor
INTO            @applicantid END CLOSE YourCursor DEALLOCATE YourCursor
                          SELECT     applicantid, RTF
                           FROM         #Temp
DROP TABLE #Temp
Comment
Watch Question

SQL Server Data Dude
CERTIFIED EXPERT
Most Valuable Expert 2013
Author of the Year 2015
Commented:
IF OBJECT_ID('tempdb..#temp') IS NOT NULL
    drop table #temp

-- All your code goes here

Explore More ContentExplore courses, solutions, and other research materials related to this topic.