Tag: mssql

Fix “Invalid object name ‘GENERATE_SERIES'” in SQL Server

Posted on by Ian

If you’re getting SQL Server error 208 that reads “Invalid object name ‘GENERATE_SERIES’“, it could be that you’re calling GENERATE_SERIES() in a version of SQL Server that doesn’t support this function.

The GENERATE_SERIES() function was introduced in SQL Server 2022 (16.x), and so if we try to call it in an earlier version of SQL Server, we’ll get the above error.

So if you’re running this on an earlier version, you’ll need to upgrade before you can run it successfully. You’ll also need to run it on a database with a compatibility level of at least 160.

Continue reading →

Fix “‘GENERATE_SERIES’ is not a recognized built-in function name” in SQL Server

Posted on by Ian

If you’re getting an error that reads “‘GENERATE_SERIES’ is not a recognized built-in function name” in SQL Server, it could be that you’re calling GENERATE_SERIES() in the wrong context.

In SQL Server, GENERATE_SERIES() is a relational operator that returns a series of values from a given start and stop point.

When we call GENERATE_SERIES() in a SQL query, we don’t include it in the SELECT list, like we would with many SQL functions. Instead, we reference it in the FROM clause, like we would reference a table.

So if you’re getting the above error, it could be that you’re inadvertently making this mistake. To fix, be sure to call GENERATE_SERIES() in the correct context.

Continue reading →

Understanding the Right Shift and Left Shift Operators in SQL Server

Posted on by Ian

SQL Server 2022 introduced a bunch of new bit manipulation functions and operators. Amongst these are the right shift and left shift operators.

The right shift and left shift operators can be used to shift bits to the left or right. Given an integer or binary expression, these operators can shift bits by the amount that we specify.

Continue reading →

Fix “Window element in OVER clause can not also be specified in WINDOW clause” in SQL Server

Posted on by Ian

If you’re getting error number 4123 that reads “Window element in OVER clause can not also be specified in WINDOW clause” in SQL Server, it’s probably because you’re referring to a named window with the same clause that’s in the named window.

When we use the OVER clause to refer to a named window in SQL Server, we can’t include a clause that’s also present in the named window.

To fix the issue, remove one of the clauses – either the one in the OVER clause or the one in the named window.

Continue reading →

Using Fractions when Generating a Series in SQL Server

Posted on by Ian

When we use SQL Server’s GENERATE_SERIES() function to create a series, we provide the start and stop points as numbers. And if we provide the optional step argument, then it’s provided as a number too.

The numbers we provide can be decimal fractions if required. This means that we can create a series made up of decimal fractions. But we still need to ensure that all our arguments are of the same type.

Continue reading →

Fix “Procedure or function GENERATE_SERIES has too many arguments specified” in SQL Server

Posted on by Ian

If you’re getting error 8144 with a message that reads “Procedure or function GENERATE_SERIES has too many arguments specified” in SQL Server, it’s because you’re passing too many arguments to the GENERATE_SERIES() function.

The GENERATE_SERIES() function/relational operator accepts a minimum of two arguments, and a maximum of three (at least, this is the case at the time of writing).

So to fix this error, be sure to provide either two arguments or three when using the GENERATE_SERIES() function.

Continue reading →

Creating Named Windows in SQL Server with the WINDOW Clause

Posted on by Ian

Many RDBMSs support the concept of named windows – windows that we can reference from within an OVER clause when defining a window function.

For those of us using SQL Server, we had to wait until the release of SQL Server 2022 before we could create named windows for our window functions. But with the release of SQL Server 2022, we now have the option of using named windows in our window functions.

Continue reading →