SQL Job (Execute Truncates)

Transact-SQL
#1

This runs fine in a query window. But when placed inside a job, it truncates the variable @JSONResponseText.

EXECUTE sp_ExecuteSQL
	  @SQL
	, @Parameter_Definition
		, @pi_Environment = @Environment
		, @pi_Verb = @Verb
		, @pi_URL = @URL
	, @po_JSONResponseText = @JSONResponseText OUTPUT
	
--SELECT @JSONResponseText

I have to add this before the execute in the job to make it work.

SET TEXTSIZE -1

Why do I have to add the above in the job but not the query window?

Also, when running a job, who should I run the job as? Is there a generic role that I should use?

Thanks

#2
  1. how are you seeing the value of @JSONResponseText when this is run in a SQL Job?
  2. When running as a job, it depends on what other perms that user needs for the process to succeed. Does it write to db, needs access to a file folder, needs permissions to hit the api etc.
#3

I execute a SP and it returns in the variable @JSONResponseText. Then I use OPENJSON against that variable. I did a research on this and found the answer above only because there are others who had the same issue.

#4

I am talking about when executing it in the sql job not when you run the proc manually.

how are you seeing the value of @JSONResponseText when this is run in a SQL Job?

#5

In regards to your last comment, I inserted the @JSONResponseText into a log table to view it and the response is getting truncated.

#6

What data type column are you dumping it into