cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
Highlighted
Community Support
Community Support

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.

View solution in original post

10 REPLIES 10
Highlighted
Dual Super User II
Dual Super User II

Re: Connect to SQL database

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

Re: Connect to SQL database

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]

 

Highlighted
Community Support
Community Support

Re: Connect to SQL database

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.

 

 

Highlighted
Frequent Visitor

Re: Connect to SQL database

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

Highlighted
Community Support
Community Support

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.

Highlighted
Frequent Visitor

Re: Connect to SQL database

Hi @v-alzhan-msft ,

 

Attached flow config. flow.png

Highlighted
Frequent Visitor

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

Highlighted
Community Support
Community Support

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.

Highlighted
Frequent Visitor

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.

Highlighted
Community Support
Community Support

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.

View solution in original post

Helpful resources

Announcements
secondImage

August 2020 CYST Winners!

Check out the winners of the recent 'Can You Solve These?' community challenge!

thirdImage

Experience what's new for Power Automate

Join us for an in-depth look at the new Power Automate features and capabilities at the free Microsoft Business Applications Launch Event.

firstImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

Users online (4,088)