Link to home
Start Free TrialLog in
Avatar of lapucca
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.
Avatar of jmoss111
jmoss111
Flag of United States of America image

Hello lapucca,

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
Avatar of lapucca
lapucca

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]

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of jmoss111
jmoss111
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of lapucca

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?  
Avatar of lapucca

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?  
Avatar of lapucca

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.