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

Rohit Kulkarni 6 Reputation points
2021-02-15T14:04:11.307+00:00

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

Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
9,095 questions
0 comments No comments
{count} votes

6 answers

Sort by: Most helpful
  1. Nasreen Akter 10,711 Reputation points
    2021-02-15T19:13:35.843+00:00

    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!

    1 person found this answer helpful.
    0 comments No comments

  2. Rohit Kulkarni 6 Reputation points
    2021-02-15T19:42:33.243+00:00

    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

    0 comments No comments

  3. Rohit Kulkarni 6 Reputation points
    2021-02-15T20:06:41.523+00:00

    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

    0 comments No comments

  4. Rohit Kulkarni 6 Reputation points
    2021-02-16T18:45:14.737+00:00

    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.68697-error.jpgDataTransfer.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

    0 comments No comments

  5. HimanshuSinha-msft 19,281 Reputation points Microsoft Employee
    2021-02-24T20:52:09.023+00:00

    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

    0 comments No comments