sp_add_jobstep add more than 4000 Character in @command parameter sp_add_jobstep is well documented here.  I understand parameter @command is type NVARCHAR(MAX).

I am creating a SQL Agent Job using a Stored Procedure.  Passing the value for @command parameter via a variable.  I am getting truncated at 4000 character.

My variable is also declared as NVARCHAR(MAX)

Any workaround to add more than 4000 characters in @command parameter?
'This' is a varchar string.

N'This' is an nvarchar string.

I'll guess that, after @tblUpdateStats_List, the remainder of your string was more than 4000 characters.

I did some experiments (in SQL 2016), and it looks like when a varchar string is implicitly converted to nvarchar, it is converted to nvarchar(4000), not nvarchar(MAX).

I ran the following query:

DECLARE @myNVar NVARCHAR(MAX);
SET @myNVar = 'ABCDEFG';

SET @myNVar = 'QQQ' + REPLICATE(@myNVar, 1000)
             +&lt;literal&gt;
             +'QQQ';

SELECT LEN(@myNVar), SUBSTRING(@myNVar, 6990, 25), RIGHT(@myNVar, 20);


With &lt;literal&gt; initially being an nvarchar string constructed as follows:


The character 'Z', repeated 3 times
the character 'z', repeated 1027 times
the character 'X', repeated 3 times
EOL
The resulting string copied, and pasted in 3 more times (wit the fourth EOL removed)


This string was 4,138 characters long (on Windows, where EOL is CR+LF), so the total string length should have been 3 + 7000 + 4138 + 3, or 11,144 characters

When run with the literal as an nvarchar string, got the following results:

11144     ABCDEFGABCDEFGZZZzzzzzzzz     zzzzzzzzzzzzzzXXXQQQ


So, correct length, and expected values at end of replicated string, at start of literal, and at end of literal.

Then, I changed the literal to a varchar string, and got this:

11006     ABCDEFGABCDEFGZZZzzzzzzzz     zzzzzzzzzzzzzzzzzQQQ


So, length shows 3 + 7000 + 4000 + 3 = 11006 - what we'd expect if the varchar value was truncated down to just 4,000 characters. And, we don't see XXX before the final QQQ, also as we'd expect under these circumstances.

FYI - If I CAST the varchar literal to nvarchar(MAX), my results went back to the first ones, so an explicit cast avoids this problem.