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: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

Question has a verified solution.

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

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…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

688 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