SQL error messages are like a box of chocolates, you never know which one you’ll get until you run the query…
As someone who works with databases and SQL, you’ve likely had your fair share of errors when querying a new statement or updating new records. Even the best SQL programmers run into these errors every single day, so don’t feel discouraged if you have to look things up. This guide provides an overview of common SQL error messages, what they mean, and how to fix them.
What we will cover:
- Incorrect syntax error.
- Data type mismatch.
- Invalid object name.
- Violation of PRIMARY KEY constraint. Cannot insert duplicate key in object.
- Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
- The EXECUTE permission was denied on the object [object name], database [database name], schema ‘dbo’.
1. Syntax Error
Error message: “Syntax error near [keyword]”
This error occurs when there is a typo or missing punctuation in the SQL syntax, such as a misspelt keyword or a missing comma.
The example below shows a missing comma between the SELECT and the FROM statements. The following error message will display: “Incorrect syntax near ‘FROM’.”
Example code:
SELECT customer_id SUM(order_total)
FROM orders
WHERE order_date >= '2022–01–01'
GROUP BY customer_id
HAVING SUM(order_total) > 1000;Solution
Check your SQL query’s syntax, and ensure you have used the correct keywords and punctuation. Double-check your spelling and make sure that you have not missed any commas or semicolons.
SELECT customer_id, SUM(order_total)
FROM orders
WHERE order_date >= '2022–01–01'
GROUP BY customer_id
HAVING SUM(order_total) > 1000;2. Data Type Mismatch
Error message: “Data type mismatch in criteria expression”
This error occurs when you are trying to compare values of different data types, such as a string and a number.
In the example below, the product_id column is stored as an integer, but the value being compared to is a string so the following error message will display: “conversion failed when converting the varchar value ‘ABC’ to data type int.”
Example code:
SELECT product_id, product_name
FROM products
WHERE product_id > '100';Solution
Check the data types of the values that you are comparing, and make sure that they are compatible. For example, if you are comparing a string and a number, you may need to convert one of the values to the other data type.
SELECT product_id, product_name
FROM products
WHERE product_id > 100;3. Invalid Object Name
Error message: “Invalid object name [object name]”
This error occurs when referencing an object that does not exist in the database. In the example below, there is no table or view named “orders” in the database, so the following error message will display: “Invalid object name ‘orders’.”
Example code:
SELECT *
FROM orders;Solution
Check the spelling of the object name and make sure that it exists in the database. If the object does not exist, create it or correct the spelling of the object that does exist in the database. You can always refer to the schema of the database. In this corrected code below, the “orders” table is in the “sales” schema, so it must be referenced as “sales.orders”.
SELECT *
FROM sales.orders;4. Duplicate Key
Error message: “Violation of PRIMARY KEY constraint [constraint name]. Cannot insert duplicate key in object [table name].”
A duplicate key error occurs when you are trying to insert a record into a table that already has a record with the same primary key value.
In the example below, there is already a record in the “orders” table with an order_id of 1001, so the following error message will display: “Violation of PRIMARY KEY constraint ‘PK_orders’. Cannot insert duplicate key in object ‘dbo.orders’.”
Example code:
INSERT INTO orders (order_id, customer_id, order_total)
VALUES (1001, 123, 50.00);Solution
Check the data that you are trying to insert and make sure that it does not already exist in the table. If it does, you may need to update the existing record instead of inserting a new one. In the corrected code below, the existing record with an order_id of 1001 is updated with a new order_total value of 50.00, rather than inserting a new record.
UPDATE orders
SET order_total = 50.00
WHERE order_id = 1001;5. Timeout Expired
Error message: “Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.”
This error occurs when the server is taking too long to respond to a request. It usually occurs when a query takes too long to execute, or when there are too many concurrent queries running on the server.
In the example below, the query is taking a long time to execute because the “sales.orders” table is very large and there are no indexes on the columns being used in the query.
Example code:
SELECT *
FROM sales.orders
WHERE order_date >= '2022-01-01'
ORDER BY order_total DESC;Solution
Check the server and network connection to make sure that they are working properly. If the problem persists, you may need to optimize your SQL queries to reduce the amount of time that they take to execute. In the corrected code below, an index is created on the “order_date” column, which allows the query to execute more quickly.
CREATE INDEX idx_orders_order_date ON sales.orders (order_date);
SELECT *
FROM sales.orders
WHERE order_date >= '2022-01-01'
ORDER BY order_total DESC;6. Insufficient Permissions
Error message: “The EXECUTE permission was denied on the object [object name], database [database name], schema ‘dbo’.
An insufficient permissions error occurs when the user does not have the necessary permissions to perform the requested operation, such as querying a table or modifying data.
In the example below, the user running the query does not have permission to select data from the “sales.customers” table, so the following error message will display: “The SELECT permission was denied on the ‘customers’ table.”
Example code:
SELECT *
FROM sales.customers;Solution
Check the user’s permissions and make sure that they have the necessary privileges to execute the statement. If the user does not have the required permissions, you may need to grant them the necessary privileges. In the corrected code below, the necessary permissions are granted to the user “user1”, allowing them to select data from the “sales.customers” table.
GRANT SELECT ON sales.customers TO user1;
-- User1 can now select data from the customers table
SELECT *
FROM sales.customers;So there you have it! A guide of some of the most common SQL error messages, including syntax errors, data type mismatches, invalid object names, duplicate keys, and timeout expired. By understanding these errors and their solutions, you can turn them into valuable learning experiences and improve your SQL skills.
Key takeaway: Remember to double-check your syntax, data types, object names, and query optimization to avoid these common errors.
Debugging can feel like an endless maze, but with every wrong turn, you learn a new path to avoid.
Additional Resources
If you’d like to take your SQL skills to the next level, here are my top three recommendations:
SQLZoo: A Wiki-based interactive tutorial that provides SQL lessons and projects for beginners.
SQLBOLT: A platform that includes a series of engaging exercises that provide a foundational understanding of SQL.
Codeacademy: A structured course as a series of interactive coding tutorials that guide students from the fundamental functionality to various extensive projects.
