question

DebbieEdwards-9837 avatar image
0 Votes"
DebbieEdwards-9837 asked BalaMuraliKrishna-4688 commented

Data Factory Stored Procedure Error Cannot find the object

I have a stored procedure to truncate tables and it works absolutely fine in SQL Server

EXEC [staging].[USP_TruncateTables] @tableName = 'example', @SchematableName = 'staging.example';

So I can use this truncate Script for all the staging tables

However I set it up in Data Factory (And I have tried both ways. I have added it in the Copy Script
30877-precopyscript.jpg


And I also tried by removing the above and adding a Stored Procedure Activity

30846-spactivity.jpg

But both produce the same error message

"errorCode": "2402",
"message": "Execution fail against sql server. Sql error number: 1088. Error Message: Cannot find the object \"example\" because it does not exist or you do not have permissions.",
"failureType": "UserError",
"target": "Stored procedure1",
"details": []

I dont think there is any issues with the User and Role because I have all the EXEC SELECT UPDATE etc permissions required. I cannot currently get past this issue.

If anyone has any ideas that would be great

azure-data-factoryazure-sql-database
precopyscript.jpg (19.7 KiB)
spactivity.jpg (52.7 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

DebbieEdwards-9837 avatar image
0 Votes"
DebbieEdwards-9837 answered BalaMuraliKrishna-4688 commented

Sorted. It needed GRANT ALTER permissions

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi Debbie,

Could you please let us know where do we provide these permissions in ADF? I am facing the same issue. Looking forward to seeing a response from you. Thank you.

Regards,
Bala.

0 Votes 0 ·