web
You’re offline. This is a read only version of the page.
close
Skip to main content
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Small and medium business | Business Central, N... / Power Automate - SQL C...
Small and medium business | Business Central, N...
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.

I have the same question (0)
  • 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.

  • Suggested answer
    JAngle Profile Picture
    JAngle 119 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

  • 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

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

News and Announcements

Join experts in person to explore low code and AI agents at the Power Platform Community Conference sponsored by Microsoft

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Abhilash Warrier – Community Spotlight

We are honored to recognize Abhilash Warrier as our Community Spotlight honoree for…

Congratulations to the September Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Small and medium business | Business Central, NAV, RMS

#1
Rishabh Kanaskar Profile Picture

Rishabh Kanaskar 4,327

#2
Sumit Singh Profile Picture

Sumit Singh 2,734

#3
Nimsara Jayathilaka. Profile Picture

Nimsara Jayathilaka. 2,599

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans