Tag: t-sql

2 Ways to Set a Maximum and/or Minimum Value when Creating a Sequence in SQL Server

Posted on by Ian

By default, SQL Server sets its own minimum and maximum values for sequence objects based on the data type of the sequence object. Assuming we don’t set the data type for the sequence, these min/max values are based on the bigint data type (because that’s the default data type for sequence objects in SQL Server).

However, we can also set our own minimum and maximum values for our sequences. We can do this either explicitly (by setting the MAXVALUE and MINVALUE properties) or implicitly (by setting the data type).

Continue reading →

Fix Error Msg 6825 “ELEMENTS option is only allowed in RAW, AUTO, and PATH modes of FOR XML” in SQL Server

Posted on by Ian

If you’re getting error msg 6825 that reads “ELEMENTS option is only allowed in RAW, AUTO, and PATH modes of FOR XML“, it’s probably because you’re trying to use the ELEMENTS directive while using EXPLICIT mode of the FOR XML clause.

As the message alludes to, this option is not available when using the EXPLICIT mode (it’s only allowed with the RAW, AUTO, and PATH modes).

However, SQL Server does provide us with an equivalent that provides the same result. When using EXPLICIT mode, we can use the ELEMENT directive instead. We apply this to the column names, instead of the end of the query. We can alternatively use the ELEMENTXSINIL directive to ensure that elements are generated even if the column contains a NULL value.

Continue reading →

Fix “Argument ‘AS’ cannot be used in an ALTER SEQUENCE statement.” (Error Msg 11711) in SQL Server

Posted on by Ian

If you’re getting an error that reads “Argument ‘AS’ cannot be used in an ALTER SEQUENCE statement.” in SQL Server, it’s probably because you’re trying to change the data type of a sequence object.

We can’t actually change the data type of a sequence object, so this error is to be expected.

If you need to change the data type of a sequence object, you’ll need to drop the sequence and recreate it with the correct data type.

Continue reading →

Three ISDATE() Alternatives that Work with DATETIME2 Values in SQL Server

Posted on by Ian

SQL Server’s ISDATE() function checks whether or not an expression is a valid date. However, you may be aware that this function doesn’t work on datetime2 values. On datetime2 values it returns 0, which means it’s not a valid date, even when the value is a valid date.

This is obviously not ideal, because the datetime2 type is a valid date type. Microsoft even recommends that we use datetime2 instead of datetime for our dates, as it aligns with the SQL Standard and it provides more fractional seconds precision.

Anyway, below are three options we can use to check whether a datetime2 value is a valid date.

Continue reading →

How to Create Decrementing Sequence Numbers in SQL Server

Posted on by Ian

When we create a sequence in SQL Server, we have the option of making it an incrementing sequence or decrementing.

By “decrementing”, I mean that the sequence decreases instead of increases. For example, if it starts at 100, the next value is 99, and then 98, and so on.

To create a sequence that decrements, all we do is provide a negative value for the INCREMENT BY argument.

Continue reading →

Fix Error Msg 235 “Cannot convert a char value to money. The char value has incorrect syntax” in SQL Server

Posted on by Ian

If you’re getting error msg 235 which reads Cannot convert a char value to money. The char value has incorrect syntax, it’s probably because you’re trying to convert a string to the money data type, but the string isn’t in a format that can be converted to the money type.

To fix this issue, make sure you’re trying to convert the right value (perhaps you’ve got the wrong column or variable). If you’re sure you’re trying to convert the right value, try a workaround like the one below.

Continue reading →

How to Change the Range of a Sequence in SQL Server

Posted on by Ian

SQL Server sequence objects allow us to increment or decrement through a range of numbers. Once set, we don’t need to change anything. We can simply use NEXT VALUE FOR to generated the next sequential number.

But what if we want to change the sequence to use a different range?

For example, we created a sequence that increments between 0 and 100, but now we want to change it to increment between 200 and 300.

Easy. We can use the ALTER SEQUENCE statement to do just that.

Continue reading →

Fix Msg 529 “Explicit conversion from data type date to int is not allowed” in SQL Server

Posted on by Ian

If you’re getting SQL Server error Msg 529 that reads Explicit conversion from data type date to int is not allowed, it’s because you’re trying to explicitly convert a date data type to an int data type, which is not allowed in SQL Server.

To fix this issue, try converting the date value to a string first, and then to an integer.

Alternatively, change the destination type to one that’s allowed.

Also, check that you’re trying to convert the correct value. For example, you may have selected the wrong column or variable. In this case, selecting the correct column may fix the problem.

Continue reading →