John Liu Blog

Every drop counts

Linked Server DTC

When run query involve transactions using Linked Server, you will need to enable Network DTC Access for MS DTC transaction on both servers. If not configured, you may receive following error message: “The partner transaction manager has disabled its support for remote/network transactions” (if not enabled on remote server) “The transaction manager has disabled its support for remote/network transactions” (if not enabled on the local server) “The operation could not be performed because OLE DB provider “MSOLEDBSQL” for linked server “LinkedServer” was unable to begin a distributed transaction”

Posted by John Liu Friday, July 22, 2022

Power BI DAX handle duplicate values

When you have detailed data mixed with summary data, you can’t directly using the simple aggregation function in PowerBI to do aggregation on the summary data as the results will not be correct (due to duplicate data). However, you can create a measure to lead PowerBI to do aggregation correctly. For example, you have following data: Project Name Allocated Hours User Name Used Hours Project1 40 User1 2 Project1 40 User2 4 Project1 40 User3 3 Project2 100 User1 20 Project2 100 User4 50 Project2 100 User3 10 When you need to report on Project Allocated Hours and Total Used Hours, the Allocated Hours can’t be simply sum up.

Posted by John Liu Friday, July 22, 2022

Upgrade SQL SSISDB

When upgrade SSISDB, a few steps need to be considered when moving the SSISDB to a higher version on a new SQL instance. 1; Backup currently database master key BACKUP MASTER KEY TO FILE = 'file location/name' ENCRYPTION BY PASSWORD = 'your password for the backup key file' If you forgot the current password for the master key, you can reset the password via backup the master key (if not already have one) and then restore it with a new password

Posted by John Liu Tuesday, July 19, 2022

Power Automate extract email alias address

One can use Azure PowerAutomate to process files received via email. We can setup one central email account and create different email alias for different client where PowerAutomate only need to mornitor one email account. Howerver, PowerAutomate doesn’t (at the time of this article) expose the alias email address in the Office365 Outlook email action. One solution to address this challenge is to use Azure Function App to extract the alias email address from the email body using Regex.

Posted by John Liu Saturday, April 16, 2022

Power Automate file contents base64

When using Power Automate flow to upload a CSV file to SQL server, the base64 value in the flow will be converted to a UTF-8 binary value if the target column data type is varbinary, or the raw base64 string value if the column data type is varchar/nvarchar. However, when the original data contains unicode string value like Chinese character, the varbinary data type will not be able to be converted back to its original unicode text.

Posted by John Liu Tuesday, March 29, 2022