SQL Server - No Longer Set

AdventureWorks

The AdventureWorks SQL Server Sample Database

Step-by-step instructions for installing the AdventureWorks SQL Server sample database.

Prompt School

Reformat an INSERT INTO...VALUES Statement with ChatGPT

Let's use ChatGPT to help us debug an SQL INSERT INTO ... VALUES statement by lining up the fields with the values.

Debugging

TCP Port Test Fails When SQL Server Service Is Stopped

There are three likely causes for a failed TCP Port test: 1) firewall on the SQL Server host; 2) SQL Server uses dynamic ports; or 3) SQL Server service is stopped.

Database Design

When Should You Include an Autonumber Column in a Table?

Surrogate vs. Natural Keys. GUIDs vs. Autonumbers. What factors dictate whether to include an autonumber column in your table? When should you do it?

Debugging

Using PowerShell to Provide TCP Port Details of Running SQL Server Instances

A PowerShell script that returns useful debugging information about the TCP port details of running instances of SQL Server.

Code Library

ptReplaceDeclaredValues(): A VBA Function to Update SQL in Pass-Through Queries

Use this function to reliably replace declared values in a pass-through query.

AI

How ChatGPT Helped Me Write a Complex Stored Procedure in SQL Server

Stop treating ChatGPT like an all-knowing oracle and start treating it like the hard-working, enthusiastic, but not-quite-fully-formed intern that it currently is.

SQL Server

B-BEAR: Quick and Dirty SQL Server Testing

Begin, Before, Execute, After, Rollback. This technique makes testing your SQL Server statements a snap.

SQL Server

Installing Multiple Versions of SQL Server Side-by-Side on a Development Machine

Save resources by setting additional instances of SQL Server to Manual startup. Use Process Explorer to grant Start/Stop permissions for an individual service.

Testing

Testing Non-Privileged Access to SQL Server During Development

How to use the runas command and a local user account to test non-privileged access to SQL Server.

Testing

Creating a Low-Privileged Local User for SQL Server Testing

It can be difficult to test how an Access application with a SQL Server backend behaves for a low-privileged user. Creating such a user account is step one.

SQL Server

Updating Linked SQL Server Views in Access When Changing the Underlying Table Data Types

If you change the data type of a table field in SQL Server, follow these steps to be sure all related views are updated in Microsoft Access.

SQL Server

5 Tips for Making Changes on Live SQL Server Databases

Making database changes on production? These 5 tips will make one of the most dangerous programming activities a little bit safer.

SQL Server

Epoch Differences Between VBA and SQL Server

Do you have unexplained dates of December 30, 1899 in your SQL Server database? VBA's epoch (i.e., zero date) could be the culprit.

Under 100

What is rowversion in SQL Server?

The concept of SQL Server's rowversion data type explained in under 100 words. #Under100

Debugging

Fixing a Persistent "Write Conflict" Error

If you are getting consistent "Write Conflict" warnings when using bound forms to update SQL Server tables with datetime fields, there may be an easy fix.

Migration

Scripted SQL Server Migrations

Use this batch file to automatically backup a database on one instance of SQL Server and restore it on a different instance of SQL Server.

Migration

SQL Server Migration Steps

My SQL Server migration philosophy consists of two priorities: (1) Avoid disasters and (2) Minimize production downtime.

SQL Server

HOW TO: Automate Batch Deletion of SQL Server Records

Using a single DELETE statement to remove 20 million+ records from SQL Server is a bad idea. Here's a better approach.

SQL Server

Deleting a Large Number of Records in SQL Server

Let's explore four options--two bad and two good--for deleting one million or more records from a single SQL Server table.

SQL Server

How to Install SQL Server on a Read-Only Domain Controller (RODC)

The official Microsoft documentation says it's not possible, but with a little ingenuity and poor judgment, we can get SQL Server to run on an RODC.

Tools

3 Free Options for Managing Linked Tables in Access

The original linked table manager was a terrible tool. Let's look at three better options for today's Microsoft Access developers.

SQL Server

Advanced Server-Side SQL Server Query Troubleshooting

The XEvent Profiler in SSMS is a powerful troubleshooting tool, but what happens when the standard sessions are not enough? We need to customize...

SQL Server

IIF ... IS NULL vs. Nz(): Testing a Hypothesis

Which is better in a WHERE clause: IIf...Is Null or Nz()? Or is there a third option that's better than both of those?

Migration

Convert Common Access Field Defaults to SQL Server

Here's the Select Case statement I use to convert common Access field default calculations--Now(), Date(), etc.--to their SQL Server equivalents.