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
SQL Agent Job Step
SET
TEXTSIZE
2147483647
WHILE
1=1
BEGIN
Exec
Get_Raw_Data
WAITFOR
DELAY
'
00:00:45'
;
Please help me,why is this happening?
Thanks,
What I have tried:
changed varchar(max) to varchar(800).
Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad
spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or
edit the question
and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it.
Provide an answer or move on to the next question.
Let's work to help developers, not make them feel stupid.
