nvarchar begrenzt auf 8000 character innerhalb der gespeicherten Prozedur

Ich habe ein Problem mit meiner gespeicherten Prozedur:

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 

Wenn ich diese Prozedur ausführe, bekomme ich diesen Fehler:

Msg 103, Level 15, State 1, Zeile 3 Die characterfolge, die mit SELECT beginnt h.hours_id, h.hours_spent haben TimeSheet_Date, h.hours_hours haben Hour_Number, h.hours_note haben Hour_Note, (CASE WHEN h.ttick 'ist zu lang. Die maximale Länge beträgt 8000.

die exception ist auf diese Zeile EXEC (@exec) ; .

Also muss ich es wissen

  1. Was ist der Grund für diesen Fehler?
  2. Wie kann ich das beheben?

Warum nicht sp_executesql verwenden ?

 EXEC sp_executesql @exec 

Wie ich sehen kann @exec ist nvarchar(max) , können Sie es ohne ein Problem passieren.

Bei 64-Bit-servern ist die Größe des Strings auf 2 GB begrenzt, die maximale Größe von nvarchar (max).

BEARBEITEN

Verwenden Sie sp_executesql INSTEAD von 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 

Sie verwenden OPENQUERY und nach MSDN ist die maximale Größe für die Abfrage 8KB, dh 8000 character, die in Ihrer Abfrage überschritten wird.

 OPENQUERY ( linked_server ,'query' ) 

'query' Ist die Abfragezeichenfolge im verknüpften server ausgeführt. Die maximale Länge des Strings beträgt 8 KB.

Sie überschreiten die max Länge character , in dieser Art von Fall müssen Sie Ihre dynamische Abfrage in mehreren Teil zu teilen und führen Sie durch kombinieren.

 Declare @Query1 VARCHAR(MAX),@Query2 VARCHAR(MAX) SET @Query1='SELECT * FROM' SET @Query2=' Employee' EXEC (@Query1+@Query2) 

In deinem Fall kann es sein

 EXEC (@OPENQUERY+@TaskQuery); 

Wenn du immer noch den gleichen Fehler hast, bitte gib deine variables in mehr …