I created a Stored Procedure. It returns one JSON field with data over 500k characters. 1,054,073 to be exact. Works fine and does not truncate when I use EXEC. I have executed it over a thousand time and works every time.
When I setup a Job for this SP, it truncates the JSON to 512 characters.
At first, I thought that it is because the JSON method that I was calling which to some part that maybe the reason for the 512 characters. But when I zeroed in, I decided to return REPLICATE('TEST', 99999), it stills truncate to 8K.
Has anyone experienced this with a JOB? Again, SP ran fine.
Harish, it can't be the mapping because when running the SP without the Job, it is working as expected. But I will go back and check again. Thanks
[quote="ScottPletcher, post:3, topic:17661"]
REPLICATE(CAST('TEST' AS varchar(max)), 99999)
[/quote] Scott, thanks. That did it for the variable test. Do you have any explanation why this is happening with calling the SP through a Job? Do I need to turn on a feature of some sort?
I am doing a call to a WebAPI. Usually it returns a full JSON but it is truncating the JSON causing an error. Do you have a solution for me on the following when calling through a JOB?
DROP TABLE IF EXISTS #JSONTable
CREATE TABLE #JSONTable (JSONData NVARCHAR(MAX))
--Place value into a table because returned data can be huge and variable will not be able to handle it.
INSERT #JSONTable (JSONData)
EXEC @RC = sp_OAMethod @API, 'responseText'
Could you have the job run the proc and see if that works? You could always add an optional parameter to the proc that "told" the proc it was being run thru a job if it needs to change how it executes for a job.
What happens if you replace NVARCHAR(MAX) with json data type? Would also really help what is your end goal here. Sometimes knowing the bigger picture would help us give you maybe a better design approach also. Not just DML. Why are you for example using SQL sp_OAMethod instead of other means of getting the data such as powershell or some other approach? Just to understand your design/architecture approach
I am not sure how to replace the NVARCHAR(MAX) with JSON data type. If you saying to literally replace it with the word "JSON", it compiles fine, but error when the SP is being called/ran.
It is not thatI am lazy. I don't usually describe the big picture because just exactly where do I start and when there is an orgy of (too much) information, i can lose the audience really quick.
However, I am trying to use the vendor's huge set of APIs to get data. That is the ultimate goal. Their APIs work fine when run in Postman. I can use C# but I choose not to because I wanted to take advantage of OPENJSON and all of SQL Server's built in support for JSON. The problem is not the JSON but the calling of the API using the sp_OAMethod. It has been working fantastic but where it coughs up, I think it is the service account that is running the job. As I have shared before, the SP executes perfectly by itself. As a matter of fact, I connected the SP to a SSRS page and put that on a schedule and that has been running perfectly fine too. It is just the SQL Job is causing the truncate of the JSON.
That brings me to the questions of you suggesting PowerShell and your above JSON datatype.
How would I replace and use the JSON datatype?
How would I use PowerSHell to call an API and then return the JSON to a SP?
