sp_add_jobstep add more than 4000 Character in @command parameter

Question

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?

Answer 1

'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)
             +<literal>
             +'QQQ';

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

With <literal> 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.

Answer 2

I had to add N before every termination of string. What I mean by that?

Before I did not had N after @dbName and @tblUpdateStats_List variables.

 DECLARE @command2 NVARCHAR(MAX)   
 SET @command2 =N'SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED  
                USE [' + @dbName +']
                INSERT INTO [DBA].[dbo].['+@tblUpdateStats_List+'] ....................

Added N after @dbName and @tblUpdateStats_List and now I can pass more than 4000 character to @command parameter.

DECLARE @command2 NVARCHAR(MAX)   
     SET @command2 =N'SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED  
                    USE [' + @dbName +N']
                    INSERT INTO [DBA].[dbo].['+@tblUpdateStats_List+N']..................