Stored procedure variable defined nvarchar(max) gets truncated to 4000 chars

Question

When i print the @v_sql parameter i get 4000 chars.

How i fix this?

Source:

BEGIN
    SET NOCOUNT ON;
    DECLARE @v_sql nvarchar(max)
    SELECT @v_sql = N'';
    IF UPPER(@action)='DISABLE'
    BEGIN
        SELECT @v_sql = @v_sql + 'ALTER INDEX '+ QUOTENAME(name,'[')+' ON ' + quotename( OBJECT_SCHEMA_NAME(object_id),'[') +'.'+ quotename( OBJECT_NAME(object_id),'[') + ' DISABLE;'+char(10)
        FROM sys.indexes
        WHERE OBJECTPROPERTY([object_id], 'IsMsShipped') = 0 AND type =2;   
    END
    IF UPPER(@action)='REBUILD'
    BEGIN
        SELECT @v_sql = @v_sql + 'ALTER INDEX '+QUOTENAME(name,'[')+' ON ' +  quotename( OBJECT_SCHEMA_NAME(object_id),'[') +'.'+ quotename( OBJECT_NAME(object_id),'[') + ' REBUILD WITH(SORT_IN_TEMPDB=ON);'+char(10)
        FROM sys.indexes
        WHERE OBJECTPROPERTY([object_id], 'IsMsShipped') = 0 AND type =2;
    END
    IF @v_sql <> ''
    BEGIN
        print @v_sql
        --EXEC sp_executesql @v_sql;
    END
END

check this answer : http://stackoverflow.com/questions/4833549/nvarcharmax-still-being-truncated — Pranay Rana, Jan 09 '15 at 07:52
So i should replace print with select and maybe add a char(13) after char(10)? — e4rthdog, Jan 09 '15 at 07:55
One of the options I saw to use XML (maybe not for your query, but just want to share): `select @SQL as [processing-instruction(x)] FOR XML PATH` — Darka, Jan 09 '15 at 07:57

score 1 · Answer 1 · answered Jan 09 '15 at 07:57

1

it is truncated by print - "The varchar(max) and nvarchar(max) data types are truncated to data types that are no larger than varchar(8000) and nvarchar(4000)."

answered Jan 09 '15 at 07:57

fly_ua

1,034
8
12

score 1 · Answer 2 · edited May 23 '17 at 12:28

You are right, when we get the data in PRINT it is truncated, but it will not truncated when actual data executed, the only thing is we have to define length as MAX.

Sqlserver support 8000 charcter also. Only thing , you can't see, but it will executed by sqlserver.

Means EXEC sp_executesql @v_sql; will execute sqlserver append as many long string. and executed.

I think you get an error, at that we need to print actual query. Right, if yes, then I have one solution like, you can break your code which is independently execute or work.

You can use hardcore value from one the resultset, check that will create an issue, then remove/add some condition which filter the more data.

Finally you got the data which create an issue.

Run only this portion and then second portion.

DECLARE @v_sql nvarchar(max)
    SELECT @v_sql = N'';
    IF UPPER(@action)='DISABLE'
    BEGIN
        SELECT @v_sql = @v_sql + 'ALTER INDEX '+ QUOTENAME(name,'[')+' ON ' + quotename( OBJECT_SCHEMA_NAME(object_id),'[') +'.'+ quotename( OBJECT_NAME(object_id),'[') + ' DISABLE;'+char(10) + char(13)
        FROM sys.indexes
        WHERE OBJECTPROPERTY([object_id], 'IsMsShipped') = 0 AND type =2;   
    END
    IF @v_sql <> ''
       BEGIN
         select @v_sql
         --EXEC sp_executesql @v_sql;
       END

nvarchar(max) still being truncated

score 0 · Accepted Answer · answered Jan 09 '15 at 08:41

In my case i wanted to be able to copy paste the results of the sp so the solution is:

1) Replace print with select 2) Add char(13) after char(10)

BEGIN
    SET NOCOUNT ON;
    DECLARE @v_sql nvarchar(max)
    SELECT @v_sql = N'';
    IF UPPER(@action)='DISABLE'
    BEGIN
        SELECT @v_sql = @v_sql + 'ALTER INDEX '+ QUOTENAME(name,'[')+' ON ' + quotename( OBJECT_SCHEMA_NAME(object_id),'[') +'.'+ quotename( OBJECT_NAME(object_id),'[') + ' DISABLE;'+char(10) + char(13)
        FROM sys.indexes
        WHERE OBJECTPROPERTY([object_id], 'IsMsShipped') = 0 AND type =2;   
    END
    IF UPPER(@action)='REBUILD'
    BEGIN
        SELECT @v_sql = @v_sql + 'ALTER INDEX '+QUOTENAME(name,'[')+' ON ' +  quotename( OBJECT_SCHEMA_NAME(object_id),'[') +'.'+ quotename( OBJECT_NAME(object_id),'[') + ' REBUILD WITH(SORT_IN_TEMPDB=ON);'+char(10) + char(13)
        FROM sys.indexes
        WHERE OBJECTPROPERTY([object_id], 'IsMsShipped') = 0 AND type =2;
    END
    IF @v_sql <> ''
    BEGIN
        select @v_sql
        --EXEC sp_executesql @v_sql;
    END
END

Stored procedure variable defined nvarchar(max) gets truncated to 4000 chars

3 Answers3