0

I have an On-Prem SQL which I am accessing in my Logic App using On-Prem Data Gateway. I have created a table in my SQL database as per below:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Temp_Emp_CC](
    [Timestamp] [timestamp] NOT NULL,
    [EmployeeCode] [int] IDENTITY(1,1) NOT NULL,
    [CostCentreCode] [varchar](50) NOT NULL,
 CONSTRAINT [PK_Temp_Emp_CC] PRIMARY KEY CLUSTERED 
(
    [EmployeeCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Data Filegroup 1]
) ON [Data Filegroup 1]
GO

From a Logic App, I am trying to insert rows using "Insert row (V2)" action but it does not populate any tables:

enter image description here

If I try any other SQL action in a Logic App, for example, "Get rows (V2)" or "Execute a stored procedure (V2)" they work fine for the same connection/database server/database. So, my assumption is I'm doing right things with creating a connection!

From some forum I have learnt that a table needs to have an Identity Column and a Timestamp column, which I have added already; however issue still persist.

enter image description here

Victor McIntyre
  • 93
  • 1
  • 10
  • 3
    Do you see any error messages? Where's the part where you actually try and insert an EmployeeCode and CostCenter? You defined both as `NOT NULL`, so unless you supply values for both, the insert would fail. Have you tested manually inserting values using the same connection credentials in order to ensure the account you are using has INSERT permissions? – SchmitzIT Dec 14 '21 at 07:57
  • I agree - if it's not inserting rows, either you should see an error, or you'r not inserting where you think you are. – Nick.Mc Dec 14 '21 at 11:33
  • Yes, the account I am using has INSERT permission on that particular table; as well as it is member of db_owner Database role. My understanding is, after we have created a connection and defined the Server name and Database name in the Insert a row (V2) action in Logic App, it should populate the Table name(s) to select from and then provide the Row items to insert. But that Table name isn't populating any tables. – Victor McIntyre Dec 14 '21 at 19:26
  • @VictorMcIntyre, I've tested this with a similar setup, and I am getting a list with the table names. Try using 'Enter custom value' and run your Logic App - it'll either run successfully or it might give you an error pointing at the issue. – 10p Dec 15 '21 at 09:30

1 Answers1

0

I think I have figured this out. Although there is still a question but a separate one.

The SQL database I was trying to access has so many tables - at least 100+ of them. I didn't want to write to all of them but only a few. So, I have restricted my SQL account's access to those specific tables only, and they populated fine in my Logic App.

The existing question is, does Logic App/APIConnection/On-Prem Data Gateway have any limit on accessing the tables?

Anyway, the main issue is resolved so I will close this one now. Thanks all your helps. :-)

Victor McIntyre
  • 93
  • 1
  • 10