Cannot find the object "xxx" because it does not exist or you do not have permissions.

byMaheshkumar Tiwari •
1

{tocify} $title={Table of Contents}



Problem Statement


The requirement is to load data in SQL with two conditions to fulfill
1. If table doesn't already exist then create it and then load data in it
2. And every time before loading data, the already existing data in the table has to be removed/cleaned. 



Solution


There is a feature Auto Create Table available in CopyData activity (if your destination(sink) is database) which helps you to create the table automatically if it doesn't exist already.

And you have option to execute script before copying the data, for that you need to provide the query against Pre-copy script. As the requirement is to clean the table before loading data, following Truncate  query is used

   
             @{concat('Truncate Table ',item().TableName,'_Parquet')}



Below is the pipeline with Copy data activity

auto create table and pre copy script

I created a pipeline and clicked on  validate and No errors were found.


Error/Issue



However when I triggered the pipeline for testing, got below an error

Cannot find the object "xxx" because it does not exist or you do not have permissions.


                   


Why it happened



ErrorCode=SqlOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=A database operation failed with the following error: 'Cannot find the object "Customer_Parquet" because it does not exist or you do not have permissions.',Source=,''Type=System.Data.SqlClient.SqlException,Message=Cannot find the object "Customer_Parquet" because it does not exist or you do not have permissions.,Source=.Net SqlClient Data Provider,SqlErrorNumber=4701,Class=16,ErrorCode=-2146232060,State=1,Errors=[{Class=16,Number=4701,State=1,Message=Cannot find the object "Customer_Parquet" because it does not exist or you do not have permissions.,},],'


 The error message is pointing to couple of possible reasons

    1. Not enough permission

    2. The table does not exist

For first possible reason checked the user permission (the one used in Linked service) - all was good and also Test connection on linked service was successful.

The second reason was true here, there was no table.

But, we have selected Auto create table option so did it not worked?

No it didn't reached to that step, the error was thrown by the step before it, which we selected by using Pre-copy script option. Where we are truncating the table.

As this was the first run, thus there was no Table and when truncate query was executed the object(Table) was not found.


What to do


Couple of ways to handle such scenarios

1. Have all the tables created already(remove auto create table) and then use only Pre-copy script to clean table before loading data

But here the requirement was to create tables automatically, so below approach can be used

2. For the first pipeline run, remove the Pre-copy script. After pipeline is run once, tables would be created. Now add the Truncate query in it.


If you have questions or suggestions, feel free to do in comments section below !!!


Do share if you find this helpful .......
 
                          Knowledge Sharing is Caring !!!!!!






Learn More about some more Azure Data Factory errors


Tags:

1 Comments

If you have any suggestions or questions or want to share something then please drop a comment

  1. AnonymousJuly 14, 2022 at 4:09 PM

    Hello, still table have 0 row showing

    ReplyDelete
Previous Post Next Post