Solved

I can't drop a newly created table

Posted on 2008-10-16
7
220 Views
Last Modified: 2012-05-05
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.
0
Comment
Question by:lapucca
  • 4
  • 3
7 Comments
 
LVL 18

Expert Comment

by:jmoss111
ID: 22737699
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
0
 

Author Comment

by:lapucca
ID: 22737722
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

0
 
LVL 18

Accepted Solution

by:
jmoss111 earned 500 total points
ID: 22737754
I was asking about the state of the hardware. You have problems with the query; you're going to have to work on that because that is where the problem lies, nothing with 233 rows is going to run 10 minutes. The DROP TABLE cant run in a loop if the code that I see in your next post is the code in question. When I do a DROP TABLE on a 700000 row table its instantaneous.
0
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 

Author Comment

by:lapucca
ID: 22737849
Agree.  But why it runs in loop and not droping the table and the code I copied in this posting is generated by SQL?  
0
 

Author Comment

by:lapucca
ID: 22737924
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?  
0
 

Author Comment

by:lapucca
ID: 22739223
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.
0
 
LVL 18

Expert Comment

by:jmoss111
ID: 22739694
You have to have the correct priveleges; check to ensure they are what you think they are.
0

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

808 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question