lapucca
asked on
I can't drop a newly created table
A new tabel was created using a stored procedure which ran for 10 minutes and produce no rows of data at all so I cancel the query while it's still running. Now, I can't right click to delete the table or script a Drop table query. It will just run the drop query a quite a while and I had to cancel that. how can I remove this tabel from my database? Thank you.
ASKER
yes, that's the same sp you've been helping me with tonight. The dbo.Applicaton only has 233 rows. not even the Teh Drop Table, it ran in a loop. What is a profile of the server, where can I get that? I use the SQL generated drop code
USE [CtsiMembershipProduction]
GO
/****** Object: Table [dbo].[NewApplication] Script Date: 10/16/2008 20:40:41 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[NewApplication]') AND type in (N'U'))
DROP TABLE [dbo].[NewApplication]
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Agree. But why it runs in loop and not droping the table and the code I copied in this posting is generated by SQL?
ASKER
Well, this is strange. I open the table I created using s.p. and add my login user id to all the permission in the property page. Run the sql generate Drop Tabel query and I was successfully dropping the table. I wonder if this is creating a problem because I don't have permission to alter the tabel. But, I created the tabel in my sp. What's going on here?
ASKER
The sp cross join and innner join still not working and those tables weren't created properly. ONce that's fixed I'm able to drop the tables. Thanks.
You have to have the correct priveleges; check to ensure they are what you think they are.
10 minutes is quite awhile. I'm assuming that you did a SELECT INTO in the SP to create the table. How long does just the SELECT run, how many rows returned, etc? What is the profile of the machine that the server resides on? The DROP TABLE should run instantly (almost). What state is the database in? What does the code look like? If you don't let anything complete, i'm surprised there is a table at all.
Regards,
jmoss111