I created a stored procedure in sql server to get json data with API.
This sp runs successfully manually at once. But I created sql agent job which constantly calls this sp. This job always works, it works successfully for about 3 hours, but then it returns null from the api. Why could such a thing happen? There is no problem with the api because when I call the same api with the c# windows service, I do not encounter such a problem.
Store Procedure(Get_Raw_Data)
DECLARE @ResponseText as nvarchar(max),@Token VARCHAR(100),@URL VARCHAR(100)
SET @Token='mytoken'
SET @URL ='myURL'
DECLARE @responseTextTable table(responseText varchar(max));
Declare @Object as Int;
Declare @Body as varchar(max);
Exec sp_OACreate 'MSXML2.ServerXMLHTTP', @Object OUT;
EXEC sp_OAMethod @Object, 'open', NULL, 'get',@URL, 'false'
Exec sp_OAMethod @Object, 'setRequestHeader', null, 'Content-Type', 'application/json'
Exec sp_OAMethod @Object, 'setRequestHeader', null, 'Authorization', @Token
Exec sp_OAMethod @Object, 'send', null, @body
insert into @responseTextTable (responseText)
Exec sp_OAMethod @Object, 'responseText'
select @ResponseText = responseText from @responseTextTable
IF ISJSON(@ResponseText)=1 BEGIN
SELECT device_id, company_id, master_name, last_at,status,date,timestamp,tag.[key],tag.[value],tag.[type],JSON_VALUE(tag.[value],'$.name') tagName,JSON_VALUE(tag.[value],'$.value') AS tagValue
FROM OPENJSON(@ResponseText)
WITH (
device_id NVARCHAR(max) '$.device_id',
company_id NVARCHAR(max),
master_name NVARCHAR(50)'$.name',
last_at DATETIME2 '$.last_at',
status NVARCHAR(max),
date DATETIME2 '$.date',
timestamp NVARCHAR(max),
tags NVARCHAR(MAX) '$.tags' AS JSON
)
OUTER APPLY OPENJSON(tags) tag
END
SQL Agent Job Step
SET TEXTSIZE 2147483647
WHILE 1=1 BEGIN
Exec Get_Raw_Data
WAITFOR DELAY '00:00:45';
END
Please help me,why is this happening?
Thanks,
What I have tried:
changed varchar(max) to varchar(800).