T-SQL Error Reference

Syntax Errors Msg 102

Indicates a problem with the structure or grammar of your T-SQL statement. This is often due to misplaced keywords, missing punctuation, or incorrect object names.

Common Causes:

• Typographical errors in keywords (e.g., SELECT vs. SEELCT).
• Missing or misplaced commas, parentheses, or semicolons.
• Incorrect order of clauses (e.g., WHERE before FROM).
• Using reserved keywords as identifiers without quoting.

-- Example of a syntax error
SELECT column1 FROM my_table WHERE column2 = 'value' AND;

Solution: Carefully review the T-SQL statement, paying close attention to keywords, punctuation, and the order of clauses. Use SQL Server Management Studio's (SSMS) syntax highlighting and IntelliSense to help identify issues.

Ambiguous Column Names Msg 207

Occurs when a column name exists in more than one table in the FROM clause, and the query doesn't specify which table's column to use.

Common Causes:

• Joining tables that have columns with the same name without aliasing.
• Referring to a column that doesn't exist in the scope of the current query.

-- Example of ambiguous column name
SELECT OrderID FROM Customers JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

Solution: Qualify the column name with the table name or table alias. For example: SELECT Orders.OrderID or, if using aliases: SELECT o.OrderID FROM Customers c JOIN Orders o ON c.CustomerID = o.CustomerID;

Invalid Column Name Msg 207

Indicates that the column name specified in the query does not exist in any of the tables or views referenced in the FROM clause.

Common Causes:

• Typographical error in the column name.
• The column has been renamed or dropped.
• The column is not part of the selected tables/views.

-- Example of invalid column name
SELECT emplyee_name FROM Employees;

Solution: Verify the exact spelling of the column name by checking the table schema using SSMS or by running sp_help 'YourTableName'. Ensure the column exists in the tables included in your query.

Conversion Failed When Converting Datatype Msg 245

The query attempted to convert a value to a data type that it cannot be implicitly or explicitly converted to.

Common Causes:

• Trying to insert non-numeric data into a numeric column.
• Attempting to convert a date string in an incorrect format.
• Using CAST or CONVERT with incompatible source and target data types.

-- Example: Trying to convert 'abc' to an integer
SELECT CAST('abc' AS INT);

Solution: Ensure the data being converted is compatible with the target data type. Use functions like ISNUMERIC() or TRY_CAST()/TRY_CONVERT() (SQL Server 2012+) to handle potential conversion errors gracefully. Validate input data before attempting conversion.

Violation of PRIMARY KEY / UNIQUE KEY Constraint Msg 2627 / Msg 2601

Msg 2627 indicates a violation of a PRIMARY KEY constraint, meaning you tried to insert or update a row with a value that already exists in the primary key column(s). Msg 2601 is similar but specifically for UNIQUE constraints.

Common Causes:

• Attempting to insert a record with a duplicate primary key or unique key value.
• An update operation results in duplicate values in a unique column.

-- Assuming an Orders table with OrderID as PRIMARY KEY
INSERT INTO Orders (OrderID, CustomerID) VALUES (1001, 1); -- If OrderID 1001 already exists

Solution: Check if the record already exists before inserting. If updating, ensure the new value does not violate uniqueness. Use MERGE statements or check for existence with IF NOT EXISTS before performing insert/update operations.

Violation of FOREIGN KEY Constraint Msg 547

Indicates that a FOREIGN KEY constraint has been violated. This happens when you try to perform an action that would break the referential integrity between tables.

Common Causes:

• Trying to insert a value into a foreign key column that does not exist in the referenced primary/unique key column of the parent table.
• Trying to delete or update a row in a parent table when child rows still reference it (and cascading delete/update is not enabled).

-- Assuming Customers.CustomerID is PRIMARY KEY and Orders.CustomerID is FOREIGN KEY
INSERT INTO Orders (OrderID, CustomerID) VALUES (1002, 999); -- If CustomerID 999 does not exist in Customers

Solution: Ensure that the value being inserted into the foreign key column exists in the parent table's key column. When deleting or updating parent rows, either ensure no child rows reference them, or configure appropriate ON DELETE and ON UPDATE actions (CASCADE, SET NULL, SET DEFAULT) on the foreign key constraint.

Deadlock Victim Msg 1205

This error means that SQL Server detected a deadlock situation and chose one of the processes involved as the "victim" to resolve the deadlock. The victim's transaction is rolled back.

Common Causes:

• Two or more transactions are waiting for locks held by each other.
• Inconsistent order of accessing resources (tables, rows) across different transactions.

Solution: Review application logic to ensure transactions access resources in a consistent order. Optimize queries and transactions to acquire locks for the shortest duration possible. Consider using appropriate transaction isolation levels or deadlock priority settings. Monitor deadlock graphs using Extended Events or SQL Server Profiler.

Transaction Rollback Msg 3902

Indicates that a transaction has been rolled back, often due to another error (like deadlock, constraint violation, or an explicit ROLLBACK statement).

Common Causes:

• Deadlock situations (Msg 1205).
• Constraint violations.
• Explicit ROLLBACK TRANSACTION statements.
• Errors occurring within a TRY...CATCH block that lead to rollback.

Solution: The underlying cause needs to be identified. If it's due to deadlock, address that. If it's a constraint violation, fix the data or logic. If it's an expected rollback, ensure it's handled correctly in the application code.

Object Not Found Msg 208

This error signifies that the specified object (table, view, stored procedure, function, etc.) does not exist in the current database or is not accessible.

Common Causes:

• Typographical error in the object name.
• The object does not exist.
• The object exists in a different database and is not qualified with the database name.
• Lack of necessary permissions to access the object.

-- Example: Table misspelled
SELECT * FROM Employess;

Solution: Double-check the spelling of the object name. Ensure the object exists in the current database or qualify it with the correct database name (e.g., DatabaseName.SchemaName.ObjectName). Verify user permissions.