Email body string being cut short when passed to SQL

[Anonymous]

Hi community

I am writing a flow that uses the Get Email action to then Export Email and pass email metadata (To, From, Subject, Body) to a SQL database. However, I am finding that the body variable is being cut short and therefore isn't complete when it lands in SQL. Am I doing something wrong here?

This is the structure of the flow:

This is my compose action: length(string(outputs('Get_emails_(V3)_-_Search_our_inbox_for_the_email_sent_above')?['body']))

The character count in the email body is 47002 here.

Once I retrieve the value from SQL and count the character length, it shows 44,184 only:

Thanks!

[stephankline]

Hello @Anonymous ,

Thank you very much for including your breakdown of what you are seeing when trying to extract and export information from an email to your SQL database.  I completed a quick test run-through in my own environment, extracting just the body of an email using the Compose action. 

For my example:

I set an automatic trigger to watch for a new email from my test email account specifically and set a Compose Action to fire accordingly:

For the body of the test email I sent to trigger this flow, the body of the email stating "The body of this email is 38 characters long."

The resulting Raw Output from the Compose Action was significantly longer that 38 characters when all was said and done (expected):

Where the issue seems to occur may be what you have your Body Column set to for Data Type in your table, though it may take a side-by-side comparison of the Raw Output from your Flow and what is stored in the corresponding column.  I wasn't able to find much on documentation on storing HTML in SQL (everyone seems to go the opposite direction), but the consensus seems to be to set the Data Type to nvarchar(MAX).

The next course of action I would recommend to you at this point is to set up a similar flow, but have the output written to a table where the Data Type for Body is set to nvarchar(MAX) and compare between the 2 configurations.

[Anonymous]

Hi @stephankline 

Thanks for your response. However, I think we are comparing different numbers. While you are comparing the marked up version of the email (38 characters) vs. the exported version (HTML); I'm less interested in the marked up count and more interested in the HTML character count. The two numbers that I have (47002 vs 44184) should both be representative of the length of the HTML string. 

What seems to be happening on my end is - when I try to place this:

(outputs('Get_emails_(V3)_-_Search_our_inbox_for_the_email_sent_above')?['body']))

as an input to SQL action - Add a row, the character length that is actually parsed across seems to be different to when I am simply counting the length of the string. Hence leading me to believe that for some reason, the string is being cut short as the action writes to SQL.

My SQL column is already set to max character length - which should mean it allows up to a billion characters, according to my understanding.

Thanks for your help!