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?

• Gareth Lyons: "@command" is nvarchar(max). Although if you need to go higher I'd be looking at moving the code into a stored proc/file, for readability if nothing else!
• SqlWorldWide: @GarethLyons Yes I see that, Not sure what I was thinking. Edited my question. thx.
• Gareth Lyons: np, just tried it on SQL2014 / SSMS 2014, happily accepts & displays commands over 4000 characters. What versions are you on? Thanks
• SqlWorldWide: sql2016/ssms2016 are you passing it as a variable? DECLARE @command1 NVARCHAR(MAX) then ` @command=@command1` .
• Gareth Lyons: Yep, and just tried successfully on 16/16 too. Maybe a rogue apostrophe somewhere?

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.

• SqlWorldWide: Thank you. I agree with your comment about implicit conversion. My problem was part of my string was NVARCHAR and part was VARCHAR . I started with N then I had many variables in the string where I did not put N'. Once I added N in front of every string it did work for me. After @tblUpdateStats_List I have many more strings separated by variables.
• Robert Carnegie: I may have been making this mistake of mixing types in a string for EXEC() or for sp_executesql . I hadn't identified some combinations as safe and some not, but my workaround for it has been to use just one long string which contains token marker terms like @{explicit_integer} and '@{explicit_string}', into @template nvarchar(max), then, REPLACE() each of the tokens with the required value, instead of inserting by adding strings together.

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']..................