Monday, 2 July 2018

Microsoft Flow: Sql Server Table Names is not Visible in Flow




Recently came across a limitation/issue while creating a flow to export some data from Azure SQL Server. Flow does not show table in the list if it does not have a Primary Key, so we made a DateTime field Primary Key (bad practice anyway). But it was not showing in list of list of tables.

Then came across SQL Connector Documentation and found it's limitations there. Second point under limitations says that table must have an IDENTITY column.

Limitations

The triggers do have the following limitations:
  • It does not work for on-premises SQL Server
  • Table must have an IDENTITY column for the new row trigger
  • Table must have a ROWVERSION (a.k.a. TIMESTAMP) column for the modified row trigger


Then we changed our SQL table as below and it started showing up in the list of tables in flow:

CREATE TABLE [dbo].[abc_TriggerToRunExportUsingFlow](
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[TriggerOn] [datetime] NOT NULL,
[Description] [nchar](50) NULL,
PRIMARY KEY CLUSTERED 
(
[Id] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[abc_TriggerToRunExportUsingFlow] ADD  CONSTRAINT [DF_abc_TriggerToRunExportUsingFlow_TriggerOn]  DEFAULT (getdate()) FOR [TriggerOn]
GO
Premjit Singh
Attendite Ltd.
Twitter

4 comments :

  1. Very good content. I read your blog that was awesome, please doing continue daily post. Customer Relations Software in Indore

    ReplyDelete
  2. nice topic which you have choose.
    second is, the information which you have provided is better then other blog.
    so nice work keep it up. And thanks for sharing.
    Microsoft Dynamics AX Online Training

    ReplyDelete
  3. This is really a very good blog and thanks for sharing it with the community!Keep Posting Many Blogs Like this.
    crm software development company in chennai

    ReplyDelete