Warm tip: This article is reproduced from serverfault.com, please click

Azure source Dynamics with parameter error

发布于 2020-11-27 10:26:59

I have a simple pipeline where I look up a date from an Azure SQL DB table, and then try pass this to an XML query for Dynamics.

The lookup activity succeeds, output looks fine, I get an error message for the XML query which I have been trying to resolve for a long time without any success so far.

This is the error message I get

And this is the XML query I am using in the source query:

<fetch>
<entity name="account" >
<all-attributes/>
    <filter type="and">
      <condition attribute="modifiedon" operator="on-or-after" value="@{activity('LookupAccountSQL').output.firstRow.modate}" />
    </filter>
  </entity>

The lookup activity name is LookupAccountSQL, and the SQL query is simple as below: (also debug runs successfully for this activity):

SELECT CAST(MAX(modifiedon)) as modate FROM dbo.account

Any help is greatly appreciated!

Questioner
Matyas
Viewed
0
Matyas 2020-12-04 22:20:07

Thanks to the helpful comments below my question I figured out the problem. The lookup activity returns a datetime regardless of the fact that I use cast in the SQL query.

The solution is to put a formatDateTime() function on top in the Fetchxml.

<fetch>
  <entity name="account" >
   <all-attributes/>
    <filter type="and">
     <condition attribute="modifiedon" operator="on-or-after" value="@{formatDateTime(activity('LookupAccountSQL').output.firstRow.modate,'yyyy-MM-dd')}" />
    </filter>
  </entity>