Member Avatar for 007tron

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
  • 4K Views
  • 22 Hours Discussion Span
  • Latest Post Latest Post by 007tron
Member Avatar for Tess James

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 because: n/a
Member Avatar for Netcode

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.

Member Avatar for 007tron

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

Member Avatar for 007tron

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

Member Avatar for 007tron

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

Member Avatar for BitBlt

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.

Member Avatar for 007tron

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.

Member Avatar for 007tron

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

Thanks to everyone that contributed in solving my problem

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.