John Liu Blog

Every drop counts

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

SSMS Default Query Template

Within SSMS, by default when click on New Query, we get a blank query window. If we want to add something to the window each time a new query window is opened, we can achieve this by editing the default template. The template file SQLFile.sql, (for example, for SSMS19) is located under: C:\Program Files (x86)\Microsoft SQL Server Management Studio 19\Common7\IDE\SqlWorkbenchProjectItems\Sql\ Whatever we put in this file, will then appear when open a new query window.

Posted by John Liu Monday, May 1, 2023

SSMS Extensions

With SSMS 19, when trying to install Poor Mans TSQL Formatter SSMS extension, it doesn’t appear working after install. This is due to the install didn’t create/copy the package file onto folder C:\Program Files (x86)\Microsoft SQL Server Management Studio 19\Extensions\Extensions. To make it work, manually copy the PoorMansTSqlFormatterSSMSPackage.pkgdef from the Extension folder for SSMS 18 and past into the Extension folder for SSMS 19. You may need to create the Extension folder if it doesn’t exist.

Posted by John Liu Friday, March 17, 2023