John Liu Blog

Every drop counts

SQL Server Scientific Notation

When we import numeric data into SQL server, sometime the source data might be in scientific notation format. SQL Server can’t automatically convert scientific notation value string to decimal or integer value and will result with implecite conversion error. To properly import the data, we need to convert the source string value to FLOAT or REAL data type first before convert to the target data type. SELECT CONVERT(DECIMAL(18,3),CONVERT(FLOAT,'1.2E-5'))

Posted by John Liu Sunday, September 7, 2025

Snowflake notes

To grant access to a role grant usage on warehouse compute_wh to role mytestrole; grant usage on database test to role mytestrole; grant usage on schema study to role mytestrole; --select on individual table grant select on table test.study.sales to role mytestrole; --full access to all existing tables grant select,insert,update,delete on all tables in schema test.study to role mytestrole; --automatically grant access to future tables grant select on future tables in schema test.

Posted by John Liu Saturday, February 15, 2025

Azure SQL DB call external API

With the introducing of sp_invoke_external_rest_endpoint in Azure SQL DB, it’s possible to directly calling the Azure OpenAI service within the database. Azure OpenAI is in the safe-listed Azure services. For other API service (like common OpenAI) that’s not in the safe-list, you will need to create a wrapper API in the Azure API Management Instance. To invoke external rest endpoint in Azure SQL DB, a few setup steps are required:

Posted by John Liu Saturday, September 9, 2023

SQL Server Run Different Version of Python

SQL Server Machine Learning Service comes with a default Phyton runtime. For SQL 2019, it’s Phyton 3.5. You might need to run your Phyton script against a different version of Phyton runtime. To configure another Phyton version runtime to be available to your SQL instance, do the following: Install Phyton 3.10 with options to install for all users Install required packages Grant required permissions to SQL Server LaunchPad service @ECHO OFF ECHO installing python "%~dp0python-3.

Posted by John Liu Thursday, August 17, 2023

SQL CLR

Start from SQL2017, CLR strict security is enabled by default in SQL, and treats SAFE and EXTERNAL_ACCESS assemblies as if they were marked UNSAFE. One can disable CLR stric security option but that’s not recommended. Microsoft recommends assembily be signed by a certificate or asymmetric key with corresponding login that has been granted UNSAFE ASSEMBLY permission in the master database. DBA can aslo add assembliy to a trusted list by using sys.

Posted by John Liu Friday, July 7, 2023