Cannot drop tables because does not exist in system catelog? 1

[Ovatvvon]

I'm trying to have temporary tables created to trasfer data and change the architecture of some tables. The process I'm using to create them is the following code in a stored procedure:

CREATE TABLE _temp_Translation_CM_SUBTYPE
(
id 	int 	IDENTITY (1, 1)	NOT NULL,
type_id	int			NOT NULL,
title 	varchar	(50)		NOT NULL,
icon_id	int			NULL,
CONSTRAINT PK_temp_CM_SUBTYPE PRIMARY KEY (id)
) 
ON [PRIMARY]

the problem is when I try dropping them (using query analyzer), I get errors like this:

Server: Msg 3701, Level 11, State 5, Line 1
Cannot drop the table '_temp_Translation_CM_SUBTYPE', because it does not exist in the system catalog.


I have also tried creating the table like the code below, but it still does not "exist in the system catalog" and will not allow me to drop it:

CREATE TABLE _temp_Translation_CM_SUBTYPE
(
id 	int 	IDENTITY (1, 1)	CONSTRAINT PK_temp_CM_SUBTYPE PRIMARY KEY NOT NULL,
type_id	int			NOT NULL,
title 	varchar	(50)		NOT NULL,
icon_id	int			NULL
)
ON [PRIMARY]

I also tried dropping it from a stored procedure as well, and it worked there!

Any idea why it can work in one and not the other? Am I doing something wrong with the code while creating the tables?



-Ovatvvon :-Q

 

[gmmastros]

I think you are a little too close to those system tables. Generally, you should avoid them is possible. Instead, use the Information_Schema views. Anyway....

To create a temp table, you preface the table name with #

Create Table #Temp_Tran....
(
field....
)

The pound sign tells SQL Server that it is a temp table.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Ovatvvon]

Will it still create it as a physical table like any normal table, that can hold the data, and if left alone, will continue to hold the data until a later time (whether it is 2 minutes later, or 2 days later)?

(I didn't think I was working with any system tables here. Just creating tables to use them as temporary transition tables for the data, and then want to be able to drop them when done.)

(I the other posts I had in reference to finding and dropping constraints were related to non-temporary tables - the old ones that are being replaced.)


-Ovatvvon :-Q

 

[gmmastros]

>> I'm trying to have temporary tables created

I misunderstood. Temporary tables disappear after you are 'done' with them. Ex. Using QA, you create a temp table, while the QA window is open, the temp table exists.

If you want Permanent tables that should exist for the next couple hours, then create them without the # sign. It may be the leading underscores that are causing your problem.

Try this...

CREATE TABLE temp_Translation_CM_SUBTYPE
(
id int IDENTITY (1, 1) PRIMARY KEY,
type_id int NOT NULL,
title varchar (50) NOT NULL,
icon_id int NULL
)
ON [PRIMARY]


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Ovatvvon]

Oh, that is my fault for not using the correct terminology. Sorry about that.

I removed the leading underscore, but it is the same situation. Cannot do it in Query Analyzer, but can in the stored procedure. (It won't be the end of the world if I can't get it to work with Query Analyzer, it just makes me wonder if I am setting up the tables wrong, or missing some other step.)

-Ovatvvon :-Q

 

[gmmastros]

I tested this on one of my databases and it seemed to work fine from within QA (even with the underscore). Is it possible that you are trying to drop the table in the wrong database?

Here is the code I used.

CREATE TABLE TekTips.dbo._temp_Translation_CM_SUBTYPE
(
id     int     IDENTITY (1, 1)    PRIMARY KEY,
type_id    int            NOT NULL,
title     varchar    (50)        NOT NULL,
icon_id    int            NULL
)
on [PRIMARY]

go

Drop Table tektips.dbo._temp_Translation_CM_SUBTYPE

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

Looking at this a little closer, it could be a owner issue.

If you create a table without specifying the owner, and you are logged in with a user account, then that user becomes the owner.

Run this...

Select * From Information_Schema.Tables

Do you see anything other than dbo for the Table_Schema?

I recommend that you specify the owner in the Create Table statement

Create Table [red]dbo.[/red]TableName ...

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Ovatvvon]

You had me worried for a minute that I was working on the wrong Database (i.e. Not Development) in QA. I did double (& Tripple) check it though, even by opening a new QA session and verifying that I was opening it in the DEV Database...I'm working with the right set...it just doesn't want to work for some reason. :-(

-Ovatvvon :-Q

 

[Ovatvvon]

That was it!! The user account name was listed when running that sql statement, so I destroyed the tables, and specified the owner when creating them. Now I'm able to interact with it correctly using QA.

Why is it always the simple things that get ya?....

Thank you very much!!

-Ovatvvon :-Q