How to handle SQLServer LSN (binary data type) in Logic App

Question

I'm hoping someone can help here; I've been banging my head against this problem half the day and can't seem to solve it!

I have a Logic App workflow that needs to retrieve the LSN values from a SQL Server change data capture (CDC) table and use them in a later query. The LSNs are data type 'binary(10)'.

If I let the LA simply retrieve the value from the SQL run query action, I see a string. If I try to use that string later in a SQL statement, it won't implicitly convert back to binary, and gives an incorrect value if I use CONVERT(binary(10), '<string form>', 0).

I've also tried converting to bigint in the first SQL query, but this returns incorrect values about half the time too!

Can anyone tell me how to handle the binary data type between SQL server and Azure LA succesfully, please?

Thanks in advance!

Answer 1

Hi @Jeff Thompson ,

Logic Apps works with and supports other content types, but might require that you manually get the message body by decoding the $content variable.

For example, suppose your logic app gets triggered by a request with the application/x-www-url-formencoded content type. To preserve all the data, the $content variable in the request body has a payload that's encoded as a base64 string:

CustomerName=Frank&Address=123+Avenue

For more details, please see:
https://learn.microsoft.com/en-us/azure/logic-apps/logic-apps-content-type#applicationxml-and-applica..

Best regards,
Niko

----------

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Thanks Niko. It turned out to be rather simpler, just took me a lot of experimenting.

It's this:

**SELECT CONVERT(varchar(max), <the varbinary LSN>, 1) AS LSNString FROM wherever;**  

That gives a hex string ('0x1234567890abcdef') that the LA is fine with; to use it in a SQL statement back, just needs a quick '@markus.bohland@hotmail.de ' to put the hex string, without any quotes around it, into the SQL statement - e.g.

"query": "@concat('SELECT * FROM cdc.fn_cdc_get_net_changes_dbo_Partner(', body('Get_start_LSN')?[0][0]?['StartLSN'], ', ', body('Get_end_LSN')?[0][0]?['EndLSN'], ',''all'');')"     

(pity that my select query in Get_start_LSN returns the value inside two single-element arrays; that caused some confusion!)

The key seems to have been converting to varchar(max) in format 1 rather than varchar(n) and not quoting the hex string when using it in a statement.

Jeff