SQL Server Express 2008 R2 nvarchar(max) truncated 1

[PeDa]

I am using dynamic SQL to query either one of three datasets or a union query of all three. It works fine for the individual datasets, but the nvarchar(max) string containing the long union union gets truncated:

DECLARE @text as nvarchar(max);
DECLARE @text1 as nvarchar(1500);
DECLARE @text3 as nvarchar(1500);
DECLARE @text4 as nvarchar(1500);
DECLARE @where nvarchar(50);
SELECT @text1 = 'SELECT dbo.tbToezegging.tzAuditKey,...<snip>...WHERE (dbo.tbToezegging.tzIngelost_d IS NULL) AND (dbo.tbToezegging.tzDataset_i = 1)';
SELECT @text3 = 'SELECT dbo.tbToezegging.tzAuditKey,...<snip>...WHERE (dbo.tbToezegging.tzIngelost_d IS NULL) AND (dbo.tbToezegging.tzDataset_i = 3)';
SELECT @text4 = 'SELECT dbo.tbToezegging.tzAuditKey,...<snip>...WHERE (dbo.tbToezegging.tzIngelost_d IS NULL) AND (dbo.tbToezegging.tzDataset_i = 4)';
SELECT @text='??';
SELECT @where='';
IF @company <>0
  SELECT @where= ' AND dbo.tbAfdelingen.afBedrijf_i = ' + cast(@company AS nvarchar(2));
IF @department<>0
  SELECT @where= ' AND dbo.tbAfdelingen.afKeyfield = ' +  cast(@department AS nvarchar(2));
IF @dataset = 0 SELECT @text = @text1 + @where + ' UNION ALL ' + @text3 + @where + ' UNION ALL ' + @text4 + @where ;
IF @dataset = 1 SELECT @text = @text1 + @where;
IF @dataset = 3 SELECT @text = @text3 + @where;
IF @dataset = 4 SELECT @text = @text4 + @where;
SELECT @text = @text +  ' ORDER BY dbo.tbToezegging.tzBeloofdVoor_d';
print 'Where:'+ @where
print '============'
PRINT SUBSTRING(@text, 1, 3000);
print '============'
PRINT SUBSTRING(@text, 3001, 4000);
print '============'
print 'dataset:'+ cast(@dataset as varchar(2))
print 'length where:'+ cast(len(@where) as varchar(6))
print 'length text1:'+ cast(len(@text1) as varchar(6))
print 'length text3:'+ cast(len(@text3) as varchar(6))
print 'length text4:'+ cast(len(@text4) as varchar(6))
print 'length text:'+ cast(len(@text) as varchar(6))
exec sp_executesql @text

(other fields come from different tables for the different datasets, so this procedure is actually appropriate)

Sample output is

Where: AND dbo.tbAfdelingen.afBedrijf_i = 2
============
SELECT dbo.tbToezegging.tzAuditKey, dbo.tbToezegging.tzMedewerker_i, dbo.tbToezegging.tzBeloofdVoor_d, dbo.tbToezegging.tzToezegging, 
    dbo.tbToezegging.tzKeyfield, dbo.tbConstateringen.coConstatering, dbo.tbConstateringen.coTYPE_i, dbo.tbConstateringen.coAntwoord, 
    dbo.tbConstateringen.coNummer_i, dbo.tbAudits.aAuditNr_l, dbo.tbAudits.aTermijn_d, dbo.tbAudits.aSerie, dbo.tbAudits.aAuditor1_i, dbo.tbAudits.aKeyfield, 
    dbo.tbToezegging.tzDataset_i, dbo.tbToezegging.tzConsKey, tbMedewerkers_1.mNaam, dbo.tbToezegging.tzMailVerzonden_b, 
    dbo.tbMedewerkers.mNaam AS Ontvanger, dbo.tbAfdelingen.afKeyfield, dbo.tbAfdelingen.afBedrijf_i
    FROM dbo.tbMedewerkers LEFT OUTER JOIN dbo.tbAfdelingen 
    ON dbo.tbMedewerkers.mAfd_i = dbo.tbAfdelingen.afKeyfield RIGHT OUTER JOIN dbo.tbAudits 
    ON dbo.tbMedewerkers.mKeyfield = dbo.tbAudits.aOntvanger_i RIGHT OUTER JOIN dbo.tbToezegging LEFT OUTER JOIN dbo.tbMedewerkers AS tbMedewerkers_1 
    ON dbo.tbToezegging.tzMedewerker_i = tbMedewerkers_1.mKeyfield 
    ON dbo.tbAudits.aKeyfield = dbo.tbToezegging.tzAuditKey LEFT OUTER JOIN dbo.tbConstateringen 
    ON dbo.tbToezegging.tzConsKey = dbo.tbConstateringen.coKeyfield
    WHERE (dbo.tbToezegging.tzIngelost_d IS NULL) AND (dbo.tbToezegging.tzDataset_i = 1) AND dbo.tbAfdelingen.afBedrijf_i = 2 UNION ALL SELECT dbo.tbToezegging.tzAuditKey, dbo.tbToezegging.tzMedewerker_i, dbo.tbToezegging.tzBeloofdVoor_d, dbo.tbToezegging.tzToezegging, 
    dbo.tbToezegging.tzKeyfield, dbo.tbConstateringenF.coConstatering, dbo.tbConstateringenF.coTYPE_i, dbo.tbConstateringenF.coAntwoord, 
    dbo.tbConstateringenF.coNummer_i, dbo.tbAuditsF.aAuditNr_l, dbo.tbAuditsF.aTermijn_d, dbo.tbAuditsF.aSerie, dbo.tbAuditsF.aAuditor1_i, dbo.tbAuditsF.aKeyfield, 
    dbo.tbToezegging.tzDataset_i, dbo.tbToezegging.tzConsKey, tbMedewerkers_1.mNaam, dbo.tbToezegging.tzMailVerzonden_b, 
    dbo.tbMedewerkers.mNaam AS Ontvanger, dbo.tbAfdelingen.afKeyfield, dbo.tbAfdelingen.afBedrijf_i
    FROM dbo.tbMedewerkers LEFT OUTER JOIN dbo.tbAfdelingen 
    ON dbo.tbMedewerkers.mAfd_i = dbo.tbAfdelingen.afKeyfield RIGHT OUTER JOIN dbo.tbAuditsF 
    ON dbo.tbMedewerkers.mKeyfield = dbo.tbAuditsF.aOntvanger_i RIGHT OUTER JOIN dbo.tbToezegging LEFT OUTER JOIN dbo.tbMedewerkers AS tbMedewerkers_1 
    ON dbo.tbToezegging.tzMedewerker_i = tbMedewerkers_1.mKeyfield 
    ON dbo.tbAuditsF.aKeyfield = dbo.tbToezegging.tzAuditKey LEFT OUTER JOIN dbo.tbConstateringenF 
    ON dbo.tbToezegging.tzConsKey = dbo.tbConstateringenF.coKeyfield
    WHERE (dbo.tbToezegging.tzIngelost_d IS NULL) AND (dbo.tbToezegging.tzDataset_i = 3) AND dbo.tbAfdelingen.afBedrijf_i = 2 UNION ALL SELECT dbo.tbToezegging.tzAuditKey, dbo.tbToezegging.tzMedewerker_i, dbo.tbToezegging.tzBeloofdVoor_d, dbo.tbToezegging.tzToezegging, 
    dbo.tbToezegging.tzKeyfield, dbo.tbConstateringenV.coConstatering, dbo.tbConstateringenV.coTYPE_i, dbo.tbConstateringenV.coAntwoord, 
    dbo.tbConstateringenV.coNummer_i,
============
 dbo.tbAuditsV.aAuditNr_l, dbo.tbAuditsV.aTermijn_d, dbo.tbAuditsV.aSerie, dbo.tbAuditsV.aAuditor1_i, dbo.tbAuditsV.aKeyfield, 
    dbo.tbToezegging.tzDataset_i, dbo.tbToezegging.tzConsKey, tbMedewerkers_1.mNaam, dbo.tbToezegging.tzMailVerzonden_b, 
    dbo.tbMedewerkers.mNaam AS Ontvanger, dbo.tbAfdelingen.afKeyfield, dbo.tbAfdelingen.afBedrijf_i
    FROM dbo.tbMedewerkers LEFT OUTER JOIN dbo.tbAfdelingen 
    ON dbo.tbMedewerkers.mAfd_i = dbo.tbAfdelingen.afKeyfield RIGHT OUTER JOIN dbo.tbAuditsV 
    ON dbo.tbMedewerkers.mKeyfield = dbo.tbAuditsV.aOntvanger_i RIGHT OUTER JOIN dbo.tbToezegging LEFT OUTER JOIN dbo.tbMedewerkers AS tbMedewerkers_1 
    ON dbo.tbToezegging.tzMedewerker_i = tbMedewerkers_1.mKeyfield 
    ON dbo.tbAuditsV.aKeyfield = dbo.tbToezegging.tzAuditKey LEFT OUTER JOIN dbo.tbConstateringenV 
    ON dbo.tbToezegging.tzConsKey = dbo.tbConstateringenV.coKeyfield
    WHERE (dbo.tbToezegging.tzIngelost_d IS NULL) AND (dbo.tbToezegging.tzDataset_i = 4) AND dbo. ORDER BY dbo.tbToezegging.tzBeloofdVoor_d
============
dataset:0
length where:37
length text:4042
length text1:1289
length text3:1303
length text4:1303
Msg 156, Level 15, State 1, Line 34
Incorrect syntax near the keyword 'ORDER'.

@text gets truncated here:

WHERE (dbo.tbToezegging.tzIngelost_d IS NULL) AND (dbo.tbToezegging.tzDataset_i = 4) AND dbo.<<<===

 

[fredericofonseca]

define all result fields as nvarchar(max)

And if none of the values you are using is indeed a N type, don't use nvarchar, just use varchar(max) as it takes half the required bytes

As this type of dynamic sql's can also become quite big I would also advise you to use alias for the tables and use those when referencing the columns instead of using the full qualification you have - again this will save lots of chars

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[fredericofonseca]

to be more specific
In order for the result of a concatenation operation not to be truncated, at least one of the arguments of the operation must be of large type (such as varchar(max))
Note that the variable to which the result of the concatenation is assigned to is not considered.

following example exemplifies this

declare @t1 nvarchar(max);
declare @t2 nvarchar(max);
declare @t3 nvarchar(3000);
declare @t4 nvarchar(3000);
declare @t5 nvarchar(3000);

set @t2 = replicate('A',3000)
set @t3 = replicate('A',3000)
set @t4 = replicate('A',3000)
set @t5 = replicate('A',3000)
set @t1 = @t2+@t3+@t4
select len(@t1) -- gives 9000
set @t1 = @t3+@t4+@t5
select len(@t1) -- gives 4000

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[PeDa]

Thank you Frederico,
[tt]
define all result fields as nvarchar(max)
[/tt]
has indeeed solved this problem.

Peter D.