Click here to Skip to main content
15,943,294 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 24-Jul-23 0:54am
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
Declare @Object as Int;
Declare @ResponseText as Varchar(800);

Exec sp_OACreate 'MSXML2.ServerXMLHTTP', @Object OUT;
Exec sp_OAMethod @Object, 'open', NULL, 'get',
Exec sp_OAMethod @Object, 'send'
Exec sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT

Select @ResponseText
Exec sp_OADestroy @Object
Share this answer
Richard Deeming 24-Jul-23 7:49am    
As the OP already said ten months ago.

Stick to answering new questions unless you have something new and interesting to add to the discussions. Resurrecting an old question to repeat the solution that has already been posted will end up with you being kicked off the site.

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