[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

#Temp Table Problem

Posted on 2008-01-28
1
Medium Priority
?
188 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
0
Comment
Question by:halifaxman
1 Comment
 
LVL 66

Accepted Solution

by:
Jim Horn earned 2000 total points
ID: 20763827
IF OBJECT_ID('tempdb..#temp') IS NOT NULL
    drop table #temp

-- All your code goes here
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Get the source code for a fully functional Access application shell with several popular security features that Access VBA application developers desire, but find difficult or impossible to figure out how to code. You get the source code for managi…

590 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question