2

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?

  • "@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! – Gareth Lyons May 31 '17 at 18:05
  • @GarethLyons Yes I see that, Not sure what I was thinking. Edited my question. thx. – SqlWorldWide May 31 '17 at 18:09
  • np, just tried it on SQL2014 / SSMS 2014, happily accepts & displays commands over 4000 characters. What versions are you on? Thanks – Gareth Lyons May 31 '17 at 18:16
  • sql2016/ssms2016 are you passing it as a variable? DECLARE @command1 NVARCHAR(MAX) then ` @command=@command1` . – SqlWorldWide May 31 '17 at 18:18
  • Yep, and just tried successfully on 16/16 too. Maybe a rogue apostrophe somewhere? – Gareth Lyons May 31 '17 at 18:27
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.

  • 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. – SqlWorldWide May 31 '17 at 23:37
  • 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. – Robert Carnegie Jun 1 '17 at 23:20
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.