If you’re getting SQL Server error 7999 that reads something like “Could not find any index named ‘IX_LastName’ for table ‘Employees’“, it appears that you’re trying to create an index with the DROP_EXISTING argument ON, but there is no existing index of that name.
When we set the DROP_EXISTING argument to ON, SQL Server tries to look for the index to drop before it recreates it with the new definition. If it doesn’t exist, then we get the error.
If you’re getting an error in SQL Server that reads “Incorrect syntax near the keyword ‘Order’“, it could be because you’re using the word Order (or another reserved keyword) in your SQL code.
The 156 error itself merely indicates a syntax error, so there could be a multitude of reasons you might be getting this error. But when it refers to the keyword 'Order', this could suggest that you’re trying to use the word Order as an identifier (such as a column name, table name, etc).
If you’re getting an error that reads something like “Incorrect syntax near the keyword ‘ON’“, it appears that you’re trying to recreate an existing index, but you’re using the wrong syntax for the DROP_EXISTING argument.
The same error occurs when using the wrong syntax to set DROP_EXISTING to OFF; “Incorrect syntax near the keyword ‘OFF’“.
Either way, to fix this issue, be sure to use the right syntax.
Continue readingIf you’re getting SQL Server error 4145 that goes something like “An expression of non-boolean type specified in a context where a condition is expected…“, it’s probably because you’re using a boolean-like syntax in the wrong context.
This error occurs when a non-boolean expression is used in a context where a condition (i.e., something that evaluates to true or false) is expected. This often happens in IF statements, CASE expressions, or WHERE clauses when a value that isn’t a boolean is mistakenly used as a condition.
Even when we do use a boolean value, we can still get this error if we use it in the wrong context.
Continue readingIf you’re getting SQL Server error 107 that goes something like “The column prefix ‘e’ does not match with a table name or alias name used in the query“, look at your column prefix.
This error occurs when we use the asterisk wildcard (*) that’s incorrectly qualified with a column prefix. The prefix, if provided, must match a table name or alias name. If you’re using a table alias, then you must use that instead of the table name.
If you’re getting SQL Server error 547 that reads something like “The DELETE statement conflicted with the REFERENCE constraint “FK_Projects_DepartmentID”. The conflict occurred in database “test”, table “dbo.Projects”, column ‘DepartmentID’“, you’re probably trying to delete data from a parent table of a foreign key relationship.
Continue readingIf you’re getting SQL Server error 1785 that reads something like “Introducing FOREIGN KEY constraint ‘FK__Name’ on table ‘Department’ may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints“, it’s probably because you’ve specified the CASCADE option when trying to create a foreign key with a circular reference, or one that has multiple cascade paths (perhaps your child table has multiple foreign keys to the same primary key on the parent table).
If you’re getting SQL Server error 1989 that reads something like “Cannot enable foreign key constraint ‘FK_Jobs_Employees’ as index ‘PK__Employee__7AD04FF1A39ECAB1’ on referenced key is disabled“, it’s probably because you’re trying to enable a foreign key, when the index that it references is disabled.
For example, if you’ve disabled a primary key on the parent table, and then try to enable a foreign key that references that primary key, then you’ll get this error.
To fix this issue, enable the index for the primary key/referenced key before enabling the foreign key.
Continue readingIf you’re getting SQL Server error 2714 that reads something like “There is already an object named ‘Actors’ in the database” it seems that you’re trying to create an object that already exists.
It could be that you didn’t know that the object had already been created. Or it could be that you’re trying to create a different object, but you’re inadvertently using the same name that another object has. Or it could be that you want both objects to have the same name, but a different schema. In this case, perhaps you inadvertently omitted the schema name from the object.
Continue readingIf you’re getting the “Incorrect syntax…” error in SQL Server when trying to create a filtered index, it could be that you’re using an unsupported operator.
Filtered indexes in SQL Server only support simple comparison operators. If you use an unsupported operator or expression, you’ll likely get the dreaded “Incorrect syntax” error.
To fix, either use a supported operator, or create a view.
Continue reading