?
Solved

I can't drop a newly created table

Posted on 2008-10-16
7
Medium Priority
?
250 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
[X]
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
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 2000 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Suggested Courses

801 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