Connect to SQL database

[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.

[Pstork1]

Can you show us the definition of the Primary Key column in SQL?  That's usually the issue if you can't update items in SQL.  Also, do you have any SQL triggers defined on the table?

[ntripathi]

No I do not have any trigger in the SQL Table.

Below is the Primary key and Foreign Key schema:

[ColumnName] [int] IDENTITY(1,1) NOT NULL

CONSTRAINT [PK_keyName] PRIMARY KEY CLUSTERED

([ColumnName] ASC)

WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

ALTER TABLE [Table1] WITH CHECK ADD CONSTRAINT [FK_FKName] FOREIGN KEY([table2Column]) REFERENCES [Table2] ([table2Column])

ALTER TABLE [Table1] CHECK CONSTRAINT [FK_FKName]

[v-alzhan-msft]

Hi @ntripathi ,

Could you please share the screenshot of the configuration of the flow?

Could you please share the run history of the flow when it has run failed?

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]

@v-alzhan-msft  Kindly find the error screenshot attached.

It is a simple flow that has one insert and one update statement.

Thanks.

[v-alzhan-msft]

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.

[ntripathi]

Hi @v-alzhan-msft ,

Attached flow config.

[ntripathi]

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')"
    }
}

[v-alzhan-msft]

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]

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 · Accepted solution]

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.

[arbitrary]

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,

[Pstork1]

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]

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]

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.