Fix Error Msg 11728 “The sequence object ‘…’ has reached its minimum or maximum value.” in SQL Server

If you’re getting an error that reads something like “The sequence object ‘…’ has reached its minimum or maximum value. Restart the sequence object to allow new values to be generated.“, it’s probably because you’re using a sequence that’s reached the minimum or maximum value specified in its definition.

Continue reading

Fix “NEXT VALUE FOR function cannot be used directly in a statement that contains an ORDER BY clause unless the OVER clause is specified.” in SQL Server (Error Msg 11723)

If you’re getting an error that reads “NEXT VALUE FOR function cannot be used directly in a statement that contains an ORDER BY clause unless the OVER clause is specified” in SQL Server, it’s probably because you’re trying to sort the results of a query that uses the NEXT VALUE FOR function without using the OVER clause.

Continue reading

How to Reset a Sequence in SQL Server

In SQL Server, we can use sequences to generate sequence numbers that increment by a specified amount. This means that any new number generated by the sequence will be the next sequential increment as specified in the sequence’s definition.

Normally, this is exactly what we want. We want each number to adhere to the increment that we specified when defining the sequence.

But what if we want to reset the sequence, so that the numbering starts all over again? In other words, we want to restart the sequence from the beginning. Or what if we want to reset the sequence so that it increments in a different range?

Fortunately, we can reset a sequence with the ALTER SEQUENCE statement.

Continue reading

How to Change the Sequence Number of a Database Mail Account within a Profile in SQL Server (T-SQL)

If you’ve already added a Database Mail account to a profile, but you now want to change the sequence number, you can do that with the sysmail_update_profileaccount_sp stored procedure.

When doing this, you need to provide the name or ID of both the account and the profile. This is because you need to specify exactly which profile/account association you want to update.

Continue reading