nvarchar limits to 8000 characters within stored procedure

Question

I have a problem with my stored procedure :

  ALTER PROCEDURE [dbo].[Sp_Calculate_TimeSheet_Global_Info]
        @DateDebut date,
        @DateFin date,
        @UserId int,
        @CA varchar(10)    
    AS


    BEGIN

       DECLARE @TaskQuery nvarchar(MAX) ;
       DECLARE @OPENQUERY nvarchar(MAX),@TSQL nvarchar(MAX), @LinkedServer nvarchar(MAX);
      DECLARE @DateDebut1 date;
      DECLARE @DateFin1 date;
      DECLARE @UserId1 int; 
      DECLARE @Query nvarchar(MAX);

       DECLARE @cond1 nvarchar(MAX);
       DECLARE @cond2 nvarchar(MAX);
       DECLARE @cond3 nvarchar(MAX);
       DECLARE @cond4 nvarchar(MAX);
       DECLARE @cond5 nvarchar(MAX);
       DECLARE @cond6 nvarchar(MAX);
       DECLARE @cond7 nvarchar(MAX);
       DECLARE @cond8 nvarchar(MAX);
       DECLARE @cond9 nvarchar(MAX);
       DECLARE @cond10 nvarchar(MAX);
       DECLARE @cond11 nvarchar(MAX);
       DECLARE @cond12 nvarchar(MAX);
       DECLARE @cond13 nvarchar(MAX);

       DECLARE @op nvarchar(MAX);
       DECLARE @where nvarchar(MAX);
       DECLARE @exec nvarchar(MAX);

       SET NOCOUNT ON;

     SET @LinkedServer = 'TASK'
     SET @OPENQUERY = 'SELECT * FROM OPENQUERY('+ @LinkedServer + ','''
     SET @DateDebut1 = @DateDebut;
     SET @DateFin1 = @DateFin;
     SET @UserId1 = @UserId;

     set @op = ' UNION ';
     set @where = ' where 1=1 ';

     set @cond1 = ' LEFT OUTER JOIN tickets tkt ON tkt.ttick_id = h.ttick_id  ';
     set @cond2 = ' RIGHT OUTER JOIN tickets tkt ON tkt.ttick_id = h.ttick_id  ';
     set @cond3 = ' LEFT OUTER JOIN ptasks tsk ON h.ptask_id = tsk.ptask_id  ';
     set @cond4 = ' RIGHT OUTER JOIN tasks tsk ON h.ptask_id = tsk.ptask_id  ';
     set @cond5 = ' LEFT OUTER JOIN phases ph ON tsk.phase_id = ph.phase_id  ';
     set @cond6 = ' RIGHT OUTER JOIN tasks tsk ON h.ptask_id = tsk.ptask_id  ';
     set @cond7 = ' LEFT OUTER JOIN projects p ON ph.proj_id = p.proj_id  ';
     set @cond8 = ' RIGHT OUTER JOIN projects p ON ph.proj_id = p.proj_id  ';
     set @cond9 = ' and h.hours_spent >= str_to_date(''''' + convert(varchar,@DateDebut,103) + ''''', ''''%d/%m/%Y'''')';
     set @cond10 = ' and h.hours_spent <= str_to_date(''''' + convert(varchar,@DateFin,103) + ''''', ''''%d/%m/%Y'''')';
     set @cond11 = ' and h.user_id=' + cast(@UserId as varchar);
     set @cond12 = ' and 
                       ( ph.phase_name like ''''' + '%' + @CA +'%' +''''' and (p.proj_name not like ''''' + '%' + @CA +'%' +''''' or p.proj_name is null) ) or
                       ( (ph.phase_name not like ''''' + '%' + @CA +'%' +''''' or ph.phase_name is null) and (p.proj_name not like ''''' + '%' + @CA +'%' +''''' or p.proj_name is null) and tsk.ptask_name like ''''' + '%' + @CA +'%' +''''') or
                       ( (ph.phase_name not like ''''' + '%' + @CA +'%' +''''' or ph.phase_name is null) and (p.proj_name not like ''''' + '%' + @CA +'%' +''''' or p.proj_name is null) and (tsk.ptask_name not like ''''' + '%' + @CA +'%' +''''' or tsk.ptask_name is null) and tkt.ttick_name like ''''' + '%' + @CA +'%' +''''') or
                       ( (p.proj_name not like ''''' + '%' + @CA +'%' +''''' or p.proj_name is null) and tsk.ptask_name like ''''' + '%' + @CA +'%' +''''' and (tsk.phase_id is null or tsk.phase_id =-1) ) or
                       ( (p.proj_name not like ''''' + '%' + @CA +'%' +''''' or p.proj_name is null) and tkt.ttick_name like ''''' + '%' + @CA +'%' +''''')
                        ';
     set @cond13 = ''') src';




      SET @TaskQuery  =N' SELECT  h.hours_id , h.hours_spent as  TimeSheet_Date, h.hours_hours as Hour_Number,h.hours_note as Hour_Note,(case when h.ttick_id is null or h.ttick_id=-1 then tsk.ptask_name else tkt.ttick_name end) as Task_Ticket_Libelle,(case when h.ttick_id is null or h.ttick_id=-1 then h.ptask_id else h.ttick_id end) as Task_Ticket_Id,h.proj_id as Project_Id,(case when h.ttick_id is null or h.ttick_id=-1 then 1 else 0 end) as Is_Task,h.user_id as User_Id,p.proj_name as Project_Name, u.user_uname as User_Name from users u RIGHT OUTER JOIN user_hours h  on u.user_id=h.user_id ' ;



      SET  @Query = @TaskQuery  ;

      SET @TaskQuery = @Query+   @cond1  ;

      SET @TaskQuery = @TaskQuery + @op +  @Query + @cond2    ;


      SET  @Query =  @TaskQuery  ;
      SET @TaskQuery = @Query + @cond3   ;
      SET @TaskQuery =@TaskQuery + @op +  @Query + @cond4    ;


      SET  @Query = @TaskLandQuery ;
      SET @TaskQuery = @Query + @cond5   ;
      SET @TaskQuery =@TaskQuery + @op +  @Query + @cond6    ;



      SET  @Query = @TaskQuery ;
      SET @TaskQuery = @Query + @cond7   ;
      SET @TaskQuery =@TaskQuery + @op +  @Query + @cond8    ;

      SET @TaskQuery = @TaskQuery + @where   ;



    IF(@DateDebut is not null)
    BEGIN 
       SET @TaskQuery = @TaskQuery + @cond9 ;
    END

    IF(@DateFin is not null)
    BEGIN 
       SET @TaskQuery =@TaskQuery + @cond10   ;
    END


    IF(@UserId is not null)
    BEGIN 
       SET @TaskQuery =  @TaskQuery +  @cond11   ;
    END

    IF(@CA is not null)
    BEGIN 
       SET @TaskQuery =  @TaskQuery + @cond12   ; 
    END

    set @TaskQuery = @TaskQuery + @cond13    ; 


      set @exec = @OPENQUERY+@TaskQuery;




    delete  from [dbo].[Timesheet_Global_Info]; 

    insert into [dbo].[Timesheet_Global_Info](
                [hours_id]
               ,[TimeSheet_Date]
               ,[Hour_Number]
               ,[Hour_Note]
               ,[Task_Ticket_Libelle]
               ,[Task_Ticket_Id]
               ,[Project_Id]
               ,[Is_Task]
               ,[User_Id]
               ,[Project_Name]
               ,[User_Name] 
               )

         EXEC (@exec) ; 

END

When I execute this procedure, I get this error:

Msg 103, Level 15, State 1, Line 3 The string that starts with SELECT h.hours_id, h.hours_spent have TimeSheet_Date, h.hours_hours have Hour_Number, h.hours_note have Hour_Note, (CASE WHEN h.ttick 'is too long. The maximum length is 8000.

the exception is due to this line EXEC (@exec) ; .

So I need to know

What is the reason of this error?
How can I fix it?

These links will help you - http://www.codeproject.com/Questions/299179/the-character-string-is-too-long-Maximum-length-is and http://stackoverflow.com/questions/11131958/what-is-the-maximum-characters-for-the-nvarcharmax — MusicLovingIndianGirl, Sep 28 '16 at 09:27

score 3 · Answer 1 · answered Sep 28 '16 at 09:28

3

You are using OPENQUERY and as per MSDN, the max size for query is 8KB i.e. 8000 characters which is exceeding in your query.

OPENQUERY ( linked_server ,'query' )

' query ' Is the query string executed in the linked server. The maximum length of the string is 8 KB.

answered Sep 28 '16 at 09:28

Mudassir Hasan

28,083
20
99
133

@mhasen So How can I fix this – Lamloumi Afif Sep 28 '16 at 09:51

Sandip - Frontend Developer · Answer 2 · 2016-09-28T09:49:52.930

1

You exceed the max length character, in this type of case you have to split your dynamic query in multiple part and execute by combine.

Declare @Query1 VARCHAR(MAX),@Query2 VARCHAR(MAX)

SET @Query1='SELECT * FROM'
SET @Query2=' Employee'

EXEC (@Query1+@Query2)

In your case may be

EXEC (@OPENQUERY+@TaskQuery);

If you still got same error, please split your variables into more...

edited Sep 28 '16 at 09:49

answered Sep 28 '16 at 09:43

Sandip - Frontend Developer

14,397
4
35
61

as @mhasen mentionned, even `EXEC (@OPENQUERY+@TaskQuery);` didn't work for me – Lamloumi Afif Sep 28 '16 at 09:51
as i said he has to split in more in that case – Sandip - Frontend Developer Sep 28 '16 at 09:51
the unique signature of OpenQuery is `OPENQUERY ( linked_server ,'query' )` so it is not possible to split the second argument – Lamloumi Afif Sep 28 '16 at 09:55
I think you have to split @TaskQuery – Sandip - Frontend Developer Sep 28 '16 at 09:59

gofr1 · Accepted Answer · 2016-09-28T11:53:07.170

1

Why not use sp_executesql?

EXEC sp_executesql @exec

As I can see @exec is nvarchar(max), you can pass it without a problem.

On 64-bit servers, the size of the string is limited to 2 GB, the maximum size of nvarchar(max).

EDIT

Use sp_executesql INSTEAD of OPENQUERY

DECLARE @paramDef nvarchar(max) = '@TaskQuery nvarchar(max)'

SELECT @exec = 'INSERT INTO [dbo].[Timesheet_Global_Info] EXEC '+QUOTENAME(@LinkedServer)+'.database.dbo.sp_executesql @TaskQuery',

EXEC sp_executesql @exec, @paramDef, @TaskQuery=@TaskQuery

edited Sep 28 '16 at 11:53

answered Sep 28 '16 at 11:22

gofr1

15,741
11
42
52

the problem is in `OpenQuery` function not in the size of the variable `@exec` – Lamloumi Afif Sep 28 '16 at 11:27
1

I got it! You are trying to execute some select from OPENQUERY from linked server, and insert this data somewhere? Am I getting this right? You can use `EXEC remoteserver.database.dbo.sp_executesql 'dynamic SQL';` to execute query without OPENQUERY. That will solve your problem with 8000 character string. – gofr1 Sep 28 '16 at 11:30
1

I add some info to my answer, hope this will help you. – gofr1 Sep 28 '16 at 11:43

nvarchar limits to 8000 characters within stored procedure

3 Answers3