errors

Fix Error 2744 “Multiple identity columns specified for table” in SQL Server

by

If you’re getting SQL Server error 2744 that reads “Multiple identity columns specified for table…“, it looks like you’re trying to define a table to have more than one identity column.

SQL Server restricts identity columns to just one per table. If you try to add another one, you’ll get the above error.

The easiest way to address this issue is to leave the table with one identity table and be done with it. But that might not always be practical. Maybe you need a column that increments a different value than the identity column. Fortunately, there are ways of doing that.

Read more

Fix Error 4901 “ALTER TABLE only allows columns to be added that can contain nulls… etc” in SQL Server

by

If you’re getting an error in SQL Server that reads something like “ALTER TABLE only allows columns to be added that can contain nulls, or have a DEFAULT definition specified, or the column being added is an identity or timestamp column, or… etc etc“, it’s probably because you’re trying to add a NOT NULL column to a table that already contains data.

This error occurs due to the fact that the NOT NULL constraint will be violated for every row in the table. Think about it for a second. When you first add the column, there’s no data. It’s not until you run a subsequent INSERT statement (or some other process that populates the table) that you will get data. In the meantime, all values in your new column will be NULL. And that, of course, violates the NOT NULL constraint.

Read more

Fix Error 3234 “Logical file is not part of database” When Restoring a Database in SQL Server

by

If you’re getting error 3234 that reads something like “Logical file ‘AdventureWorksLT_Data’ is not part of database ‘AdventureWorksLT2025’. Use RESTORE FILELISTONLY to list the logical file names.“, you’re referencing the wrong logical file names when restoring a database.

This issue can happen when you try to map the logical file names to a new location, but you get those logical file names wrong.

Fortunately there’s an easy fix. It involves looking up the actual logical file names, then modifying your RESTORE DATABASE statement accordingly.

Read more

Writing Valid ORDER BY Queries for Views and CTEs in SQL Server

by

If you’ve worked with SQL Server long enough, you’ve probably hit the dreaded error when trying to use ORDER BY inside a view or CTE. It usually shows up as something like:

The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.

This rule can come as a bit of a surprise because it feels natural to want a query “pre-sorted” when wrapping it in a view or CTE. The problem is that SQL Server doesn’t allow the ORDER BY clause in this context unless it’s in conjunction with the clauses mentioned in the error message. Without such clauses, you need to explicitly request it at the outermost SELECT.

Let’s walk through an example of how to handle this.

Read more

How to Prevent Overflow Errors When Aggregating Data in SQL Server

by

When working with aggregate functions in SQL Server, it’s easy to overlook that certain datatypes have strict limits. If you’re summing values in large tables (or even just summing very large numbers), you might run into an arithmetic overflow error. This happens when the result of an aggregate exceeds the maximum value the datatype can hold.

Understanding how this works and how to prevent errors will help you write reliable queries.

Read more

Avoiding “Columns Mismatch” Errors in INSERT Statements

by

A “columns mismatch” error in SQL usually happens when the number of values you’re trying to insert doesn’t line up with the number of columns in the table. It’s not a complicated issue, but it can be an easy one to overlook, especially when working with tables that evolve over time or when you skip specifying column names in your INSERT statements.

Understanding why the error occurs makes it simple to avoid, and a few small habits can help keep your SQL inserts clean and reliable.

Read more

Using NULLIF() to Handle the “Divide by Zero” Error in SQL Server

by

If you’ve written SQL long enough, you’ve probably run into the dreaded “Divide by zero error encountered” message. This happens when you try to perform a division and the denominator turns out to be zero. SQL Server throws an error immediately, which stops your query. This can be annoying, especially if the zero values are expected in the data but you don’t want them breaking your query.

One simple way to deal with this is by using the NULLIF() function.

Read more

Fixing Invalid Date Conversions in SQL Server

by

When you work with dates in SQL Server, you’ll often run into situations where a value can’t be converted directly to a datetime or date. This usually happens because the source data isn’t in a format SQL Server recognises, or because the value itself is out‑of‑range (e.g., “2025‑02‑30”). Fortunately, the built‑in conversion functions CAST() and CONVERT() provide us with enough flexibility to clean up those problematic values without resorting to messy string manipulation.

Below we’ll look at the most common scenarios, show how to diagnose the issue, and demonstrate how to fix it.

Read more

Fix “The specified schema name either does not exist or you do not have permission to use it” in SQL Server

by

There are a couple of obvious reasons you might be seeing an error that reads something like “The specified schema name “XYZ” either does not exist or you do not have permission to use it” in SQL Server. This typically happens when creating an object on a schema that doesn’t exist or you don’t have permissions.

This article shows an example of the error and a couple of ways to fix it, depending on the underlying cause.

Read more

Troubleshooting Date Format Errors in SQL Server Imports

by

Importing data into SQL Server is usually quite straightforward. That is, until you run into date and time formatting issues. Dates that look fine in a CSV, Excel, or flat file can suddenly throw errors or, worse, silently load with the wrong values. Since SQL Server is strict about how it interprets dates, mismatches between source file formats and SQL Server’s expectations are one of the most common headaches during imports.

This article looks at why these errors happen, what SQL Server expects, and how to troubleshoot these pesky date format issues.

Read more