If you’re getting an error that reads something like “‘DAYS’ is not a recognized dateadd option” in SQL Server, it’s because you’re using the DATEADD() function with an invalid datepart argument.
This often happens when you use a plural form of the argument. For example, DAYS instead of DAY. Or HOURS instead of HOUR.
The easiest way to fix this is to provide a valid datepart argument.
If you’re getting error 9810 that reads something like “The datepart hour is not supported by date function dateadd for data type date“, it’s because the datepart that you’re trying to add or subtract a datepart is not supported for the data type of the original value.
This typically happens when you try to add a timepart to a date value. For example, trying to add an hour to a date value will result in this error, because the date type doesn’t support the hour datepart. You can’t have a date value that includes the hour.
If you’re getting an error that reads “JSON aggregates do not support order-by within group when specified with grouping sets, cubes and rollups. Try your query without order-by within group.” it looks like you’re trying to use the ORDER BY clause inside a JSON aggregate function when using GROUPING SETS, CUBE, or ROLLUP.
To fix this error you’ll need to remove ORDER BY from the aggregate function when using those clauses.
If you’re getting SQL Server error 8110 that reads “Cannot add multiple PRIMARY KEY constraints to table…” it’s because you’re trying to add more than one primary key to a table. In SQL Server, a table cannot contain more than one primary key.
It’s quite possible you were trying to create a composite primary key, and so you might need to fix your syntax. If that’s the case, read on.
If you’re getting SQL Server error 13680 that reads something like “Column ‘details’ on table ‘table_name’ is not of JSON data type, which is required to create a JSON index on it.” it looks like you’re trying to create a JSON index on a non-JSON column. You can only create JSON indexes on columns defined with the JSON type.
To fix this issue, be sure that the column is of JSON type before running CREATE JSON INDEX on it.
If you’re getting SQL Server error 13681 that states “A JSON index already exists on column…” , it’s probably because you’re trying to create a JSON index on a column that already has one. In SQL Server, each JSON column can have no more than one JSON index.
The solution here is to simply stop trying to create a second JSON index. Another option would be to drop the existing one and create the new one.
If you’re trying to set the ONLINE option when creating a JSON index in SQL Server, but you’re seeing “Invalid usage of the option ONLINE in the CREATE JSON INDEX statement“, don’t worry – it might not be an error on your part.
It probably depends on things like, are you trying to set ONLINE = ON or ONLINE = OFF? And are you trying to do it around the time I write this article, or sometime in the future?
Let’s take a quick look at what could be happening when you try to use this option.
If you’re getting error Msg 5133 and error Msg 3156 in SQL Server, it would seem that you’re trying to restore a database to a different location, but you’re not specifying the different location. This can happen when you restore a database to a different environment that uses a file paths. A common cause of this error is when restoring a backup from a Windows environment to a Linux or Mac environment.
Fortunately, the fix is easy. All you need to do is map the logical file names to paths that actually exist in the new environment.
If you’ve spent any time working with SQL Server, you’ve almost certainly run into the “Violation of UNIQUE KEY constraint” error.
It’s one of those errors that can show up in a few different ways depending on how your data flows into the database. Sometimes it’s a data quality issue. Sometimes it’s a race condition. Sometimes it’s just a gap in the logic that nobody caught until production. Whatever the cause, there are quite a few different ways to handle it. Some reactively, some proactively. This article walks through the main ones.
If you’re getting SQL Server error 13683 stating “Invalid JSON paths in JSON index“, it sounds like you could be specifying overlapping paths in your JSON index definition.
A JSON index cannot contain overlapping paths.
To fix this, remove the overlap from the specified paths that make up the index definition.