Solved How to provide dynamic SQL table name as metadata in sql operation

Hello ,

we generate daily backup of d_stockiest table with timestamp appended.
i wrote dynamic sql loop query for deleting all backup tables one by one except recent 5 backup tables.

but it gives bellow error :"Cannot drop the table 'd_stockist_hq_mapping_bkp_20171010173223', because it does not exist or you do not have permission".


because name of table to be deleted is dynamic so i cant drag that metadata on sql operation.

if i run these query on sql server it works n it gives below result



please suggest solution

Hi,
The error message mentions that the table might not exist or the user does not have permisssion to drop the table.
Can you check this last point ?

You can try adding the catalog.schema prefix to the table name too (sql server's query tool has a "current" database set, the stambia process might not have such thing, depending on how your metadata is configured).
For example : Drop table db01.schema01.d_stockist_...

Hi,

Thanks for your solution .
its working properly.


Thanks & Regards,
Nikita Barve