[Automation] Using Power Automate with SQL Server (Primary keys that aren’t int)

Level of Difficulty: Beginner – Senior.

Are you in a situation where you’re using a SQL database and you’ve already designed, created and gone through all 700 iterations of the review? Primary keys already created of type uniqueidentifier with default value of NEWID(). Then Fred comes with his clever idea of adding some sort of automation workflow on top of the database – you know, like Power Automate?

So you go down a tangent of getting info together around Power Automate triggers and actions, specific to SQL Server. You come across the two triggers: When an item is created (V2) and when an item is modified (V2).

You decide on using SQL Server “When an item is created (V2)” as a trigger and Office 365 Outlook “Send an Email (V2)” as an action.

You go into Power Automate and you connect it up to your database but when you hit test and “I’ll perform the trigger action”, you wait… And wait… And wait… ? … ? … And wait. Nothing’s happening?

You do more research and you find that most people use a primary key of type int and it works for them. Now you are running ahead with everything that needs to change in order to get this to work. You think of workarounds like creating views, using a different trigger, using a different automation type, heck… Even using a different workflow tool. But wait! That’s not necessary.

Here’s what to do:

• Alter your table design in SQL Server Management Studio (SSMS)
• Add another row, named something like “PowerAutomateID”
  • Make the type int
  • Untick “Allow Null”
  • Scroll down in the table properties and set Identity Specification to Yes
• Save
• Go test your flow

There’s no need to make the new column a Primary Key as the Power Automate trigger looks for the column that has Identity Specification set. I had a web form built on top of my database and none of the CRUD (create, read, update and delete) functionality had to be changed. It all continued to work properly, even the Power Automate process.

This is definitely something I wish I knew when I first went on this journey a few weeks ago.

If you hadn’t come across this in your research, you would most likely have loved to have landed on the Microsoft Docs that explain some of the limitations.

Still not working or may you found a better way? Drop a comment below or send an email through to jacqui.jm77@gmail.com