0

Hi I have a MS SQL db script, I have the same temp table declared more than once. I keep getting this error: There is already an object named ????(question marks are representative of table name) in the database. the following resembles what my script looks like.

IF OBJECT_ID('tempdb..#table123') IS NOT NULL
    DROP TABLE #table123
    


CREATE TABLE #table123(
     -- Key Fields
     Student                Integer     NOT NULL,
     ProcessDate            DateTime    NOT NULL,          
     JoinDate               DateTime    NULL,          
     FaceDate               DateTime    NULL,
     FinalDate              DateTime    NULL,
     Cash                   Money       NULL,
     SubjectCode            Char(5)     NULL
)     


CREATE TABLE #table567...

CREATE TABLE #table135..

-- Then I have the first table again, I drop it first then create it again as before:


IF OBJECT_ID('tempdb..#table123') IS NOT NULL
    DROP TABLE #table123
    


CREATE TABLE #table123(
     -- Key Fields
     Student                Integer     NOT NULL,
     ProcessDate            DateTime    NOT NULL,          
     JoinDate               DateTime    NULL,          
     FaceDate               DateTime    NULL,
     FinalDate              DateTime    NULL,
     Cash                   Money       NULL,
     SubjectCode            Char(5)     NULL
)

If you have any ideas please let me know.

4
Contributors
8
Replies
13
Views
5 Years
Discussion Span
Last Post by 007tron
0

Add a GO statement after DROP statement.

But why do u want to have the same table declared twice?:?:
I don't see any schema changes in the second declaration.:confused:

Edited by Tess James: n/a

0

I have the same temp table declared more than once

I would advise you try using another name but i would also like to ask if its a local or global temporary table. The reason for this is: Local temporary tables are only available to the current connection to the database for the current user and are dropped when the connection is closed. So if its local, you definitely can't create another temp table with the same name. It would definitely tell you that there is already an object with the same name as long as that connection is still open.

0

Add a GO statement after DROP statement.

But why do u want to have the same table declared twice?:?:
I don't see any schema changes in the second declaration.:confused:

I am doing a trace and trying to recreate an error so I can analysis it. These tables are created and dropped in sql batches and stored procedures

0

I would advise you try using another name but i would also like to ask if its a local or global temporary table. The reason for this is: Local temporary tables are only available to the current connection to the database for the current user and are dropped when the connection is closed. So if its local, you definitely can't create another temp table with the same name. It would definitely tell you that there is already an object with the same name as long as that connection is still open.

I am doing a trace and trying to recreate an error so I can analysis it. These tables are created and dropped in sql batches and stored procedures

0

Add a GO statement after DROP statement.

But why do u want to have the same table declared twice?:?:
I don't see any schema changes in the second declaration.:confused:

I will try that, will let you know

0

You could also just truncate the temp table, rather than drop/recreate. Since there are no schema differences, it should handle your requirement of an empty table.

0

Add a GO statement after DROP statement.

But why do u want to have the same table declared twice?:?:
I don't see any schema changes in the second declaration.:confused:

GO statements in the right places solved the issues. Thank you that was perfect.

0

GO statements in the right places solved the issues. Thank you that was perfect.

Thanks to everyone that contributed in solving my problem

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.