0

I ran an azure function for about an hour and a half and then started receiving these errors when it tried to drop tables:

Cannot drop the table 'TABLE_NAME', because it does not exist or you do not have permission.

I've checked the tables, they exist, but my function has somehow lost its permissions it seems, or something else is happening that I'm unaware of. Anyone have any suggestions as to how to fix this?

EDIT: I've noticed the errors coincide with my DB reaching 100% CPU utilization.

BlakeB9
  • 345
  • 1
  • 3
  • 13
  • As suggested by @dotnetstept, “Create managed identity for Azure function and also allow that identity to specific permission in DB.” References: https://stackoverflow.com/questions/71471144/how-to-give-my-blob-storage-trigger-function-permission-to-drop-table , https://www.pluralsight.com/guides/how-to-use-managed-identity-with-azure-sql-database https://sqlworldwide.com/how-to-use-managed-identity-with-azure-function-app/ – Madhuraj Vadde Mar 16 '22 at 05:58

1 Answers1

0

I've noticed the errors coincide with my DB reaching 100% CPU utilization.

Try to scale up the Database or try to Troubleshoot high cpu usage issues

You have to check the below conditions to overcome the Error

Cannot drop the table 'TABLE_NAME', because it does not exist or you do not have permission.

I hope you are doing the right way to drop a table and create the table if it does not exist. By using the below code

.to_sql(_name_, _con_, _schema=None_, _if_exists='replace'_, _index=True_, _index_label=None_, _chunksize=None_, _dtype=None_, _method=None_)

if_exists='replace' - Drop the table before inserting new values.

If you are using the same above you have to check the other possibilities.

  • Use Truncate to drop the table before the .to_sql()
engine = sqlalchemy.create_engine('mssql+pyodbc://<Your SQL SERVER>/<DB NAMEE>')
conn = engine.connect()
conn.execute("TRUNCATE TABLE <TABLE NAME>")
.to_sql(_name_, _con_, _schema=None_, _if_exists='replace'_, _index=True_, _index_label=None_, _chunksize=None_, _dtype=None_, _method=None_)
  • Make sure The User has all permissions to Access/Drop table.
  • Check if the Table is already Drop before we actually run the .to_sql()
Delliganesh Sevanesan
  • 4,146
  • 1
  • 5
  • 15
  • I am actually using to_sql replace, thank you. I didn't know that I should truncate the table beforehand, though, could you possibly explain why I should do that? Also, I ended up doing exactly that, scaling up the database, seems to be doing better, but I've had to scale it up a couple times. Whenever a failure happens, the DTU on the db spikes and that causes even more errors because it hits 100% utilization. I believe I've got it in a place where it doesn't come close to 100% anymore. – BlakeB9 Mar 17 '22 at 15:59
  • Truncate Immediately Remove all types of data in a table. So you can try to use Truncate in your code before deleting the table. so that it can be less time to complete the delete operation in SQL. Also, it reduces the usage of SQL CPU usage. – Delliganesh Sevanesan Mar 21 '22 at 04:50