Tables will not drop 1

[whateveragain]

What reasons are there for a table not being dropped? I tried the following 3 ways and none of them work. If I run the SP outside the app, it works. The SP is being called and run because I created a junk table within the SP to see if it were being entered. None of these tables are being used in an ASP.net form.

1. DROP Table Mytbl1

2. IF EXISTS (SELECT * from sysobjects
WHERE id = object_id(N'[yeldata].[dbo].[Mytbl]') AND
OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP Table [yeldata].[dbo].[Mytbl1]

3. IF EXISTS (SELECT * from sysobjects
WHERE id = object_id(N'[yeldata].[dbo].[Mytbl1]') AND
OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP Table Mytbl1

 

[markros]

Could it be a permission issue?

PluralSight Learning Library

 

[whateveragain]

Other tables drop earlier in the process. I just tried changing the code to delete the data and not drop the tables and they won't delete. If I run a sample test SP against the very same tables without running the entire app, it works. There are no filters, triggers or indexes established with these tables. They are short lived, but not temparary because I need them across a couple of forms and a couple of SPs.

 

[SQLBill]

A couple of possibilities.

1. you say other tables drop, well permissions can be granted on the object level...so you might have permission to drop some tables but not others.

2. something may be using the table and is keeping it from being dropped.

When you run the DROP statement (#1), what error message do you get back?

Another thing to check, do you have the right table name? Who owns the table?

If the objects are case-sensitive, the table might be mytbl1. There could be a space at the end of Mytbl1.
The table could be owned by someoneelse.Mytbl1.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[whateveragain]

When you run the DROP statement (#1), what error message do you get back? -----None. it runs as if there were no errors. The only reason I know it didn't drop the table is because I selected it into a new table when it should have been dropped and it did create the new table. Since the stored procedure doesn't work the 2nd time I call it, it may have an error that's not displayed and is simply blowing past the SP because the tables weren't dropped?

Another thing to check, do you have the right table name? Who owns the table? -----all my tables are in one database. I'm still creating this on my desktop and have not uploaded to the website yet so there are not too many permission issues yet.

 

[whateveragain]

I should add that all the tables I'm having trouble dropping are programmatically made. Would them being programmatically created have anything to do with this?

 

[dBjason]

Might be that the account the program is creating them under has different rights than the account you're using to drop them.

 

[SQLBill]

Let's start by checking the owner of the table. You don't say what version you are using, but whichever one it is, connect to the database, expand until you see the table folder. Click on that and the tables should show up in the right window with the owner listed.

Now, compare the owner to the login that is running the stored procedure...most applications have their own login and pass that into SQL Server.

If the table isn't owned by the application's login or by DBO, the application won't be able to drop it; unless permissions are granted for it to drop it.

Are you running the DROP TABLE mytbl1 from the query window or from the application?

Try running it from the query window...that should return an error if it doesn't drop the table. If the table drops then YOU have permission to drop the table. Now you need to see what login the application is using and grant that login permissions to drop the table (ddladmin).

If the tables aren't owned by DBO, you could solve the issue by changing the owner to DBO.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875