errors

Fix “Cannot insert explicit value for identity column in table” in SQL Server (Error 544)

by

If you’re getting an error that reads “An explicit value for the identity column in table ‘Dogs’ can only be specified when a column list is used and IDENTITY_INSERT is ON” in SQL Server, it appears that you’re trying to insert a value into an IDENTITY column.

Specifically, you’re trying to do that while the IDENTITY_INSERT option is set to OFF.

To fix this issue, either enable IDENTITY_INSERT before inserting the value, or omit the value from your list of values to insert (and let the IDENTITY column do it’s thing).

Read more

Fix Error 1909 “Cannot use duplicate column names in index” in SQL Server

by

If you’re getting SQL Server error 1909 that reads something like “Cannot use duplicate column names in index. Column name ‘FirstName’ listed more than once“, it appears that you’re trying to create an index, but you’re listing the same column multiple times.

Each column can only be listed once in the index definition.

To fix this issue, make sure each column is listed only once.

Read more

Fix “The objects … in the FROM clause have the same exposed names” in SQL Server (Error 1013)

by

If you’re getting an error that reads something like “The objects “employees” and “employees” in the FROM clause have the same exposed names. Use correlation names to distinguish them’” in SQL Server, it could be that you’re trying to do a self-join without assigning table aliases.

When doing self-joins, we need to provide aliases for the tables so that SQL Server can distinguish between each instance.

To fix this issue, be sure to use unique table aliases in your query.

Read more

Fix “Create View or Function failed because no column name was specified for column” in SQL Server (Error 4511)

by

If you’re getting an error that reads “Create View or Function failed because no column name was specified…” in SQL Server, perhaps you’re using a function or an arithmetic expression in the SELECT list, but you haven’t provided a name for the column.

As the error message alludes to, you need to provide a column name when doing stuff like that.

Read more

Fix “Cannot create index on view because it contains the DISTINCT keyword” in SQL Server (Error 10100)

by

If you’re getting an error that reads “Cannot create index on view “demo.dbo.vDistinctPrice” because it contains the DISTINCT keyword…” etc etc in SQL Server, it appears that you’re trying to create an index on a view that contains the DISTINCT keyword.

As the error message alludes to, this is not allowed.

Read more

Fixing Error 258 “Cannot call methods on nvarchar(max)” When a Subquery Uses FOR XML in SQL Server

by

If you’re running a subquery that uses FOR XML to produce XML in SQL Server, but you’re getting error 258, which reads “Cannot call methods on nvarchar(max)” it might be that you’re trying to query the XML in the outer query, but your inner query isn’t outputting the XML as an actual xml type.

The FOR XML result must be an xml type in order to process the result on the server.

To fix this issue, make sure the subquery outputs the result using the xml data type.

Read more

Fix Error “The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions…” in SQL Server (Error 1033)

by

If you’re getting an error that reads “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” in SQL Server, it appears that you’re using the ORDER BY clause in a place that it must also be accompanied by a TOP, OFFSET or FOR XML clause, but you’re not including one of those clauses.

To fix this issue, be sure to include one of those clauses in your query if you need to use the ORDER BY clause.

Read more

Fix “The correlation name … is specified multiple times in a FROM clause” in SQL Server (Error 1011)

by

If you’re getting an error that reads something like “The correlation name ‘a’ is specified multiple times in a FROM clause’” in SQL Server, it could be that you’re doing a join on two tables, but you’re assigning them the same aliases.

Each table’s alias must be unique.

To fix this issue, be sure to use unique table aliases in your query.

Read more

Fix “Cannot create index on view because the view is not schema bound” in SQL Server (Error 1939)

by

If you’re getting an error that reads something like “Cannot create index on view ‘MyView’ because the view is not schema bound” in SQL Server, it sounds like you’re trying to create an index on a view that’s not schema bound.

Indexes can only be created on schema bound views.

To fix this issue, define the view as schema bound before creating the index.

Read more

Fix “Computed column … cannot be persisted because the column is non-deterministic” in SQL Server (Error 4936)

by

If you’re getting an error that reads something like “Computed column ‘c3’ in table ‘t1’ cannot be persisted because the column is non-deterministic” in SQL Server, it appears that you’re trying to persist a computed column that’s nondeterministic.

A nondeterministic function or expression returns a different result for the same input. If a column uses such an expression, then it will be nondeterministic. A computed column must be deterministic.

Read more