MS SQL Server

2026-04-30T07:27:00.000-07:00

Simulate Slow Typing with PowerShell: Type SQL Code Over Extended Periods

Introduction

Have you ever needed to simulate slow, character-by-character typing in Windows? Whether you're demonstrating code on a remote desktop, creating training videos, or just need to type slowly for testing purposes, this PowerShell script is perfect for the job.

This script uses the Windows SendKeys API to simulate keyboard input and can type either generated SQL comments or code from a file over a specified time period (default: 30 minutes).

Features

Type SQL code from a file or generate comment lines
Customizable typing duration (default: 30 minutes)
Automatic 10-second countdown before typing starts
Works with remote desktop sessions
Launches on-screen keyboard for visual reference
Real-time progress tracking
Handles special characters gracefully

Prerequisites

Windows PC with PowerShell
.NET Framework (included with Windows)
Target application (SSMS, Notepad, VS Code, etc.)

How It Works

The script launches the Windows on-screen keyboard
You have 10 seconds to click on your target window and position the cursor
After the countdown, the script automatically starts typing
Each character is sent with a 50ms delay
Lines are separated by configurable delays to spread typing over the desired time period

Installation & Setup

Step 1: Create the Script File

Open Notepad or your favorite text editor
Copy the entire script code (see below)
Save the file as SlowTypeScriptWithTargetWindow.ps1 in a folder (e.g., C:\Users\YourUsername\)

Step 2: Enable PowerShell Execution

Before running the script, you need to allow PowerShell to execute scripts. Open PowerShell as Administrator and run:

Set-ExecutionPolicy -ExecutionPolicy Bypass -Scope CurrentUser

Usage

Option 1: Generate 10,000 Comment Lines (Default)

Open PowerShell and navigate to the script folder, then run:

.\SlowTypeScriptWithTargetWindow.ps1

Option 2: Type SQL Code from a File

.\SlowTypeScriptWithTargetWindow.ps1 -FilePath "C:\path\to\your\file.sql"

Option 3: Customize Total Time

.\SlowTypeScriptWithTargetWindow.ps1 -TotalMinutes 60

Option 4: Combine Options

.\SlowTypeScriptWithTargetWindow.ps1 -FilePath "C:\path\to\your\file.sql" -TotalMinutes 45

Step-by-Step Execution Guide

Open PowerShell in the directory where you saved the script
Run the command with your desired parameters
The script will display:
- Configuration details (total lines, time, delay per line)
- A 10-second countdown
During the countdown:
- Click on your target window (SSMS, Notepad, etc.)
- Position your cursor where you want the text to appear
After countdown completes:
- Typing starts automatically
- A progress bar shows real-time status
- Do NOT click away from the target window
When complete:
- Summary statistics are displayed
- Total lines typed and elapsed time

Troubleshooting

Issue: "Keyword delimiter is missing" or "Group delimiters are not balanced"

These are expected for certain special characters
The script handles them gracefully and continues typing
The text will still appear correctly in your target window

Issue: Script won't run

Ensure you've set the execution policy: Set-ExecutionPolicy -ExecutionPolicy Bypass -Scope CurrentUser
Run PowerShell as Administrator

Issue: Text not appearing in target window

Make sure the target window is in focus when typing starts
The on-screen keyboard should be visible (it's just for reference)
Check that you're using a compatible application (SSMS, Notepad, VS Code, etc.)

Complete Script Code

Copy the code below and save it as SlowTypeScriptWithTargetWindow.ps1


# PowerShell Script to type slowly using on-screen keyboard
# Can type SQL code from a file or generate comment lines
# User has 10 seconds to position cursor before typing starts

param(
    [string]$FilePath = "",
    [int]$TotalMinutes = 30,
    [int]$LineCount = 10000
)

# Add required assemblies
Add-Type -AssemblyName System.Windows.Forms
Add-Type -AssemblyName System.Drawing

# Calculate delay per line in milliseconds
$totalMilliseconds = $TotalMinutes * 60 * 1000
$delayPerLine = $totalMilliseconds / $LineCount

# Initialize code lines array
$codeLines = @()

# Check if file path is provided
if ($FilePath -and (Test-Path $FilePath)) {
    Write-Host "Reading SQL code from file: $FilePath" -ForegroundColor Cyan
    $codeLines = @(Get-Content -Path $FilePath)
    $LineCount = $codeLines.Count
    Write-Host "Loaded $LineCount lines from file" -ForegroundColor Green
}
else {
    # Generate SQL comment lines
    Write-Host "Generating $LineCount SQL comment lines..." -ForegroundColor Cyan
    for ($i = 1; $i -le $LineCount; $i++) {
        $codeLines += "-- This is a SQL comment line $i"
    }
    Write-Host "Generated $LineCount comment lines" -ForegroundColor Green
}

# Recalculate delay per line based on actual line count
$delayPerLine = $totalMilliseconds / $codeLines.Count

# Launch on-screen keyboard
Write-Host "Launching on-screen keyboard..." -ForegroundColor Cyan
Start-Process "osk.exe"
Start-Sleep -Seconds 2

# Show instructions
Write-Host ""
Write-Host "========================================" -ForegroundColor Green
Write-Host "SLOW TYPE SCRIPT - READY TO START" -ForegroundColor Green
Write-Host "========================================" -ForegroundColor Green
Write-Host ""
Write-Host "Configuration:" -ForegroundColor Yellow
Write-Host "  Total lines to type: $($codeLines.Count)" -ForegroundColor Yellow
Write-Host "  Total time: $TotalMinutes minutes" -ForegroundColor Yellow
Write-Host "  Delay per line: $([math]::Round($delayPerLine / 1000, 2)) seconds" -ForegroundColor Yellow
if ($FilePath -and (Test-Path $FilePath)) {
    Write-Host "  Source: File ($FilePath)" -ForegroundColor Yellow
}
else {
    Write-Host "  Source: Generated SQL comments" -ForegroundColor Yellow
}
Write-Host ""
Write-Host "Instructions:" -ForegroundColor Cyan
Write-Host "  1. Click on your target window (SSMS, Notepad, etc.)" -ForegroundColor Cyan
Write-Host "  2. Position your cursor where you want to type" -ForegroundColor Cyan
Write-Host "  3. Typing will start automatically in 10 seconds" -ForegroundColor Cyan
Write-Host ""
Write-Host "Typing will start in 10 seconds..." -ForegroundColor Green

# Wait 10 seconds before starting
for ($i = 10; $i -gt 0; $i--) {
    Write-Host "Starting in $i seconds..." -ForegroundColor Yellow
    Start-Sleep -Seconds 1
}

Write-Host ""
Write-Host "Starting typing now..." -ForegroundColor Green
Write-Host "Do NOT click away from the target window!" -ForegroundColor Yellow
Write-Host ""

# Type each line with delay
$lineNumber = 0
$startTime = Get-Date

foreach ($line in $codeLines) {
    $lineNumber++
    $elapsedSeconds = ((Get-Date) - $startTime).TotalSeconds
    $estimatedTotalSeconds = $TotalMinutes * 60
    $percentComplete = ($elapsedSeconds / $estimatedTotalSeconds) * 100
    
    Write-Progress -Activity "Typing Code" `
                   -Status "Line $lineNumber of $($codeLines.Count) | Elapsed: $([math]::Round($elapsedSeconds, 0))s / $estimatedTotalSeconds`s" `
                   -PercentComplete $percentComplete
    
    # Type the line character by character
    foreach ($char in $line.ToCharArray()) {
        try {
            # Escape special characters for SendKeys
            $charToSend = $char
            switch ($char) {
                '{' { $charToSend = '{{}}' }
                '}' { $charToSend = '{{}}' }
                '+' { $charToSend = '{+}' }
                '^' { $charToSend = '{^}' }
                '%' { $charToSend = '{%}' }
                '~' { $charToSend = '{~}' }
                '(' { $charToSend = '('; [System.Windows.Forms.SendKeys]::SendWait($charToSend); Start-Sleep -Milliseconds 50; continue }
                ')' { $charToSend = ')'; [System.Windows.Forms.SendKeys]::SendWait($charToSend); Start-Sleep -Milliseconds 50; continue }
                '[' { $charToSend = '['; [System.Windows.Forms.SendKeys]::SendWait($charToSend); Start-Sleep -Milliseconds 50; continue }
                ']' { $charToSend = ']'; [System.Windows.Forms.SendKeys]::SendWait($charToSend); Start-Sleep -Milliseconds 50; continue }
            }
            [System.Windows.Forms.SendKeys]::SendWait($charToSend)
        }
        catch {
            # Skip problematic characters and continue
        }
        Start-Sleep -Milliseconds 50
    }
    
    # Press Enter to go to next line
    [System.Windows.Forms.SendKeys]::SendWait("{ENTER}")
    
    # Wait before next line
    Start-Sleep -Milliseconds $delayPerLine
}

$totalElapsedSeconds = ((Get-Date) - $startTime).TotalSeconds

Write-Host ""
Write-Host "========================================" -ForegroundColor Green
Write-Host "TYPING COMPLETED!" -ForegroundColor Green
Write-Host "========================================" -ForegroundColor Green
Write-Host ""
Write-Host "Summary:" -ForegroundColor Yellow
Write-Host "  Total lines typed: $lineNumber" -ForegroundColor Yellow
Write-Host "  Total time elapsed: $([math]::Round($totalElapsedSeconds / 60, 2)) minutes" -ForegroundColor Yellow
Write-Host "  Expected time: $TotalMinutes minutes" -ForegroundColor Yellow
Write-Host ""

Conclusion

This PowerShell script is a powerful tool for simulating slow typing in Windows. Whether you're creating training content, demonstrating code on remote desktops, or testing applications, it provides a flexible and easy-to-use solution.

Setting Up a Standby Reporting Database in SQL Server Express

2025-11-25T08:48:00.000-08:00

Objective and Setup

You want a read‑only reporting copy (test_reporting) of a primary database (test_prod) on the same SQL Server Express instance. You’ll periodically restore transaction log backups from test_prod to test_reporting so reporting users can query between restores. We’ll do this with RESTORE … WITH STANDBY.

Prerequisites and Notes

SQL Server Express is fine; it lacks SQL Agent, so scheduling will be manual or via Windows Task Scheduler + sqlcmd.
Ensure you have directories for data, log, backups, and the standby undo file:
- Example paths:
  - D:\Data, E:\Logs
  - D:\Backups
  - D:\StandbyUndo
Use your actual drive letters/paths based on your server.

Step 1: Create the `test_prod` Database

Run in SSMS (or sqlcmd):

-- Create test_prod with explicit file locations
CREATE DATABASE [test_prod]
ON PRIMARY (
    NAME = N'test_prod_data',
    FILENAME = N'D:\Data\test_prod.mdf',
    SIZE = 100MB, FILEGROWTH = 50MB
)
LOG ON (
    NAME = N'test_prod_log',
    FILENAME = N'E:\Logs\test_prod.ldf',
    SIZE = 64MB, FILEGROWTH = 32MB
);
GO

-- Set FULL recovery model to enable log backups
ALTER DATABASE [test_prod] SET RECOVERY FULL;
GO

Step 2: Seed Some Sample Data (Optional but Useful)

USE [test_prod];
GO

CREATE TABLE dbo.Sales (
    SalesId INT IDENTITY(1,1) PRIMARY KEY,
    OrderDate DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
    Customer NVARCHAR(100) NOT NULL,
    Amount DECIMAL(18,2) NOT NULL
);
GO

INSERT INTO dbo.Sales (Customer, Amount)
VALUES (N'Acme Inc', 1250.00), (N'Globex', 980.75), (N'Initech', 212.00);
GO

Step 3: Take an Initial Full Backup of `test_prod`

BACKUP DATABASE [test_prod]
TO DISK = N'D:\Backups\test_prod_full_2025-11-25.bak'
WITH INIT, CHECKSUM, STATS = 5;
GO

Step 4: Create the Reporting Database via Restore (NORECOVERY)

We’ll restore the full backup to a new database name (test_reporting) and place files in separate locations.

-- Restore full backup, leave database non-recovered (ready for logs)
RESTORE DATABASE [test_reporting]
FROM DISK = N'D:\Backups\test_prod_full_2025-11-25.bak'
WITH MOVE N'test_prod_data' TO N'D:\Data\test_reporting.mdf',
     MOVE N'test_prod_log'  TO N'E:\Logs\test_reporting.ldf',
     NORECOVERY, REPLACE, STATS = 5;
GO

Note: If your logical file names differ, run this first to inspect and substitute names:

RESTORE FILELISTONLY
FROM DISK = N'D:\Backups\test_prod_full_2025-11-25.bak';
GO

Step 5: Take the First Transaction Log Backup from `test_prod`

-- Make some changes (optional)
INSERT INTO dbo.Sales (Customer, Amount) VALUES (N'Vector LLC', 333.33);
GO

-- Log backup
BACKUP LOG [test_prod]
TO DISK = N'D:\Backups\test_prod_log_2025-11-25_1200.trn'
WITH INIT, CHECKSUM, STATS = 5;
GO

Step 6: Apply the Log Backup to `test_reporting` WITH STANDBY

Create an undo file path (used by SQL Server to roll back uncommitted transactions and keep the DB queryable):

RESTORE LOG [test_reporting]
FROM DISK = N'D:\Backups\test_prod_log_2025-11-25_1200.trn'
WITH STANDBY = N'D:\StandbyUndo\test_reporting_undo.dat',
     STATS = 5;
GO

Result: test_reporting is now accessible read‑only for reporting.

Step 7: Periodically Update `test_reporting` with New Log Backups

Repeat this cycle whenever you want to refresh reporting:

-- On test_prod: make changes
INSERT INTO dbo.Sales (Customer, Amount) VALUES (N'Wayne Enterprises', 9876.54);
GO

-- On test_prod: take a log backup
BACKUP LOG [test_prod]
TO DISK = N'D:\Backups\test_prod_log_2025-11-25_1300.trn'
WITH CHECKSUM, STATS = 5;
GO

-- On test_reporting: apply the new log WITH STANDBY (use the SAME undo file)
RESTORE LOG [test_reporting]
FROM DISK = N'D:\Backups\test_prod_log_2025-11-25_1300.trn'
WITH STANDBY = N'D:\StandbyUndo\test_reporting_undo.dat',
     STATS = 5;
GO

Keep using the same undo file path every time.
Users can run read‑only queries between restores; connections are briefly interrupted while a restore runs.

Step 8: Optional Actions

Bring Reporting Fully Online (Ends the Chain)

RESTORE DATABASE [test_reporting] WITH RECOVERY;
GO

After this, you cannot apply further logs from the same chain.

Rerun the Setup from a New Full Backup

If you need to reinitialize, repeat Steps 3–6 starting with a fresh full backup.

Operational Guidance for SQL Server Express

Use Windows Task Scheduler to run sqlcmd for periodic restores:

Program: sqlcmd

Arguments:

-S .\SQLEXPRESS -E -Q "RESTORE LOG [test_reporting] 
FROM DISK = 'D:\Backups\test_prod_log_latest.trn' 
WITH STANDBY = 'D:\StandbyUndo\test_reporting_undo.dat';"

Keep your backup files timestamped and apply them strictly in chronological order.
Don’t move or delete the undo file; it’s required for subsequent STANDBY restores.
Advise report users about short maintenance windows during restores.

Quick Verification Queries

-- On test_reporting: confirm read-only and recent data
SELECT TOP 10 * FROM dbo.Sales ORDER BY SalesId DESC;
GO

-- Check database state
SELECT name, state_desc, recovery_model_desc, is_read_only
FROM sys.databases
WHERE name IN ('test_prod', 'test_reporting');
GO

Common Pitfalls and Fixes

Missing or out‑of‑order log backup: Ensure the log chain is continuous; reinitialize from a new full backup if broken.
Wrong logical file names in RESTORE MOVE: Always confirm with RESTORE FILELISTONLY.
Changing the undo file path or name: Keep it consistent; changing it breaks the chain.
Long‑running queries during refresh: Schedule log restores at predictable low‑usage times.

Extract and analyze deadlock events from SQL Server's system health extended event logs.

2025-08-26T07:22:00.000-07:00


-- Step 1: Create a temporary table to store error log data
CREATE TABLE #errorlog (
    LogDate DATETIME,
    ProcessInfo VARCHAR(100),
    [Text] VARCHAR(MAX)
);

-- Step 2: Declare variables to extract log path
DECLARE @tag VARCHAR(MAX), @path VARCHAR(MAX);

-- Step 3: Populate the error log table using system stored procedure
INSERT INTO #errorlog EXEC sp_readerrorlog;

-- Step 4: Extract the log file path from the error log
SELECT @tag = [Text]
FROM #errorlog
WHERE [Text] LIKE 'Logging%MSSQL\Log%';

-- Step 5: Drop the temporary error log table
DROP TABLE #errorlog;

-- Step 6: Parse the log path from the extracted text
SET @path = SUBSTRING(@tag, 38, CHARINDEX('MSSQL\Log', @tag) - 29);

-- Step 7: Drop the #Deadlock table if it already exists
IF OBJECT_ID('#Deadlock') IS NOT NULL DROP TABLE #Deadlock;

-- Step 8: Read deadlock reports from the system health extended events file
SELECT  
    CONVERT(xml, event_data).query('/event/data/value/child::*') AS DeadlockReport,
    CONVERT(xml, event_data).value('(event[@name="xml_deadlock_report"]/@timestamp)[1]', 'datetime') AS Execution_Time
INTO #Deadlock
FROM sys.fn_xe_file_target_read_file(@path + '\system_health*.xel', NULL, NULL, NULL)
WHERE OBJECT_NAME LIKE 'xml_deadlock_report'
  AND event_data LIKE '%BNY_HF_6840_0008%' -- Filter by database name
ORDER BY Execution_Time DESC;

-- Step 9: Retrieve the top 1000 deadlock events
SELECT TOP 1000 * 
FROM #Deadlock 
ORDER BY Execution_Time DESC;

-- Optional: Filter by specific time range
-- WHERE Execution_Time BETWEEN '2021-09-18 05:00:00.000' AND '2021-09-20 05:00:00.000'

MS SQL Server JSON Basics Cheat Sheet

2025-06-17T22:29:00.000-07:00

MS SQL Server JSON Basics Cheat Sheet

This cheat sheet provides a quick reference for common functions and syntax used to work with JSON data in Microsoft SQL Server (SQL Server 2016 and later).

1. Validating JSON Text

Use ISJSON() to check if a string contains valid JSON. It returns 1 for valid JSON, 0 for invalid JSON, and NULL if the input is NULL.

-- Example Data
DECLARE @json_valid NVARCHAR(MAX) = N'{"name": "John Doe", "age": 30}';
DECLARE @json_invalid NVARCHAR(MAX) = N'{"name": "Jane Doe", "age": }'; -- Invalid JSON

-- Check Validity
SELECT ISJSON(@json_valid) AS IsValid1;         -- Returns 1
SELECT ISJSON(@json_invalid) AS IsValid2;       -- Returns 0
SELECT ISJSON(NULL) AS IsNullValid;         -- Returns NULL

2. Extracting Scalar Values

Use JSON_VALUE() to extract a single scalar value (string, number, boolean) from a JSON string using a JSON path expression.

● Path Syntax: $ represents the root object. Use . for member access and [index] for array element access (0-based).

-- Example Data
DECLARE @json_data NVARCHAR(MAX) = N'{
"person": {
    "name": "Alice",
    "age": 28,
    "isStudent": false,
    "address": {
      "street": "123 Main St",
      "city": "Anytown"
    },
    "courses": ["Math", "History"]
}
}';

-- Extract Scalar Values
SELECT
    JSON_VALUE(@json_data, '$.person.name') AS PersonName,       -- Returns 'Alice'
    JSON_VALUE(@json_data, '$.person.age') AS PersonAge,         -- Returns '28' (as string by default)
    JSON_VALUE(@json_data, '$.person.isStudent') AS IsStudent,   -- Returns 'false'
    JSON_VALUE(@json_data, '$.person.address.city') AS City,     -- Returns 'Anytown'
    JSON_VALUE(@json_data, '$.person.courses[0]') AS FirstCourse; -- Returns 'Math'

-- Extract with specific data type (using CAST/CONVERT)
SELECT
    CAST(JSON_VALUE(@json_data, '$.person.age') AS INT) AS PersonAgeInt,
    CAST(JSON_VALUE(@json_data, '$.person.isStudent') AS BIT) AS IsStudentBit;

3. Extracting Objects or Arrays

Use JSON_QUERY() to extract an object or an array from a JSON string. JSON_VALUE returns NULL if it targets an object or array.

-- Example Data (using @json_data from above)

-- Extract Object
SELECT JSON_QUERY(@json_data, '$.person.address') AS AddressObject;
-- Returns: {"street": "123 Main St", "city": "Anytown"}

-- Extract Array
SELECT JSON_QUERY(@json_data, '$.person.courses') AS CoursesArray;
-- Returns: ["Math", "History"]

-- Extracting an object/array with JSON_VALUE returns NULL
SELECT JSON_VALUE(@json_data, '$.person.address') AS AddressValueIsNull; -- Returns NULL

4. Modifying JSON Data

Use JSON_MODIFY() to update a property value, insert a new property, or delete a property within a JSON string. It returns the modified JSON string.

● Path Syntax: Same as JSON_VALUE and JSON_QUERY.

● append keyword: Adds an element to the end of an array.

● lax mode (default): If the path doesn't exist for an update/delete, it doesn't raise an error.

● strict mode: If the path doesn't exist for an update/delete, it raises an error.

-- Example Data
DECLARE @json_doc NVARCHAR(MAX) = N'{"name": "Bob", "age": 35, "skills": ["SQL"]}';

-- Update existing value
SET @json_doc = JSON_MODIFY(@json_doc, '$.age', 36);
-- @json_doc is now: {"name": "Bob", "age": 36, "skills": ["SQL"]}

-- Insert new key/value pair
SET @json_doc = JSON_MODIFY(@json_doc, '$.city', 'New York');
-- @json_doc is now: {"name": "Bob", "age": 36, "skills": ["SQL"], "city": "New York"}

-- Append to an array
SET @json_doc = JSON_MODIFY(@json_doc, 'append $.skills', 'JSON');
-- @json_doc is now: {"name": "Bob", "age": 36, "skills": ["SQL", "JSON"], "city": "New York"}

-- Delete a key/value pair (set value to NULL)
SET @json_doc = JSON_MODIFY(@json_doc, '$.city', NULL);
-- @json_doc is now: {"name": "Bob", "age": 36, "skills": ["SQL", "JSON"]}

SELECT @json_doc AS ModifiedJson;

5. Parsing JSON into Rows and Columns

Use OPENJSON() to parse JSON text and return objects and properties as rows and columns. It's often used with a WITH clause to define the output schema.

● Default Schema: Returns key/value pairs and type information.

● Explicit Schema (WITH clause): Defines columns and their data types, mapping them to JSON properties using JSON paths.

-- Example Data
DECLARE @json_array NVARCHAR(MAX) = N'[
{"id": 1, "name": "Product A", "price": 10.50},
{"id": 2, "name": "Product B", "price": 25.00, "tags": ["tech", "gadget"]},
{"id": 3, "name": "Product C", "price": 5.75}
]';

-- Using OPENJSON with Default Schema
SELECT [key], [value], [type]
FROM OPENJSON(@json_array);
/* Returns rows representing each object in the array:
key | value                                                     | type
----|-----------------------------------------------------------|------
0   | {"id": 1, "name": "Product A", "price": 10.50}            | 5 (Object)
1   | {"id": 2, "name": "Product B", "price": 25.00, "tags":...}| 5 (Object)
2   | {"id": 3, "name": "Product C", "price": 5.75}            | 5 (Object)
*/

-- Using OPENJSON with Explicit Schema (WITH clause)
SELECT *
FROM OPENJSON(@json_array)
WITH (
    ProductID INT '$.id',
    ProductName VARCHAR(100) '$.name',
    Price DECIMAL(10, 2) '$.price',
    Tags NVARCHAR(MAX) '$.tags' AS JSON -- Keep nested array as JSON
);
/* Returns:
ProductID | ProductName | Price | Tags
----------|-------------|-------|---------------------
1         | Product A   | 10.50 | NULL
2         | Product B   | 25.00 | ["tech", "gadget"]
3         | Product C   | 5.75 | NULL
*/

-- Accessing nested arrays within OPENJSON
DECLARE @json_single_obj NVARCHAR(MAX) = N'{
"orderId": 101,
"customer": "Jane Smith",
"items": [
    {"sku": "X1", "qty": 2},
    {"sku": "Y2", "qty": 1}
]
}';

SELECT i.*
FROM OPENJSON(@json_single_obj, '$.items') -- Specify the path to the array
WITH (
    SKU VARCHAR(10) '$.sku',
    Quantity INT '$.qty'
) AS i;
/* Returns:
SKU | Quantity
----|----------
X1 | 2
Y2 | 1
*/

6. Formatting T-SQL Results as JSON

Use FOR JSON to format query results as JSON text.

● FOR JSON PATH: Gives you full control over the output format using dot notation in column aliases. Nested structures are created based on these aliases.

● FOR JSON AUTO: Automatically creates nested JSON structures based on the table structure in the SELECT statement and the order of columns.

-- Example Table Data (Assume a table named 'Employees')
-- EmployeeID | FirstName | LastName | Department | ManagerID
-- -----------|-----------|----------|------------|-----------
-- 1          | John      | Doe      | Sales      | NULL
-- 2          | Jane      | Smith    | HR         | 1
-- 3          | Peter     | Jones    | Sales      | 1

-- Using FOR JSON PATH
SELECT
    EmployeeID AS 'employee.id',
    FirstName AS 'employee.firstName',
    LastName AS 'employee.lastName',
    Department AS 'employee.dept'
FROM Employees
WHERE EmployeeID = 1
FOR JSON PATH, ROOT('employees'); -- ROOT adds a root element
-- Returns: {"employees":[{"employee":{"id":1,"firstName":"John","lastName":"Doe","dept":"Sales"}}]}

-- Using FOR JSON AUTO
SELECT
    EmployeeID,
    FirstName,
    LastName,
    Department
FROM Employees
WHERE Department = 'Sales'
FOR JSON AUTO;
-- Returns: [{"EmployeeID":1,"FirstName":"John","LastName":"Doe","Department":"Sales"},{"EmployeeID":3,"FirstName":"Peter","LastName":"Jones","Department":"Sales"}]

-- FOR JSON PATH with Nested Structure
SELECT
    e.EmployeeID AS 'id',
    e.FirstName AS 'name.first',
    e.LastName AS 'name.last',
    (SELECT m.FirstName + ' ' + m.LastName
     FROM Employees m
     WHERE m.EmployeeID = e.ManagerID) AS 'managerName'
FROM Employees e
WHERE e.EmployeeID = 2
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER; -- WITHOUT_ARRAY_WRAPPER removes the outer [] for single results
-- Returns: {"id":2,"name":{"first":"Jane","last":"Smith"},"managerName":"John Doe"}

This cheat sheet covers the fundamental JSON functions in SQL Server. Remember to consult the official Microsoft documentation for more advanced features and options.

Performance Tuning SQL Server Queries: Boosting Efficiency and Speed

2023-08-07T04:53:00.006-07:00

Introduction:

In the realm of SQL Server database administration, performance tuning plays a pivotal role in enhancing the efficiency and speed of queries. As the volume of data grows and application demands increase, optimizing the execution of queries becomes critical to maintain the overall system performance. This article delves into the fundamental aspects of performance tuning for SQL Server queries, exploring techniques to identify bottlenecks and implement optimizations for optimal query performance.

1. Understanding Query Execution Plan:

The query execution plan serves as a roadmap to understanding how SQL Server processes a query. By analyzing the execution plan, administrators can identify potential performance issues and determine the most efficient way to execute a query. We will discuss the use of SQL Server Management Studio (SSMS) and dynamic management views (DMVs) to obtain query execution plans and analyze them.

2. Indexing Strategies:

Appropriate indexing is one of the most powerful tools for optimizing query performance. We'll explore various indexing strategies, including clustered, non-clustered, and covering indexes, as well as their impact on query execution. Additionally, we'll delve into common indexing pitfalls and best practices to ensure the right indexes are created for your workload.

3. Query Optimization Techniques:

In this section, we'll explore a range of query optimization techniques, such as JOIN optimization, subquery optimization, and using appropriate SQL Server functions efficiently. We'll also discuss how to leverage query hints and plan guides to influence the execution plan when needed, though caution must be exercised when using these features.

4. Query Rewriting and Refactoring:

Sometimes, rewriting or refactoring a query can lead to significant performance gains. We'll discuss common SQL coding patterns that can be improved and explore how to rewrite complex queries into simpler and more efficient forms. Additionally, we'll cover the importance of avoiding correlated subqueries and employing CTEs (Common Table Expressions) for better query performance.

5. Temporal and Cached Data:

Caching query results and employing temporal tables can improve performance significantly by reducing the load on the database server. We'll delve into techniques for caching data, both at the application level and within SQL Server itself, to reduce query execution times and improve overall user experience.

6. Monitoring and Profiling:

To ensure ongoing performance optimization, monitoring and profiling queries are essential. We'll explore SQL Server's built-in tools, such as SQL Server Profiler and Extended Events, to capture query performance data. Additionally, we'll discuss how to leverage performance monitoring tools like SQL Server Performance Monitor to analyze resource utilization and identify performance bottlenecks.

Conclusion:

Performance tuning of SQL Server queries is a continuous process that requires a comprehensive understanding of the database structure, indexing strategies, and query execution plans. By implementing the techniques discussed in this article, you can significantly improve the efficiency and speed of your queries, resulting in better application performance and a more positive user experience. Remember to analyze the impact of any changes thoroughly and continuously monitor your system to ensure long-term success in performance optimization.

SQL Server with Python - An Intro

2021-05-23T00:33:00.006-07:00

Everyone uses SQL, and everyone uses Python. SQL is the de-facto standard for databases. Python on the other hand is an all-star, a top language for data analytics, machine learning, and web development. Imagine both, together.

This is actually incredibly easy to setup. We can quickly utilize the dynamic nature of Python, to control and build queries in SQL. The best part? After set-up, you don’t need to do anything.

Both of these amazing tools together, allow us to reach new heights of automation and efficiency.

pyodbc

Our bridge between the two technologies is pyodbc. This library allows easy access to ODBC databases.

ODBC, short for Open Database Connectivity, is a standardised application programming interface (API) for accessing databases, developed by the SQL Access group back in the early 90's.

Compliant database management systems (DBMS) include:

IBM Db2
MS Access
MS SQL Server
MySQL
Oracle

In this article, we will be using MS SQL Server. For the most part, this should be directly transferable for use with any ODBC compliant database. The only change required should be with the connection setup.

Connect

The first thing we need to do is create a connection to the SQL server. We can do this using pyodbc.connect. Within this function we must also pass a connection string.

This connection string must specify the DBMS Driver, the Server, a specific Database to connect to, and our connection settings.

So, lets assume we want to connect to server UKXXX00123,45600, database DB01 , to do this we want to use SQL Server Native Client 11.0.

We will be connecting from an internal, and thus trusted connection (we do not need to enter our username and password).

cnxn_str = ("Driver={SQL Server Native Client 11.0};"
            "Server=UKXXX00123,45600;"
            "Database=DB01;"
            "Trusted_Connection=yes;")

Our connection is now initialized with:

cnxn = pyodbc.connect(cnxn_str)

If we are not accessing the database via a trusted connection, we will need to enter the username and password that we would usually use to access the server via SQL Server Management Studio (SSMS).

For example, if our username is JoeBloggs, and our password is Password123, we should immediately change our password.

But before changing that horrible password, we can connect like so:

cnxn_str = ("Driver={SQL Server Native Client 11.0};"
            "Server=UKXXX00123,45600;"
            "Database=DB01;"
            "UID=JoeBloggs;"
            "PWD=Password123;")cnxn = pyodbc.connect(cnxn_str)

Now we are connected to the database, we can begin performing SQL queries via Python.

Run a Query

Every query we run on SQL Server now will consist of a cursor initialization, and query execution. Additionally, if we make any changes inside the server, we also need to commit these changes to the server (which we cover in the next section).

To initialize a cursor:

cursor = cnxn.cursor()

Now, whenever we want to perform a query, we use this cursor object.

Let’s first select the top 1000 rows from a table called customers:

cursor.execute("SELECT TOP(1000) * FROM customers")

This performs the operation, but within the server, and so nothing is actually returned to Python. So let’s look at extracting this data from SQL.

Extract Data

To extract our data from SQL into Python, we use pandas. Pandas provides us with a very convenient function called read_sql, this function, as you may have guessed, reads data from SQL.

read_sql requires both a query and the connection instance cnxn, like so:

data = pd.read_sql("SELECT TOP(1000) * FROM customers", cnxn)

This returns a dataframe containing the top 1000 rows from the customers table.

Changing Data in SQL

Now, if we wanted to change the data in SQL, we need to add another step to the original initialize connection, execute query process.

When we execute queries in SQL, these changes are kept in a temporarily existing space, they are not made directly to the data.

To make these changes permanent, we must commit them. Lets concatenate the firstName and lastName columns, to create a fullName column.

cursor = cnxn.cursor()# first alter the table, adding a column
cursor.execute("ALTER TABLE customer " +
               "ADD fullName VARCHAR(20)")# now update that column to contain firstName + lastName
cursor.execute("UPDATE customer " +
               "SET fullName = firstName + " " + lastName")

At this point, fullName does not exist in our database. We must commit these changes to make them permanent:

cnxn.commit()

Docker – Installation on Windows

2021-05-22T05:55:00.001-07:00

A quick walk through of Docker installation on Windows 10.

1. Downloading the binaries

Download the binaries from the official website.

2. Install WSL 2

Install WSL 2 following instructions provided in the Microsoft website. This step can be done before installing the docker or alternatively we can let docker installer install it for us. Windows Sub System for Linux provides environment to run GNU/Linux environment , command line tools , utilities directly on Windows without installing a VM or dual boot .

3. Installing Docker

Go to the place where we have downloaded the Docker Desktop Installer.exe , right click Run as administrator.
Unlike other installers , Docker installer will not prompt us with many options . The only two options that we will see during installation are “Install required Windows components for WSL 2 “ and “Add shortcut to desktop”. Select both the options and click ok to start the installation.

Wait for the installation to go through.

Once the installation is successful , we will see the following screen :

4. Starting Docker Desktop

Before we can execute any Docker command, we have to first start the Docker desktop. We can start the Docker Desktop from the start menu or from the desktop icon.

The status of the Docker engine is shown at the bottom left corner, it should be green before we can interact with Docker cli .

5. Optional Configuration

There are some configurations in Docker , which are good to have right from the start . Click on the gear icon on the top right side of the Docker Desktop dashboard .

Enabling Kubernetes (Optional)

Navigate to Kubernetes tab and then click on the Enable Kubernetes check box and then click on “Apply & Restart”. This is the fastest way to bring up k8s on our machine.

Enabling WSL 2 Integration

If we have installed any other operating system using WSL2, we don’t have to install Docker once again. We can use the same installation here also by enabling the integration.
Go to settings > Resources > WSL Integration > Enable the integration for the installed OS .

6. Verification

Now that installation is complete, we verify the installation using the following command :
docker info
This will display the details of of Docker Desktop .

For more on this , visit official documentation .

Using Group Managed Service Accounts with IIS 10 on Server 2016

2021-03-22T04:29:00.003-07:00

Using NetworkService powered application pools does have the nice effect, that there is no password needed, because the pool will be running with the credential of the webserver machine account, which is a domain account, where no password management is needed.

To access resources on the network, the webserver machine account must be enabled on the network destination and everything is fine and secure using windows authentication or Kerberos

This approach is good enough, if the scenario is limited to one application per server, because the minute you need another application, which does have different requirements in terms of security, then this approach will fail.

Lets assume, there are 2 web apps on the machine, which each does have its own SQL Server DB and which should not be allowed to access the other ones data.

This scenario can only be used with custom domain accounts, if windows authentication should be used.

Only with 2 different accounts and 2 application pools, the security on each database can be limited to the one matching application pool.

But then someone has to manage this domain passwords and make sure, that they are not expiring, but still changed from time to time. A tedious task and the passwords are probably distributed across the company, hopefully in a secure way and not inside XLS or Textfiles…

Another way with Server 2016 is to use Group Managed Service accounts.

This requires, that Active Directory scheme is on level 2012 R2, only then, the feature “Group Managed Service Accounts” can be used.

Setup a Group Managed Service Account

Login to DC:

Enable gMSA globally on Domain

— for Lab environments we use the switch –EffectiveTime, so that we don’t have to wait for 10 hours, which usually should make sure, that AD sync is ready.

Add-KdsRootKey –EffectiveTime ((get-date).addhours(-10));

This will usually be done from the Active Directory team in your environment

Open ServerManager => Tools => Active Directory Administrative Center

Add new global SecurityGroup named gMSAGroup

Go to AD Admin Center and search for the newly created group (gMSAGroup)

OR: Use Powershell and first install the Powershell AD-modules

Install-WindowsFeature -Name RSAT-AD-PowerShell

Then create the global security group using

NEW-ADGroup –name “gMSAGroup” –path “OU=XYZ,DC=mydomain,DC=com” -GroupCategory Security -groupscope Global

Right click the gMSAGroup entry and add all the memberserver, which should be able to use the Group Service Managed Account IIS1Svc

or use Powershell:

Add-ADGroupMember "gMSAGroup” -Members "Server1$", "Server2$"

After adding all the memberservers to the Group Managed Service Group, they must be rebooted!

Create first gMSA Account on the DC: (max 15 chars)

New-ADServiceAccount IIS1SvC -DNSHostName IIS1Svc.corp.litware.com -PrincipalsAllowedToRetrieveManagedPassword gMSAGroup

optionally use –path to define, whe the account should be placed into the domain structure, eg:
-Path "OU=OUXy,DC=mydomain,DC=com"

Check in AD Admin Center, that the account is visible
S

Switch to MemberServer (HSW2K12R2Web1)

Install on MemberServers: Remote Server Administration Tools via Server Manager to get Active Directory Module for Windows Powershell

OR with Powershell: Install-WindowsFeature -Name RSAT-AD-PowerShell

Open Powershell Admin Console and

Install-ADServiceAccount IIS1Svc

If error is “access denied”, make sure, that the memberserver was added to the allow list of the group service group and the server was rebooted afterwards!

Create new AppPool in InetMgr:

Use this GroupServiceManaged Account and append “$” to the name and leave password empty

Use this Account for a web application.

When this web application will access a resource on another computer, it will then use this GMSA

https://hansstan.wordpress.com/2017/07/05/using-group-managed-service-accounts-with-iis/

2021-02-19T07:01:00.005-08:00

Creating a non-XML format file

Please review Non-XML Format Files (SQL Server) for detailed information. The following command will use the bcp utility to generate a non-xml format file, myFirstImport.fmt, based on the schema of myFirstImport. To use a bcp command to create a format file, specify the format argument and use nul instead of a data-file path. The format option also requires the -f option. In addition, for this example, the qualifier c is used to specify character data, t, is used to specify a comma as a field terminator, and T is used to specify a trusted connection using integrated security. At a command prompt, enter the following command:

bcp TestDatabase.dbo.myFirstImport format nul -c -f D:\BCP\myFirstImport.fmt -t, -T

REM Review file Notepad D:\BCP\myFirstImport.fmt

https://docs.microsoft.com/en-us/sql/relational-databases/import-export/use-a-format-file-to-bulk-import-data-sql-server?view=sql-server-ver15

System.Web.HttpException: Request timed out

2021-02-09T20:53:00.001-08:00

Server Error in '/' Application. Request timed out. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Web.HttpException: Request timed out.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[HttpException (0x80004005): Request timed out.]

To solve the timeout issue we can specify the request timeout (in seconds) in the web.config, like this:

<httpRuntime executionTimeout="300" />

For context, here's a complete web.config with just this setting:

<configuration>
    <system.web>
        <httpRuntime executionTimeout="300" />
    </system.web>

</configuration>

Also you can Open IIS -> click on the your siteName -> configuration Editor on the Section, scroll to system.web/httpRuntime. Increase the executionTimeout to the time you think it will take to execute your process.

SQL Server - SET XACT_ABORT

2018-09-21T21:12:00.002-07:00

SET XACT_ABORT
Usually we use TRY… CATCH block of error handling where in CATCH block transaction is rolled back. But sometimes we simply use BEGIN.. COMMIT transaction without any TRY..CATCH block. In this scenario entire batch is not rolled back.

BEGIN TRANACTION

INSERT 1

INSERT 2 --This statement failed

INSERT 3

COMMIT TRANSACTION

In above case INSERT 1 and INSERT 3 still executes successfully.

SET XACT_ABORT

It is ideal for "all or nothing" kind of transactions.
It takes the whole batch as one transaction. If any of the statement in batch fails, whole batch is rolled back.

SET XACT_ABORT ON

BEGIN TRANACTION

INSERT 1

INSERT 2 --This statement failed

INSERT 3

COMMIT TRANSACTION

In this case INSERT 1 and INSERT 3 are also rolled back.

Advantage

TRY .. CATCH does not catch the schema related errors. It will return an error without hitting CATCH block where you have rolled back opened transaction. So You will end up with an opened transaction.
XCAT_ABORT rolls back transaction as soon as error occurs. So there will be no open transaction. Open transactions are major cause of performance degradation.

Disadvantage of using with TRY .. CATCH block (For non-schema related errors)

As CATCH block will not be executed, If you are handling / raising error in CATCH block. It will not be possible.

SQL Server Date Format conversion

2018-07-27T03:13:00.001-07:00

SELECT convert(varchar, getdate(), 100) -- mon dd yyyy hh:mmAM

SELECT convert(varchar, getdate(), 101) -- mm/dd/yyyy – 10/02/2008                  

SELECT convert(varchar, getdate(), 102) -- yyyy.mm.dd – 2008.10.02           

SELECT convert(varchar, getdate(), 103) -- dd/mm/yyyy

SELECT convert(varchar, getdate(), 104) -- dd.mm.yyyy

SELECT convert(varchar, getdate(), 105) -- dd-mm-yyyy

SELECT convert(varchar, getdate(), 106) -- dd mon yyyy

SELECT convert(varchar, getdate(), 107) -- mon dd, yyyy

SELECT convert(varchar, getdate(), 108) -- hh:mm:ss

SELECT convert(varchar, getdate(), 109) -- mon dd yyyy hh:mm:ss:mmmAM (or PM)

SELECT convert(varchar, getdate(), 110) -- mm-dd-yyyy

SELECT convert(varchar, getdate(), 111) -- yyyy/mm/dd

SELECT convert(varchar, getdate(), 112) -- yyyymmdd

SELECT convert(varchar, getdate(), 113) -- dd mon yyyy hh:mm:ss:mmm

SELECT convert(varchar, getdate(), 114) -- hh:mm:ss:mmm(24h)

SELECT convert(varchar, getdate(), 120) -- yyyy-mm-dd hh:mm:ss(24h)

SELECT convert(varchar, getdate(), 121) -- yyyy-mm-dd hh:mm:ss.mmm

SELECT convert(varchar, getdate(), 126) -- yyyy-mm-ddThh:mm:ss.mmm

appcmd - to list sites using wildcard mapping

2018-04-18T21:55:00.003-07:00

You can use wildcard characters with appcmd using the following syntax:

appcmd list apppool /name:"$=*2007"

This command will list all application pools with a name ending in "2007". Using a variation on the same method, you can find all application pools running under the identify of users in a particular domain.

appcmd list apppool /processModel.userName:"$=MyDomain\*"

This will list all application pools with running with an identity of users in the "MyDomain" domain.

appcmd list site /name:"$=*cdo*"

This will list all sites with a name having "cdo".

Tip: You can find out all of the attributes that can be used with appcmd by querying for help after using an attribute. For example:

appcmd list apppools /name:DefaultAppPool -?

ERROR ( message:-name
-queueLength
-autoStart
-enable32BitAppOnWin64
-managedRuntimeVersion
-enableConfigurationOverride
-managedPipelineMode
-passAnonymousToken
-processModel.identityType
-processModel.userName
-processModel.password
-processModel.loadUserProfile
-processModel.manualGroupMembership
-processModel.idleTimeout
-processModel.maxProcesses
-processModel.shutdownTimeLimit
-processModel.startupTimeLimit
-processModel.pingingEnabled
-processModel.pingInterval
-processModel.pingResponseTime
-recycling.disallowOverlappingRotation
-recycling.disallowRotationOnConfigChange
-recycling.logEventOnRecycle
-recycling.periodicRestart.memory
-recycling.periodicRestart.privateMemory
-recycling.periodicRestart.requests
-recycling.periodicRestart.time
-recycling.periodicRestart.schedule.[value='timespan'].value
-failure.loadBalancerCapabilities
-failure.orphanWorkerProcess
-failure.orphanActionExe
-failure.orphanActionParams
-failure.rapidFailProtection
-failure.rapidFailProtectionInterval
-failure.rapidFailProtectionMaxCrashes
-failure.autoShutdownExe
-failure.autoShutdownParams
-cpu.limit
-cpu.action
-cpu.resetInterval
-cpu.smpAffinitized
-cpu.smpProcessorAffinityMask
-cpu.smpProcessorAffinityMask2
)

By appending "-?" to the command above, appcmd generates an error that lists all of the attributes that can be used with application pools.

Source : https://docs.microsoft.com/en-us/previous-versions/windows/it-pro/windows-server-2008-R2-and-2008/cc771280(v%3dws.10)

SSMS versus SQL Operations Studio

2017-11-22T00:23:00.003-08:00

SSMS vs SQLOpsStudio:

SQL Operations Studio (preview) is a free lightweight database development and operations tool that runs on your desktop and is available for Windows, macOS, and Linux. SQL Operations Studio (preview) has built-in support for Azure SQL Database, Azure SQL Data Warehouse, and SQL Server running on-premises or in any cloud.

SQL Operations Studio (preview) offers a consistent experience across databases of your choice on your favorite operating systems.

SQL Server Management Studio is a fully featured database management tool that enables most DBA’s tasks. It will continue to be the flagship tool with the data tools portfolio.
SQL Operations Studio enables most of the routine DBA tasks for development and operations of a database but does not have all of the functionality of SSMS.
SQL Operations Studio is meant to be light weight and easy to use for non-professional DBAs and thus will most likely always optimize usability and light weight to addressing all corner cases.

Investments in flagship Windows tools (SSMS, SSDT, PowerShell) will continue in addition to the next generation of multi-OS and multi-DB CLI and GUI tools.

The goal is to offer customers the choice of using the tools they want on the platforms of their choice for their scenarios.

Source: https://docs.microsoft.com/en-us/sql/sql-operations-studio/faq

Windows Batch for getting today's date

2017-11-08T01:55:00.001-08:00

for /F "skip=1 delims=" %%F in ('

wmic PATH Win32_LocalTime GET Day^,Month^,Year /FORMAT:TABLE

') do (

for /F "tokens=1-3" %%L in ("%%F") do (

set CurrDay=0%%L

set CurrMonth=0%%M

set CurrYear=%%N

)

set dd=%CurrDay:~-2%

set mm=%CurrMonth:~-2%

set yyyy=%CurrYear%

set today=%CurrYear%%CurrMonth:~-2%%CurrDay:~-2%

SQL Logic Operator Precedence: And and Or

2017-10-20T07:41:00.001-07:00

And has precedence over Or, so, even if a <=> a1 Or a2

Where a And b

is not the same as

Where a1 Or a2 And b,

because that would be Executed as

Where a1 Or (a2 And b)

and what you want, to make them the same, is

 Where (a1 Or a2) And b

Here's an example to illustrate:

Declare @x tinyInt = 1
Declare @y tinyInt = 0
Declare @z tinyInt = 0

Select Case When @x=1 OR @y=1 And @z=1 Then 'T' Else 'F' End -- outputs T
Select Case When (@x=1 OR @y=1) And @z=1 Then 'T' Else 'F' End -- outputs F

How to disable Data Compression on tables in whole database in SQL Server 2008R2?

2017-07-24T00:53:00.001-07:00

If you restore database from enterprise edition to Standard edition and you have enabled "Data compression" on table, restoration fails. Data compression is available only Enterprise edition in SQL Server 2008R2. The best way how to disable data compression is the following:

1) make a clone of your database where data compression is enabled on enterprise edition

2) then run this script on clone database

SELECT
SCHEMA_NAME(sys.objects.schema_id) AS [SchemaName]
,OBJECT_NAME(sys.objects.object_id) AS [ObjectName]
,[rows]
,[data_compression_desc]
,[index_id] as [IndexID_on_Table]
into #compr
FROM sys.partitions
INNER JOIN sys.objects
ON sys.partitions.object_id = sys.objects.object_id
WHERE data_compression > 0
AND SCHEMA_NAME(sys.objects.schema_id) <> 'SYS'
while exists(select 1 from #compr)
begin
declare @i varchar(200) = (select top 1 [ObjectName] from #compr)
print @i
exec ('ALTER INDEX ALL ON '+@i + ' REBUILD WITH (DATA_COMPRESSION = None)')
delete from #compr where [ObjectName] = @i
end
drop table #compr

3) now you can backup clone database and restore on Standard edition

How do you drop a default constraint in T-SQL ?

2017-03-10T02:30:00.002-08:00

DECLARE @tableName VARCHAR(MAX) = '<MYTABLENAME>'
DECLARE @columnName VARCHAR(MAX) = '<MYCOLUMNAME>'
DECLARE @ConstraintName nvarchar(200)
SELECT @ConstraintName = Name 
FROM SYS.DEFAULT_CONSTRAINTS
WHERE PARENT_OBJECT_ID = OBJECT_ID(@tableName) 
AND PARENT_COLUMN_ID = (
    SELECT column_id FROM sys.columns
    WHERE NAME = @columnName AND object_id = OBJECT_ID(@tableName))
IF @ConstraintName IS NOT NULL
    EXEC('ALTER TABLE '+@tableName+' DROP CONSTRAINT ' + @ConstraintName)

Just replace <MYTABLENAME> and <MYCOLUMNNAME> as appropriate.

2016-06-05T08:28:00.000-07:00

SQLCMD and the power of the little r

I would have to say that one of my favorite new utilities that shipped with SQL Server 2005 has been the SQLCMD utility. I am going to demonstrate the use of include files. The following is the complete call syntax for SQLCMD.

sqlcmd

[{ { -U login_id [ -P password ] } | –E trusted connection }]

[ -z new password ] [ -Z new password and exit]

[ -S server_name [ \ instance_name ] ] [ -H wksta_name ] [ -d db_name ]

[ -l login time_out ] [ -A dedicated admin connection ]

[ -i input_file ] [ -o output_file ]

[ -f < codepage > | i: < codepage > [ < , o: < codepage > ] ]

[ -u unicode output ] [ -r [ 0 | 1 ] msgs to stderr ]

[ -R use client regional settings ]

[ -q "cmdline query" ] [ -Q "cmdline query" and exit ]

[ -e echo input ] [ -t query time_out ]

[ -I enable Quoted Identifiers ]

[ -v var = "value"...] [ -x disable variable substitution ]

[ -h headers ][ -s col_separator ] [ -w column_width ]

[ -W remove trailing spaces ]

[ -k [ 1 | 2 ] remove[replace] control characters ]

[ -y display_width ] [-Y display_width ]

[ -b on error batch abort ] [ -V severitylevel ] [ -m error_level ]

[ -a packet_size ][ -c cmd_end ]

[ -L [ c ] list servers[clean output] ]

[ -p [ 1 ] print statistics[colon format] ]

[ -X [ 1 ] ] disable commands, startup script, enviroment variables [and exit]

[ -? show syntax summary ]

:r is a SQLCMD command that parses additional Transact-SQL statements and sqlcmd commands from the file specified by <filename> into the statement cache.

A simple example:

In SQLQuery1.sql I have the following:

SELECT TOP 5 ProductID,Name,ProductNumber,MakeFlag FROM AdventureWorks.Production.Product

In SQLQuery2.sql I can then reference SQL file 1 as follows:

:r “c:\SQLQuery1.sql”

My results are:

ProductID    Name    ProductNumber    MakeFlag
1    Adjustable Race    AR-5381    0
2    Bearing Ball    BA-8327    0
3    BB Ball Bearing    BE-2349    1
4    Headset Ball Bearings    BE-2908    0
316    Blade    BL-2036    1

This may not seem like a big deal, but consider the following scenario. I have a large bat file of SQL that I process in jobs or in SSIS packages. This bat file is doing and setting the same variables over and over again, so I can create include files that declare my variables, and another include file that sets them.

My first file “c:\DeclareVariables.sql” will always change me to the correct DB, set no count on and declare my variables. This becomes good anchor file for any pre-processing that I may want to do.

USE [AdventureWorks]

GO

SET NOCOUNT ON

GO

DECLARE @AccountingStartDate smalldatetime;

DECLARE @AccountingEndDate smalldatetime;

DECLARE @DocumentStatusText varchar(400);

DECLARE @Status tinyint;

“c:\SetVariables.sql” is where is set my variables. You notice that the SQL variable @Status is set to a SQLCMD variable of the same name “Status”. This allows me to control that from my batch sql file. I am trying to eliminate any hard coded references in my global files.

SET @Status = $(Status);

SET @AccountingStartDate = (SELECT AdventureWorks.dbo.ufnGetAccountingStartDate());

SET @AccountingEndDate = (SELECT AdventureWorks.dbo.ufnGetAccountingEndDate());

SET @DocumentStatusText = (SELECT AdventureWorks.dbo.ufnGetDocumentStatusText(@Status));

And finally the batch file “c:\SQLBatch.sql”. The first two lines set my include files to variables. This allows for a consistent naming through all my files and allows me to change the files without having to change the batch code. This is good for testing and portability. The next line is where I set the SQLCMD “Status” variable. This variable is then set in the SetVariables.sql file to the TSQL variable @Status that is passed to the function ufnGetDocumentStatusText.

:setvar DeclareVariablesScript "c:\DeclareVariables.sql"

:setvar SetVariablesScript "c:\SetVariables.sql"

-- Set Local variable Status (Used in SetVariablesScript)

:setvar Status 1

-- Declare Global Script Variables

:r $(DeclareVariablesScript)

-- Set Global Script Variables

:r $(SetVariablesScript)

SELECT * FROM AdventureWorks.Sales.SalesOrderHeader soh

WHERE ((soh.OrderDate > = @AccountingStartDate) AND (soh.OrderDate <= @AccountingEndDate));

GO

Now all I have to do it execute the file “c:\SQLBatch.sql”. If you are familiar with ASP include files this should be easy to understand.

How can I switch themes in Visual Studio 2012 ?

2015-09-17T23:35:00.000-07:00

In Visual Studio 2012, open the Options dialog (Tools -> Options). Under Environment -> General, the first setting is "Color theme." You can use this to switch between Light and Dark.

The shell theme is distinct from the editor theme--you can use any editor fonts and colors settings with either shell theme.

There is also a Color Theme Editor extension that can be used to create new themes.

Removing HTTPS binding affects other sites.

2015-03-12T00:08:00.003-07:00

You may receive the below error message while removing https binding for domain in IIS.

Removing HTTPS binding may affect the other sites

Solution: If you remove the https binding for the site through IIS then the SSL certificate will also be removed for another domain to which the certificate is assigned.

The only way is to remove the SSL bindings from the applicationhost.config file.

# Login to the server through RDP.
# Go to path C:/windows/system32/inetsrv/config
# Open the applicationhost.config file
# Search for domain SSL binding which you want to remove.
# Delete that complete line of the SSL binding and save the file.

You will see that the SSL binding for the domain has been removed without affecting other domains.

Note: Make sure you take the backup of the applicationhost.config file before making changes

AWS EC2 VPC - RDS Error: The semaphore timeout period has expired

2014-11-26T20:50:00.002-08:00

This issue is caused by TCP offloading on some Windows EC2 instances which connect to SQL Server DB instances. We need to disable TCP Offloading on your EC2 Instance(s) and perform a reboot to resolve the issue.

The following aligns with Microsoft's guidance as outlined in the KB article: http://support.microsoft.com/kb/942861

Bring up the "Network Adapters" by opening "ncpa.cpl" (type at a Run prompt or in the Start Menu's Search box)
Right click the "Local Area Connection" and select "Properties"
Click the "Configure" button
Select the "Advanced Tab"
Select the following five "Properties" and set the "Value" to "Disabled" (See attached screenshot highlights)

* IPv4 Checksum Offload
* Large Receive Offload (IPv4)
* Large Send Offload Version 2 (IPv4)
* TCP Checksum Offload (IPv4)
* UDP Checksum Offload (IPv4)

Click "OK" to close the dialog box, then close the "Network Adapters" window
Run the following script from a command prompt to set global TCP offload settings:

C:\> netsh int ip set global taskoffload=disabled
C:\> netsh int tcp set global chimney=disabled
C:\> netsh int tcp set global rss=disabled
C:\> netsh int tcp set global netdma=disabled

Alternative to .NET Reflector

2014-11-26T20:44:00.002-08:00

For those of you who miss .NET Reflector (since Redgate took down the free version in 2011), there is a pretty good open source alternative: ILSpy.

The tool is designed around the original Roy Osherove’s .NET Reflector and supports all its major features including intermediate language disassembly, reflection (browsing), decompilation to C#.

It is a pretty decent IL decompiler.

How to backup Amazon RDS MS SQL Server database instance and restore locally

2014-09-16T21:19:00.001-07:00

We can't currently create a .bak file out of amazon rds. The way I handle this is to use the azure migration wizard which just happens to also work with amazon rds.

I spin up a EC2 instance with SQL Server and the migration wizard installed. I then use the migration tool to copy the RDS database to the ec2 instance.

Once that is done you can create a .bak file from the SQL Server running on the EC2 instance. its a pain but it works. if you have the bandwidth or your database is small you may be able to use the migration tool directly on your target machine.

http://sqlazuremw.codeplex.com

"Launchy"

2014-02-13T22:42:00.002-08:00

I don’t know if this product is widely known here but it came up in conversation yesterday so I thought I’d share. In my opinion, it’s one of the most useful tools around…and it’s free. Spend a bit of time with it and see for yourselves.

http://www.launchy.net/

MS SQL Server

Simulate Slow Typing with PowerShell: Type SQL Code Over Extended Periods

Introduction

Features

Prerequisites

How It Works

Installation & Setup

Step 1: Create the Script File

Step 2: Enable PowerShell Execution

Usage

Option 1: Generate 10,000 Comment Lines (Default)

Option 2: Type SQL Code from a File

Option 3: Customize Total Time

Option 4: Combine Options

Step-by-Step Execution Guide

Troubleshooting

Issue: "Keyword delimiter is missing" or "Group delimiters are not balanced"

Issue: Script won't run

Issue: Text not appearing in target window

Complete Script Code

Conclusion

Setting Up a Standby Reporting Database in SQL Server Express

Objective and Setup

Prerequisites and Notes

Step 1: Create the test_prod Database

Step 2: Seed Some Sample Data (Optional but Useful)

Step 3: Take an Initial Full Backup of test_prod

Step 4: Create the Reporting Database via Restore (NORECOVERY)

Step 5: Take the First Transaction Log Backup from test_prod

Step 6: Apply the Log Backup to test_reporting WITH STANDBY

Step 7: Periodically Update test_reporting with New Log Backups

Step 8: Optional Actions

Bring Reporting Fully Online (Ends the Chain)

Rerun the Setup from a New Full Backup

Operational Guidance for SQL Server Express

Quick Verification Queries

Common Pitfalls and Fixes

Extract and analyze deadlock events from SQL Server's system health extended event logs.

MS SQL Server JSON Basics Cheat Sheet

1. Validating JSON Text

2. Extracting Scalar Values

3. Extracting Objects or Arrays

4. Modifying JSON Data

5. Parsing JSON into Rows and Columns

6. Formatting T-SQL Results as JSON

Performance Tuning SQL Server Queries: Boosting Efficiency and Speed

SQL Server with Python - An Intro

pyodbc

Connect

Run a Query

Extract Data

Changing Data in SQL

Docker – Installation on Windows

1. Downloading the binaries

2. Install WSL 2

3. Installing Docker

4. Starting Docker Desktop

5. Optional Configuration

Enabling Kubernetes (Optional)

Enabling WSL 2 Integration

6. Verification

Using Group Managed Service Accounts with IIS 10 on Server 2016

Setup a Group Managed Service Account

Login to DC:

Creating a non-XML format file

System.Web.HttpException: Request timed out

SQL Server - SET XACT_ABORT

SQL Server Date Format conversion

appcmd - to list sites using wildcard mapping

You can use wildcard characters with appcmd using the following syntax:

SSMS versus SQL Operations Studio

SSMS vs SQLOpsStudio:

Windows Batch for getting today's date

SQL Logic Operator Precedence: And and Or

How to disable Data Compression on tables in whole database in SQL Server 2008R2?

How do you drop a default constraint in T-SQL ?

How can I switch themes in Visual Studio 2012 ?

Removing HTTPS binding affects other sites.

AWS EC2 VPC - RDS Error: The semaphore timeout period has expired

Alternative to .NET Reflector

Step 1: Create the `test_prod` Database

Step 3: Take an Initial Full Backup of `test_prod`

Step 5: Take the First Transaction Log Backup from `test_prod`

Step 6: Apply the Log Backup to `test_reporting` WITH STANDBY

Step 7: Periodically Update `test_reporting` with New Log Backups