Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics NAV forum / Power Automate - SQL C...
Microsoft Dynamics NAV forum
Suggested answer

Power Automate - SQL Connection

(0) ShareShare
ReportReport
Posted on by Misaq Ferdosian

Hello,

I have established an SQL Connector connection from Power Automate to my Dynamics NAV 2017 10.0, but when trying to lookup a table name to use as a trigger (When an Item is created (V2)), I run into the following error:

Could not retrieve values. The dynamic invocation request failed with error: Unexpected error occurred when calling the ApiHubsRuntime API: 'System.Threading.Tasks.TaskCanceledException: A task was canceled. at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at Microsoft.Azure.ProcessSimple.Data.DataProviders.AzureResourceManagerDataProvider.<>c__DisplayClass52_1`2.<<CallAzureResourceManager>b__...

I know this is not a problem directly related to Dynamics NAV, but I thought it wouldn't hurt to ask and get answers from people who might have solved it. The Dynamics NAV is a Cloud database, where I only have db_datareader role.

My goal is to send out an email notification (instead of manually putting a note to alert the person in NAV) when a new Finished Production Order item has been added to the table when production has been completed. Workflows can be used, where a Custom Workflow event needs to be created, meaning I would have to contact our partner and get quoted heavily on this simple solution of getting an email notification, Any help or solution to my goal would be helpful!

Thanks.

Categories:
Jobs and Resources
Warehouse
Other
  • Misaq Ferdosian Profile Picture
    Misaq Ferdosian on at
    RE: Power Automate - SQL Connection

    Hi Josh,

    I didn't think that an on premise data gateway is needed as the Dynamics NAV database is an Azure database. If so, does it have to be installed on the Cloud Server? I read the following in FAQ: 'However, you might need a data gateway if your data sources are behind a firewall, require a VPN, or are on virtual networks.'

    Could you elaborate further on how to set this up (especially the Web Service part)? 'I’d use a recurring flow so you can call a web service across the working day. If you publish web services from NAV you can access them and bring data in that way.' I'm yet exploring the full functionality of Dynamics NAV.

    What about Posted Sales Shipment (Sales Shipment Header table)? I believe new entries (new items) are created for that, but yet the flow does not run.

    Cheers

  • Suggested answer
    JAngle Profile Picture
    JAngle 33,155 on at
    RE: Power Automate - SQL Connection

    Do you have an on premise data gateway established? docs.microsoft.com/.../service-gateway-onprem

    You will want to use the modify trigger as a finished production order is not new data. It is the same entry as the released order it just changes status.

    If you’re unable to get the SQL connector working an alternative could be the HTTP connector. I’d use a recurring flow so you can call a web service across the working day. If you publish web services from NAV you can access them and bring data in that way. This won’t be trigger based so you would need to define a logic based on last date modified most likely. If you do a GET you could write the values into a supporting table in a Sharepoint to know what has and has not been handled. Then create the email messages

  • Suggested answer
    Brian Wick Profile Picture
    Brian Wick on at
    RE: Power Automate - SQL Connection

    Hi Misaq,

    We currently do not have dedicated Dev support via the NAV/Dynamics 365 Business Central forums, but I wanted to provide you some additional resources to assist.  You might have a better shot at getting a developer/consultant to reply on one of these communities.

    www.yammer.com/dynamicsnavdev

    dynamicsuser.net/.../developers

    https://mibuso.com/

    I will open this up to the community in case they have something to add.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Anton Venter – Community Spotlight

Kudos to our October Community Star of the month!

Announcing Our 2024 Season 2 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Dynamics 365 Community Newsletter - September 2024

Check out the latest community news

more Community News

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 290,532 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 228,501 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans