none
nvarchar(max) limit

    Question

  • Is there a limit to nvarchar(max) ?
    It seems to be 4262 characters ?

    I have to use this nvarchar(max) as I am doing this:

    declare @sql nvarchar(max)
    set @sql = 'A very long sql query'

    exec sp_executesql @sql

    Thanks
    Wednesday, January 25, 2012 3:32 PM

Answers

  • Try using

    set @SQL = 'select ' + @AllCols + ' from ' ...

    (all in one statement). Also, everything (@SQL and @AllCols) must be nvarchar(max).


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    • Marked as answer by arkiboys Wednesday, January 25, 2012 9:03 PM
    Wednesday, January 25, 2012 4:17 PM

All replies

  • Hey,

    your SQL is incorrect try this one

    declare @sql nvarchar(max)
    set @sql = 'A very long sql query'
    SELECT @sql
    

     As per MSDN

    Variable-length Unicode character data. ncan be a value from 1 through 4,000. max indicates that the maximum storage size is 2^31-1 bytes. The storage size, in bytes, is two times the number of characters entered + 2 bytes. The data entered can be 0 characters in length. The ISO synonyms for nvarchar are national char varying and national character varying.


    Thanks,
    Sandip Shinde
    Please remember to mark the replies as answers if they help you and unmark them if they provide no help.
    Wednesday, January 25, 2012 3:34 PM
  • If i do a print len(@sql) it only shows 4262 whereas there should be more characters
    Any reason please?
    Wednesday, January 25, 2012 3:37 PM
  • How do you construct your SQL query? Make sure that every part of it is declared as nvarchar(max). We did have threads on the similar issue in this forum before, try searching for them if you will not be able to make it work using the tip above.
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Wednesday, January 25, 2012 3:54 PM
  • How do you construct your SQL query? Make sure that every part of it is declared as nvarchar(max). We did have threads on the similar issue in this forum before, try searching for them if you will not be able to make it work using the tip above.
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    everything is declared as nvarchar(max) or nvarchar(20) or nchar(4)
    Wednesday, January 25, 2012 4:04 PM
  • You have to declare everything as nvarchar(max). All variables used in concatenation must be nvarchar(max). See, if this makes a difference. If not, post your statement.
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Wednesday, January 25, 2012 4:11 PM
  • How do you construct your SQL query? Make sure that every part of it is declared as nvarchar(max). We did have threads on the similar issue in this forum before, try searching for them if you will not be able to make it work using the tip above.
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    This is what I am doing:
    set @sql = 'select '
    set @sql += @AllCols
    set @sql += ' from'


    Wednesday, January 25, 2012 4:15 PM
  • Try using

    set @SQL = 'select ' + @AllCols + ' from ' ...

    (all in one statement). Also, everything (@SQL and @AllCols) must be nvarchar(max).


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    • Marked as answer by arkiboys Wednesday, January 25, 2012 9:03 PM
    Wednesday, January 25, 2012 4:17 PM
  • Hi,

    Here you are not executing SP. but you have to execute select or print statement.

    PRINT @sql

    OR

    SELECT @sql.


    Cheers,
    IT-Singh
    Wednesday, January 25, 2012 4:53 PM
  • Thanks
    Wednesday, January 25, 2012 9:03 PM