cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ntripathi
Frequent Visitor

Connect to SQL database

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.

1 ACCEPTED SOLUTION

Accepted Solutions

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.

View solution in original post

14 REPLIES 14
Pstork1
Dual Super User
Dual Super User

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?



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

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]

 

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.

 

 

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

 

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

 

Thanks.

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.

Hi @v-alzhan-msft ,

 

Attached flow config. flow.png

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

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.

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.

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
Frequent Visitor

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,

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.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

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

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.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Helpful resources

Announcements
Power Automate News & Announcements

Power Automate News & Announcements

Keep up to date with current events and community announcements in the Power Automate community.

Community Calls Conversations

Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

Power Automate Community Blog

Power Automate Community Blog

Check out the latest Community Blog from the community!

Users online (4,408)