Answered

Connect to SQL database

(0) Share

Report

Posted on by ntripathi

I am trying to connect SQL database with my flow. I am performing operations on the table view. I am able to perform insert operation successfully but in case of update I am getting error "The specified table has no primary key. Update and delete operations are not supported." I have dully checked for the Primary key, it is defined in the table. My PK was set on auto increment. I tried to create a PK without auto increment in another table and created another view and new flow but still for update I got same error.

Kindly help.

All responses (14)

Answers (1)

Pstork1 65,997 Most Valuable Professional on at

Like (0)

Report

Re: Connect to SQL database

If the VM is running on a local server in your network then you will need an on-premises gateway. If the VM is running in Azure, you will still need an on-premises gateway. The only way you can connect directly is if your database is running in the Azure SQL database function, not a VM.
arbitrary 23 on at

Like (0)

Report

Re: Connect to SQL database

Thanks @Pstork1

So can I connect my SQL Server on my VM to Azure?

Then via that connection connect to the Power Automate SQL connector?

Please advise
Pstork1 65,997 Most Valuable Professional on at

Like (1)

Report

Re: Connect to SQL database

To connect to a SQL server on an Azure VM you will need an on-premises gateway. You can connect without the gateway to an Azure SQL database, but not to a SQL server on a VM.
arbitrary 23 on at

Like (0)

Report

Re: Connect to SQL database

Hi @ntripathi

May I ask how you connected to SQL?

Is it with the on-premises data gateway or do you have a direct connection?

I want to connect my power automate to our SQL server sitting on a VM.

Please advise if you have a moment.

Regards,
Verified answer

v-alzhan-msft on at

Like (0)

Report

Re: Connect to SQL database

Hi @ntripathi ，

Sorry for I'm afraid that the requirement couldn't be achieved in Microsoft flow currently, as I have mentioned above, you need to know the table name and the unique id of the row when use the Update row action.

Best Regards,

Alice

Community Support Team _ Alice Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
ntripathi 23 on at

Like (0)

Report

Re: Connect to SQL database

Hello @v-alzhan-msft ,

I need to perform operations on a table view. The view name does not appear automatically in the dropdown. So I provide the custom value for the column. And then it populates the view columns in the flow action.

Please let me know if there should be other way of doing above

Thanks.
v-alzhan-msft on at

Like (0)

Report

Re: Connect to SQL database

Hi @ntripathi ,

You need to know the special row id when update the row in the table, and you should choose the table from the drop down list instead of enter the name manually.

Best Regards,

Alice

Community Support Team _ Alice Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
ntripathi 23 on at

Like (0)

Report

Re: Connect to SQL database

Hi @v-alzhan-msft ,

also find the below Update query from flow :

{
    "inputs": {
        "host": {
            "connectionName": "shared_sql_1",
            "operationId": "PatchItem_V2",
            "apiId": "/providers/Microsoft.PowerApps/apis/shared_sql"
        },
        "parameters": {
            "server": "ServerName",
            "database": "DBName",
            "table": "ViewName",
            "id": "@triggerOutputs()?['body/ID']",
            "item/Created_x0020_by": "@triggerOutputs()?['body/Author/DisplayName']",
            "item/Modified_x0020_by": "@triggerOutputs()?['body/Editor/DisplayName']",
            "item/Col1": "@triggerOutputs()?['body/Col1']",
            "item/Col2": "@triggerOutputs()?['body/Col2']",
            "item/Col3": "@triggerOutputs()?['body/Col3']"
        },
        "authentication": "@parameters('$authentication')"
    }
}
ntripathi 23 on at

Like (0)

Report

Re: Connect to SQL database

Hi @v-alzhan-msft ,

Attached flow config.
v-alzhan-msft on at

Like (0)

Report

Re: Connect to SQL database

Hi @ntripathi ,

Please share the screenshot of the configuration of the flow.

Best Regards,

Alice

Community Support Team _ Alice Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.