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.

Categories:

Using flows

I have the same question (0)

All responses (14)

Answers (1)

Sort by

Pstork1 69,246 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

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?

Was this reply helpful? Yes No
ntripathi 23 on at

Like (0)

Report
Copy link

Link copied!

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]

Was this reply helpful? Yes No
v-alzhan-msft Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
ntripathi 23 on at

Like (0)

Report
Copy link

Link copied!

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

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

Thanks.

Was this reply helpful? Yes No
v-alzhan-msft Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
ntripathi 23 on at

Like (0)

Report
Copy link

Link copied!

Hi @v-alzhan-msft ,

Attached flow config.

Was this reply helpful? Yes No
ntripathi 23 on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
v-alzhan-msft Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
ntripathi 23 on at

Like (0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
Verified answer

v-alzhan-msft Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No