4

I have a Microsoft Flow which i want to insert a record into a Azure SQL table, I am having trouble inserting a GUID which is generated from the trigger.

Screnshot

The field I am mapping from appears to be a GUID format when i inspect it however when the flow executes it looks as though the GUID is being converted to a string before being pushed to SQL, which is causing the below conversion error on execution (the destination field in uniqueidentifier type):

{
"status": 400,
"message": "Microsoft SQL: Conversion failed when converting from a character 
string to uniqueidentifier.\r\nclientRequestId: c3c1acb7-ca1a-443a-ab66- 
4a42ad0d28b1",
"source": "sql-ae.azconn-ae.p.azurewebsites.net"
}

Example of source field output: "bookableresourcebookingid": "bd403e0b-0337-e911-a86b-000d3ad11148"

I have had a look through the documentation here for GUID conversions however they all seem to output a new GUID as a string instead of doing what i want which is to input the GUID ID into the SQL table as a unique identifier.

How can I input this GUID ID field into the SQL destination unique identifier field/ convert the GUID string to a unique identifier?

Alexander Volok
  • 5,630
  • 3
  • 17
  • 33
AJ_
  • 93
  • 7

1 Answers1

0

Indeed, GUID values that you shared have correct format and Azure SQL should transparently convert them back from a string to uniqueidentifier. However, it does not happen for some reason.

I suggest to profile a destination azure database and capture DML operations on a table where inserts fail.

Unfortunately, regular SQL Profiler will not work in this case, but SQL Auditing can be configured to write INSERTS into XEL files on BLOB storage. So you should be able to see exact INSERT statement that fails, just like this:

enter image description here

By itself, SQL audit is a big topic, but here is an example of how it can be achieved: Azure SQL database auditing using Blob Storage

Alexander Volok
  • 5,630
  • 3
  • 17
  • 33
  • Thats unfortunate, i was hoping i was just missing the code to convert to a unique identifier but it seems microsoft doesnt play nice with microsoft. Because this is the primary key it will fail every insert with the audit recommendation above, only option I can see is to create a staging table with a varchar type field to insert the GUID into as a string, then run a Stored Proc in Azure to import to the table I need and convert the String to GUID in the process. – AJ_ Feb 27 '19 at 23:56