none
SQL Server 2014 misleading error messages RRS feed

  • General discussion

  • Hello,

    I use "Microsoft SQL Server 2014 - 12.0.2000.8 (X64)   Feb 20 2014 20:04:26   Copyright (c) Microsoft Corporation  Express Edition (64-bit)".

    While setting data types length I get misleading error messages.

    Please try:

    DECLARE @txt as NVARCHAR(8001);

    GO

    DECLARE @txt2 as NVARCHAR(8000);

    GO

    DECLARE @num as INT(8001);

    GO

    DECLARE @num2 as INT(8000);

    GO

    I haven't tried it on other versions of SQL.

    Any comments are welcomed.

    Monday, August 29, 2016 8:49 PM

All replies

  • Why the mystery? I put the error messages below.

    Basically, the way I read this is that the SQL Server parser is trying various validates.  The first that hits is a test of the parameter in the parentheses.  If it is more than 8000, it is an impossible value. 

    Next you have NVARCHAR(8000).  8000 is a possible value for varchar, char, binary and varbinary.  But it isn't a possible value for NVARCHAR.

    Next you have int (8001).  8001 is again impossible, the max value for any data type error is shown.

    Finally you have int (8000)  8000 is not impossible, it's just that you can't set a column width for an int.

    Now, what is misleading?

    Msg 131, Level 15, State 3, Line 1

    The size (8001) given to the type 'nvarchar' exceeds the maximum allowed for any data type (8000).

    Msg 2717, Level 16, State 2, Line 2

    The size (8000) given to the parameter '@txt2' exceeds the maximum allowed (4000).

    Msg 131, Level 15, State 3, Line 2

    The size (8001) given to the type 'int' exceeds the maximum allowed for any data type (8000).

    Msg 2716, Level 16, State 1, Line 2

    Column, parameter, or variable #1: Cannot specify a column width on data type int.


    Russel Loski, MCSE Data Platform/Business Intelligence Twitter: @sqlmovers; blog: www.sqlmovers.com

    Monday, August 29, 2016 9:17 PM
  • Please share error message!

    Is that above is error message?

    DECLARE @txt as NVARCHAR(8001);

    GO

    DECLARE @txt2 as NVARCHAR(8000);

    GO

    DECLARE @num as INT(8001);

    GO

    DECLARE @num2 as INT(8000);

    GO


    Santosh Singh

    Monday, August 29, 2016 9:22 PM
  • Thank you for your clarification. Although I think that after entering DECLARE @txt NVARCHAR(8001) it will be more simple (for the user) to get "The size (8001) given to the parameter '@txt' exceeds the maximum allowed (4000)." instead of "The size (8001) given to the type 'nvarchar' exceeds the maximum allowed for any data type (8000)."

    But I must admit that yes - all of those messages are correct.

    Tuesday, August 30, 2016 5:22 AM
  • 8000 is the max size in bytes, NVARCHAR requires 2 bytes per character => max 4000 characters

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Tuesday, August 30, 2016 5:38 AM