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

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

SQL Server Replication Setup

To setup SQL Server transactional replication, the publisher needs to be Standard or Enterprise edition. Subscriber can be any edition (except SQL Server Compact), but will require standard or Enterprise edition as well if configure merge (bi-directional) replication. We will need to create some local Windows accounts to run replication agents: Agent Location Account name Snapshot Agent Publisher <server name>\repl_snapshot Log Reader Agnet Publisher <server name>\repl_logreader Distribution Agnet Publisher/subscriber <server name>\repl_distribution Merge Agnet Publisher/subscriber <server name>\repl_merge On Publisher machine, create a file share for the snapshot folder.

Posted by John Liu Thursday, July 25, 2024

SQL Playground Setup

This post descript the steps to setup a SQL Server failover cluster playground environment. Create a base server template VM of Windows Server 2022, with features .Net Framwork 3.5.1, Failover Clustering, and Multipath I/O installed via the Server Manager. Assign 4 Network Adapters, with the first adapter using NAT. Once the template server setup is done, run sysprep from cmd. The options for sysprep should be set to “Enter System Out-of-Box Experience (OOBE)” and the checkbox for Generalize should be checked, and set Shutdown option as Shutdown.

Posted by John Liu Saturday, October 14, 2023

PowerShell export data to file

This example PowerShell script exports data from SQL table into files and then zip them together. Clear-Host function ExportDataToFile { param( [string]$instancename, [string]$dbname, [string]$flowname, [string]$clientname, [string]$filepath ) $guid = @() $guid_replace = @() if ([string]::IsNullOrEmpty($filepath)) { throw "filepath is rquired but not specified!" } if ($filepath -match "\\$") { $filepath = $filepath.Substring(0,$filepath.Length-1) } #connection to SQL $SqlConnection = New-Object System.Data.SqlClient.SqlConnection $SqlConnection.ConnectionString = "Server=" + $instancename + ";Database=" + $dbname + ";Integrated Security=True" $SqlCmd = New-Object System.

Posted by John Liu Saturday, July 1, 2023