In our ETL, we have a stage schema, we use to create external tables for temporary purposes. Something happened downstream in our pipeline, that caused us to re-run the pipeline & recreate the external table. Our workflow is to drop the external table, and recreate it again. Attempting to drop the table results in failure though. The table exists in sys.objects though, but we're unable to see it in our navigation pane. The table was created with our service account, like all our other ETL tables. Attempting to drop the table with the service account yields the failure, same with our admin account. NOTE: The reason the pipeline initially failed was due to a query timeout when creating the table. I'm thinking, after the query timed out, it now exists in some broken state? This has happened twice now over the course of the last few days, and never before have I've seen this. Open to any ideas!

Hi @Kyle Macdonald (ALT) , Thankyou for using Microsoft Q&A platform and posting your query. As I understand your issue here is that you want to drop an external table in either dedicated SQL pool or Serverless SQL pool. But it's throwing error saying either table doesn't exist or you don't have sufficient permission. In my understanding , the problem here might be that the window in which you are running the SQL script is not connected to the database where you actually have that table. Please consider revisiting if you are connected to correct database by looking at the top of the panel. Check for the SQL pool as well as database. If it looks good, try using the command before drop external table command : Use <DatabaseName> ; Hope this will help. Please let us know if any further queries. ------------------------------ Please don't forget to click on or upvote button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how Want a reminder to come back and check responses? Here is how to subscribe to a notification If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators

this error isnt about it not existing its about the account your using to drop it not have the privs to do so find out what privs the account your using has on the schema / object you may need to get dba to drop it for you or grant you the correct privs to complete the action

DROP EXTERNAL TABLE not working

Accepted answer

AnnuKumari-MSFT 32,826 Reputation points Microsoft Employee

2022-03-04T13:16:14.677+00:00
Hi @Kyle Macdonald (ALT) ,
Thankyou for using Microsoft Q&A platform and posting your query.
As I understand your issue here is that you want to drop an external table in either dedicated SQL pool or Serverless SQL pool. But it's throwing error saying either table doesn't exist or you don't have sufficient permission.

In my understanding , the problem here might be that the window in which you are running the SQL script is not connected to the database where you actually have that table.

Please consider revisiting if you are connected to correct database by looking at the top of the panel. Check for the SQL pool as well as database.

If it looks good, try using the command before drop external table command :

Use <DatabaseName> ;

Hope this will help. Please let us know if any further queries.

------------------------------

Please don't forget to click on or upvote button whenever the information provided helps you.
Original posters help the community find answers faster by identifying the correct answer. Here is how

Want a reminder to come back and check responses? Here is how to subscribe to a notification

If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators
Please sign in to rate this answer.
Kyle Macdonald (ALT) 26 Reputation points

2022-03-04T17:12:24.45+00:00

Thanks Annu.

We are currently only using a serverless pool.

I can confirm, the external table is in our "trends" database:

And when I execute the drop statement, while in the same database, it fails:

Dozens of other tables have been created + dropped in the same location since this post was created, and have had no problems.

I tried to recreate the schema, but since it this table is stuck there, I can't drop the schema.

Kyle Macdonald (ALT) 26 Reputation points

2022-03-04T18:23:56.94+00:00

And just incase it wasn't clear, the problem table is in our "stage" schema:

AaronHughes 391 Reputation points

2022-03-04T23:53:59.66+00:00

Have you tried including the DB name in the statement ?

DROP EXETERNAL TABLE TRENDS.stage.event_20220227T200500;

without pointing out the obvious are you sure that that is a external table your picking up from all objects ?

perhaps try to query sys.objects and specify the type 'ET' to ensure your picking up the correct object type, because the query your running against all_objects could be any object in the schema called that name.

https://learn.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-objects-transact-sql?view=azure-sqldw-latest

or you could just do a normal drop as a test ?
DROP TABLE stage.event_20220227T200500;

Kyle Macdonald (ALT) 26 Reputation points

2022-03-07T16:59:29.073+00:00

Hi Aaron,

Explicitly using the database name in the drop external table statement yields the same error.

And, because I am using a serverless pool, dropping/creating normal tables isn't supported.

Therefor, all the tables we are using are external tables. And for whatever reason, in sys.objects they're all classified as type "U = Table (user-defined)", not ET. That does seem a bit strange considering the documentation you just linked, there should be a type for External Table.

I will demonstrate with an example:

The two external tables I'm struggling with both had a query timeout on them, and upon retry, I cancelled the query. After that cancellation, I couldn't drop them. I suspect there's something else going on there that has broken these two tables.

AnnuKumari-MSFT 32,826 Reputation points Microsoft Employee

2022-03-10T07:15:37.267+00:00

Hi @Kyle Macdonald (ALT) ,
Thanks for the details. I have reached out to the internal product team for the issue you are facing . I will get back with the response once I hear any resolution from them.

AnnuKumari-MSFT 32,826 Reputation points Microsoft Employee

2022-03-11T09:48:32.587+00:00

Hi @Kyle Macdonald (ALT) ,

I got the response from product team . They are working on the resolution .
Hotfix was deployed yesterday. It should prevent the issue from happening for newly created external tables using a name not already taken (in sys.tables). Fixing existing corrupted metadata requires another hotfix to be deployed It will likely land by another week. Thankyou for your patience!

AnnuKumari-MSFT 32,826 Reputation points Microsoft Employee

2022-03-15T10:16:00.55+00:00

Hi @Kyle Macdonald (ALT) ,
The fix seems to be deployed from Product team. Could you please confirm if you are able to drop the external table or not .

AnnuKumari-MSFT 32,826 Reputation points Microsoft Employee

2022-03-17T09:03:34.017+00:00

Hi @Kyle Macdonald (ALT) ,
Just checking back to see if you got the resolution for the issue . If not, please revert back so that we will respond back with the further support . Thanks. Kindly do click Accept Answer and Up-Vote for the same. In case you have any further query please do let us know.

Kyle Macdonald (ALT) 26 Reputation points

2022-03-18T18:51:18.277+00:00

Hi Annu, apologies for the delay.

I haven't run into the issue again, so that's great.

I still cannot delete those corrupted tables though. Should I keep the thread open until that's resolved?

AnnuKumari-MSFT 32,826 Reputation points Microsoft Employee

2022-03-22T09:43:56.717+00:00

Hi @Kyle Macdonald (ALT) ,
Thankyou for providing necessary details. The clean-up has been performed for the mentioned external tables in your database. Could you please confirm it from your end? If it works, kindly do consider clicking Accept Answer as accepted answers help community as well. If you have any further query , please do let us know.

Kyle Macdonald (ALT) 26 Reputation points

2022-03-22T20:10:37.937+00:00

Confirmed, tables are gone now. Thank you very much for addressing this!
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

DROP EXTERNAL TABLE not working

1 additional answer

Your answer