Click here to Skip to main content
15,606,999 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
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'

DECLARE @responseTextTable table(responseText varchar(max));

Declare @Object as Int;
--Declare @ResponseText as Varchar(max);
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

SQL Agent Job Step
SET TEXTSIZE 2147483647
	Exec Get_Raw_Data
	WAITFOR DELAY '00:00:45';

Please help me,why is this happening?

What I have tried:

changed varchar(max) to varchar(800).
Updated 15-Sep-22 7:42am
CHill60 15-Sep-22 10:58am    
"when I call the same api with the c# windows service, I do not encounter such a problem" - are you calling it for 3 hours constantly from the C# service? Perhaps you are getting kicked back for hogging the API?

I hope I found the reason. I added "exec sp_OADestroy @Object" after the last step. It's been running for 20 hours, no problem.
Share this answer
I followed the running of the Windows service for 2 days. I see it's okay, I'm constantly getting the data.
Share this answer

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900