SQL Server Portal

An invalid value was specified for argument ‘%.*ls’ for the given data type  is one of the new error messages in SQL Server 2012. This error message is related to Sequence object, a new database feature shipped with SQL Server 2012.

Lets discuss this in detail:
Message Number: 11708

Severity : 16

Error Message: An invalid value was specified for argument ‘%.*ls’ for the given data type.

Error Generation:

Given below is the script that I tried to execute but it resulted with the following error.

Use AdventureWorks2012
GO
CREATE SEQUENCE [dbo].[Seq_sample]
AS [tinyint]
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 256
NO CACHE
GO

Msg 11708, Level 16, State 1, Line 1
An invalid value was specified for argument ‘MAXVALUE’ for the given data type.

Ooopps…… I am unable to execute it.

Resolution:
The issue is quite simple. I created the sequence object using tinyint data type and range of tinyint data type is from 0 to 255 as per MSDN. But if you observe carefully, in my above example, the maximum value of sequence object is 256 and that is more than the tinyint data type maximum range (255).

Lets rewrite the script and correct the maximum value range. It must be less than or equal to the defined data type range.

Use AdventureWorks2012
GO
CREATE SEQUENCE [dbo].[Seq_sample]
AS [tinyint]
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 255
NO CACHE
GO

Conclusion :
Remember, whenever you create the sequence object always keep the minimum and maximum value within the range of the data type.

Let me know if you have a better solution.