John Liu Blog

Every drop counts

SQL Server MCP

References: SQL AI Samples (Microsoft Azure Samples) MSSQL MCP (Aaronontheweb)

Posted by John Liu Sunday, November 2, 2025

Create embeddings in SQL2025

SQL2025 has the ability to use local ONNX model on the server to create embeddings. Following are steps for configuration. 1. If this feature is still developer preview feature, run following SQL scrit to enable preview features ALTER DATABASE SCOPED CONFIGURATION SET PREVIEW_FEATURES = ON; 2. Enable AI runtime on SQL2025 EXEC sp_execute 'external AI runtime enabled',1; RECONFIGURE WITH OVERRIDE; 3. Set up the ONNX runtime library Create a local folder, say C:\onnx_runtime, to store the runtime libarary files.

Posted by John Liu Sunday, October 26, 2025

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

SQL Query External Dta

Read text file contents as a single value: SELECT [BulkColumn] FROM OPENROWSET(BULK 'C:\TEMP\test.json', SINGLE_CLOB) AS Contents Read data from Excel: Option1: Using OPENROWSET. Note: if your column has data more than 255 characters, try to format the column as Text. Also try to change following registry key TypeGuessRows to 0 (default is 8), under path HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\16.0\Access Connectivity Engine\Engines\Excel (for 64-bit machine) or HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\Office\16.0\Access Connectivity Engine\Engines\Excel (for 32-bit machine). Restart of SQL instance required if change registry setting.

Posted by John Liu Wednesday, June 4, 2025

SQL Server Firewall

In Windows, you can use following Powershell script to explicitly enable remote access to your SQL Server instance (substitute the DisplayName and LocalPort accordingly). New-NetFirewallRule -DisplayName "SQLServer default instance" -Direction Inbound -LocalPort 1433 -Protocol TCP -Action Allow New-NetFirewallRule -DisplayName "SQLServer Browser service" -Direction Inbound -LocalPort 1434 -Protocol UDP -Action Allow You can find more information about ports used by various SQL Server services in this articals Configure the Windows Firewall to allow SQL Server access.

Posted by John Liu Wednesday, August 14, 2024