Removes data from the pool for all specific Name data from parameter

Question

Hello Team,

I have created a pipeline to remove a specific rows from the Sql Pool database. The output is coming perfectly. But in the Pipeline i am getting error.

The query is simple delete statement

The lookup activity I have used as "Query" in "Use query" option

delete FROM TableName
where FieldName1= '@{pipeline().parameters.FieldName1}'
and FieldName2='@{pipeline().parameters.FieldName2}'

Failure happened on 'Source' side. ErrorCode=SqlInvalidDbQueryString,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=The specified SQL Query is not valid. It could be caused by that the query doesn't return any data. Invalid query: 'delete FROM Tablename where FieldName1= 'ABC' and FieldName2='003742' ',Source=Microsoft.DataTransfer.ClientLibrary,'

Thanks
RK

Answer 1

Hi @Rohit Kulkarni ,

You can't do DELETE operation in the Lookup Query. You can do DELETE by using a Store Procedure in the CopyActivity or using SINK in the DataFlow. Thanks!

----------

If the response helps, please "Accept as answer" and "Upvote" it. Thanks!

Answer 2

But in SQL Pool DB.The stored Procedure is not supported. While trying to create a procedure in SQL Pool DB.I got the message saying :

CREATE or ALTER PROCEDURE statement uses syntax or features that are not supported in SQL Server PDW.

Please advise.

Thanks
RK

Answer 3

But i need to delete the few rows after passing the parameter on the fly in the pipeline.

Even i tried the copy Activity also in pipeline. Same issue. The output is coming perfectly but the error is coming in pipeline .

Message": "Failure happened on 'Source' side. ErrorCode=SqlInvalidDbQueryString,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=The specified SQL Query is not valid. It could be caused by that the query doesn't return any data. Invalid query: 'delete FROM TableName where Fieldname1='0000013' ',Source=Microsoft.DataTransfer.ClientLibrary,'",

Thanks
RK

Answer 4

Today i have created a stored procedure and used the copy activity and still i am getting the below error :

ErrorCode=InvalidParameter,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=The value of the property 'Fieldname' is invalid: 'Value is required property of stored procedure parameters Parameter name: Fieldname'.,Source=,''Type=System.ArgumentException,Message=Value is required property of stored procedure parameters Parameter name: Fieldname,Source=Microsoft.DataTransfer.ClientLibrary

If you refer the below link :

https://social.msdn.microsoft.com/Forums/en-US/1aa0f3d1-2fb4-4769-b2dd-98fc7f230760/copy-activity-sink-stored-procedure-parameter-value-ignored?forum=AzureDataFactory

I want to know whether it is a Bug or error.If its bug then how it can be resolved.

Regards
RK

Answer 5

Hello @Rohit Kulkarni ,

As called out before by @Nasreen Akter , lookup expect the dataset in return and so you were getting the error which you initially encountered , but it will work wih SQL procedure as I have tested that and sharing the details below .

I created a dummy table

CREATE TABLE [dbo].[t2]  
(  
	[col1] [int] NULL,  
	[col2] [varchar](100) NULL  
)  

inserted some dummy data

INSERT INTO [dbo].[t2] values (1,2)  
INSERT INTO [dbo].[t2] values (2,2)  
INSERT INTO [dbo].[t2] values (3,2)  
INSERT INTO [dbo].[t2] values (4,2)  

Created a proc which takes ID as a parameter , delete the record and send the details of the record which was delete

CREATE PROC DELETE_proc_test   
@id int   
AS   
IF OBJECT_ID('tempdb..#sometable') IS NOT NULL DROP TABLE #sometable  
select * into #sometable from [dbo].[t2]  where 1 =2   
INSERT INTO #sometable   
SELECT * from [dbo].[t2] where col1 = @id  
delete  from [dbo].[t2] where col1 = @id  
-- send the records deleted back   
SELECT * from #sometable  

Setup the copy activity , please pay special attention as to the import parameter . Also as copy activity expects a sink i used a blob as sink . you can use anything . The below animation will help you understand it better . The proc will also work using lookup .

https://pasteboard.co/JPT8uvD.png

Let me know how it goes .

Thanks
Himanshu