I can't drop a newly created table

Posted on 2008-10-16
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.
Question by:lapucca
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
LVL 18

Expert Comment

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.



Author Comment

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]
/****** 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

LVL 18

Accepted Solution

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.
Business Impact of IT Communications

What are the business impacts of how well businesses communicate during an IT incident? Targeting, speed, and transparency all matter. Find out more in this infographic.


Author Comment

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?  

Author Comment

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?  

Author Comment

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.
LVL 18

Expert Comment

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

Featured Post

[Webinar] Code, Load, and Grow

Managing multiple websites, servers, applications, and security on a daily basis? Join us for a webinar on May 25th to learn how to simplify administration and management of virtual hosts for IT admins, create a secure environment, and deploy code more effectively and frequently.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Comparison query - 4 columns 9 50
SQL Query help 3 38
how to resize column length with primary ket 4 32
What does "Between" mean? 6 42
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

737 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