question

majesticaman-8869 avatar image
0 Votes"
majesticaman-8869 asked SathyamoorthyVijayakumar-MSFT commented

Issue with Lookup activity in Data Factory

I am facing issue while updating the Error message of a previous activity into a sql table using Look up activity . Below is the error message

Error:Failure happened on 'Source' side. ErrorCode=SqlOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=A database operation failed with the following error: 'Incorrect syntax near 'Source'. Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.',Source=,''Type=System.Data.SqlClient.SqlException,Message=Incorrect syntax near 'Source'. Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.,Source=.Net SqlClient Data Provider,SqlErrorNumber=102,Class=15,ErrorCode=-2146232060,State=1,Errors=[{Class=15,Number=102,State=1,Message=Incorrect syntax near 'Source'.,},{Class=15,Number=319,State=1,Message=Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.,},],'


I am Using the below expression to update the table with error message in lookup activity:

Update dbo.ADFPipelineLogs
set Status='Failed' ,
ErrorMessage='@{activity('1-Demo_data').Error.Message}'
Select getdate();


I am not using any CTE but error says so. Can anyone help

azure-data-factoryazure-monitor
· 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.

1 Answer

SathyamoorthyVijayakumar-MSFT avatar image
0 Votes"
SathyamoorthyVijayakumar-MSFT answered SathyamoorthyVijayakumar-MSFT commented

Hello @majesticaman-8869,

Welcome to the Microsoft Q&A platform.


I had done a quick repro at my end and was not able to repro the issue on a normal circumstance.

I did see that you are making use dynamic content. I did try using the dynamic content. I was not still provided with the error.

However, when the dynamic content contain single quote (') within the body.

Something like below :

139582-image.png

I was provided with the error as yours.

139535-image.png


If this is your case i.e. activity('1-Demo_data').Error.Message - has a string returned with single quotes (') in the output(dynamic content). You're likely to encounter the behavior.

The error is because - the Single Quote (') is used to denote string boundary and this is causing the error while execution. If you want to represent a single quote (') within the string - we are supposed denote using '' (two single quotes)

Since, quote(') was causing the issue, I replaced the quote(') in the given string with a null using the replace() function.

 This is 'with2' ---> This is with

Change at my end

 Update [SalesLT].[Address]
 set StateProvince='@{replace(variables('Error'),'''','')}'
 Select getdate();

Output :

139459-image.png

The below would be snippet change at your end.

 '@{replace(activity('1-Demo_data').Error.Message,'''','')}'

Note: Replacing the single quote (') with blank - the above snippet removes all the occurrence of the single quotes (') in the string.

If you want to retain the Single Quote in the Message string, you could use the below snippet :

    '@{replace(activity('1-Demo_data').Error.Message,'''','''''')}'

Hope this will help. Please let us know if any further queries.


  • Please don't forget to click on 130616-image.png or upvote 130671-image.png button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how

  • Want a reminder to come back and check responses? Here is how to subscribe to a notification

  • If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators






image.png (5.4 KiB)
image.png (9.8 KiB)
image.png (4.9 KiB)
· 2
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.

Hello @majesticaman-8869,

Just checking in to see if the above answer helped. If this answers your query, do click Accept Answer and Up-Vote for the same. And, if you have any further query do let us know.

0 Votes 0 ·

Hello @majesticaman-8869,

Following up to see if the above suggestion was helpful. And, if you have any further query do let us know.

0 Votes 0 ·