英文:
How Parameterize Truncating a table in Azure SQL DB within an Azure Data Factory Copy Activity
问题
我遵循了一个关于如何在Azure SQL DB中使用ADF截断表的SO问题。
我试图使用我的复制活动来模拟问题中的示例,如下所示:
我的源细节如下:
使用上述配置,我得到以下错误:
{
"errorCode": "2200",
"message": "ErrorCode=SqlOperationFailed,Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=A database operation failed with the following error: 'Cannot find the object \"targettable\" because it does not exist or you do not have permissions.',Source=,'',Type=System.Data.SqlClient.SqlException,Message=Cannot find the object \"targettable\" 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 \"targettable\" because it does not exist or you do not have permissions.,}],'"
"failureType": "UserError",
"target": "Copy From CRM to SQLDB",
"details": []
}
然而,如果我将架构和表名硬编码如下 TRUNCATE TABLE dbo.mytablename
,一切都将正常工作。
作为更新,我刚刚尝试了以下参数
TRUNCATE TableName@pipeline().parameters.TableName
但失败了。
英文:
I following an SO question on how to truncate a table in Azure SQL DB with ADF
https://stackoverflow.com/questions/69456500/how-to-replace-data-in-azure-sql-database-using-azure-data-factory
I am trying to emulate the sample in the question using my Copy Activity as follows:
My Source details are as follows:
With the above configuration I get the following error:
{
"errorCode": "2200",
"message": "ErrorCode=SqlOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=A database operation failed with the following error: 'Cannot find the object \"targettable\" because it does not exist or you do not have permissions.',Source=,''Type=System.Data.SqlClient.SqlException,Message=Cannot find the object \"targettable\" 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 \"targettable\" because it does not exist or you do not have permissions.,},],'",
"failureType": "UserError",
"target": "Copy From CRM to SQLDB",
"details": []
}
However, if I were to hardcode the schema and table name as follows TRUNCATE TABLE dbo.mytablename
everything would work fine.
By way of an update, I just tried with the following parameters
TRUNCATE TableName@pipeline().parameters.TableName
And it failed.
答案1
得分: 1
Message=A database operation failed with the following error: 'Cannot find the object "targettable" because it does not exist or you do not have permissions.
This error occurred because of this query TRUNCATE TABLE dbo.targettable
.
- Here
targettable
is a SQL database parameter and we cannot access its value inside the pipeline and also you have given it with the query. - It made the meaning of SQL query as to truncate the table named
targettable
which is not available in the database, and that's why it gave you the above error.
To resolve it, give the correct parameter in the query with string interpolation like below.
TRUNCATE table @{pipeline().parameters.TableName}
I have given the pipeline parameter as sample1
which is my table name.
Before Truncate:
After Truncate:
英文:
>Message=A database operation failed with the following error: 'Cannot find the object \"targettable\" because it does not exist or you do not have permissions.
This error occurred because of this query TRUNCATE TABLE dbo.targettable
.
- Here
targettable
is a SQL database parameter and we cannot access its value inside pipeline and also you have given it with query. - It made the meaning of SQL query as to truncate the table named
targettable
which is not available in the database and that's why it gave you the above error.
To resolve it give the correct parameter in the query with string interpolation like below.
TRUNCATE table @{pipeline().parameters.TableName}
I have given the pipeline parameter as sample1
which is my table name.
Before Truncate:
After Truncate:
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论