Web API Procedure will not run in SQL Server Agent Job

Question

Web API Procedure will not run in SQL Server Agent Job

SSC-Addicted

Points: 485

July 3, 2024 at 7:32 pm

I have a stored procedure that pulls JSON data from a US government web API. The procedure executes fine manually but fails with the error below when executed from a job. I've checked permissions of the SQL Agent and ensured it has permission to execute sp_OACreate and sp_OAMethod procdures.

Extremely grateful for any help or possible solutions.

"JSON text is not properly formatted. Unexpected character '"' is found at position 505. [SQLSTATE 42000] (Error 13609). The step failed"

CREATE PROCEDURE [dbo].[usp_exec_get_API_Updated_Documents] 
   @Begin_Date DATE =  NULL
  ,@End_Date DATE  = NULL
  ,@Run_Type VARCHAR(1) = 'M'  -- M = Manual, A = Auto/from scheduled job.

AS

BEGIN 
   SET NOCOUNT ON;
   SELECT 
    @Begin_Date = ISNULL(@Begin_Date, '1/1/'+ CONVERT(VARCHAR(4), YEAR(CURRENT_TIMESTAMP)))
    ,@End_Date  = ISNULL(@End_Date, CURRENT_TIMESTAMP)

    DROP TABLE IF EXISTS #JsonTable
    CREATE TABLE #JsonTable(Json_Table NVARCHAR(MAX))

     DECLARE @Object INT
     DECLARE @hr INT 
     DECLARE @json TABLE(Json_Table NVARCHAR(MAX))
     DECLARE @BDate VARCHAR(10) = CONVERT(VARCHAR(10), @Begin_Date, 121)
     DECLARE @EDate VARCHAR(10) = CONVERT(VARCHAR(10), @End_Date, 121)
     DECLARE @URL NVARCHAR(2000)

     SET @URL = 'https://api.regulations.gov/v4/documents? 
filter[agencyId]=ONCS&filter[agencyId]=HHS&filter[agencyId]=CMS&filter[agencyId]=OIG&filter[postedDate][ge]=' + @BDate + '&filter[postedDate][le]=' + @EDate + '&page=250&api_key=TestKey'

    EXEC @hr = sp_OACreate 'MSXML2.ServerXMLHTTP.6.0', @Object OUT;
    EXEC @hr = sp_OAMethod @Object, 'open', NULL, 'get', @URL, 'false'
    EXEC @hr = sp_OAMethod @Object, 'send'
    EXEC @hr = sp_OAMethod @Object, 'responseText', @json

     INSERT INTO @json(Json_Table) EXEC sp_OAGetProperty @Object, 'responseText'
-- select the JSON string
     INSERT INTO #JsonTable(Json_Table) SELECT * FROM @json
     EXEC sp_OADestroy @Object

     DROP TABLE IF EXISTS #TempAPIOut
SELECT * 
INTO #TempAPIOut
FROM OPENJSON((SELECT * FROM #JsonTable), N'$.data')
WITH (   
  [id] VARCHAR(200) N'$.id'   ,
  [type]  VARCHAR(50) N'$.type',
  [title] VARCHAR(500) N'$.attributes.title',
  [documentType]   VARCHAR(150) N'$.attributes.documentType',
  [lastModifiedDate] DATETIME N'$.attributes.lastModifiedDate',
  [DocumentNumber]   VARCHAR(50) N'$.attributes.frDocNum',
  [AgencyId]  VARCHAR(20) N'$.attributes.agencyId',
  [postedDate] DATETIME N'$.attributes.postedDate',
  [docketId] VARCHAR(50) N'$.attributes.docketId'
)

DROP TABLE IF EXISTS #TempOUT2
SELECT 
         DocumentNumber
 ,Row_Rank = ROW_NUMBER() OVER(PARTITION BY DocumentNumber ORDER BY lastModifiedDate DESC)
         ,lastModifiedDate 
INTO #TempOUT2
FROM #TempAPIOut WHERE DocumentNumber IS NOT NULL

TRUNCATE TABLE Compliance.tbl_API_Regulations
 
 INSERT INTO Compliance.tbl_API_Regulations
 (
 id,
 type,
 document_type,
 title,
 Last_Modified_Date,
 Document_Number,
 Agency_ID,
 Posted_Date,
 Docket_ID,
 FR_Docket_ID,
 Register_Item_ID
 )
 
SELECT DISTINCT 
ap.id
,ap.Type
,ap.documentType
,ap.title
,ap.lastModifiedDate
,ap.DocumentNumber
,ap.AgencyId
,ap.postedDate
,ap.docketId
,fr.Received_Under_Docket
,fr.Register_Item_ID
FROM #TempAPIOut ap
JOIN #TempOUT2 o2 ON o2.DocumentNumber = ap.DocumentNumber
   AND o2.Row_Rank = 1
   AND o2.lastModifiedDate = ap.lastModifiedDate 
LEFT JOIN Compliance.tbl_Federal_Register fr ON fr.Document_Number = ap.DocumentNumber
WHERE ap.DocumentNumber IS NOT NULL
END

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Mr. Brian Gale SSC-Insane Points: 24800 More actions · Answer 1

one thing you could try is to change @json table variable to a permanent table. That way you can read the response from the WEB API call.

Now, that being said, I am extremely confused by this section of your code:

     INSERT INTO @json(Json_Table) EXEC sp_OAGetProperty @Object, 'responseText'
-- select the JSON string
     INSERT INTO #JsonTable(Json_Table) SELECT * FROM @json

Why not just insert directly to #JsonTable? Is there a reason why you use a table variable AND a temp table? Feels like a waste of resources to me, but maybe there is a good reason for it. Also, what is the point of the parameter "@Run_Type"? You have it as a parameter but don't actually use it anywhere? but I think these questions are not needed as I am just asking stuff about syntax... if it works when run as you with the exact same parameters, then it is likely a permission issue. If it is running with different parameters when run manually and via SQL Job, then I'd try changing it to use the same parameters.

But in the end, I would check what the API response is so you can validate that the response is valid JSON or not because it is the next section that is trying to convert the response to JSON and failing.

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

fxferguson SSC-Addicted Points: 485 More actions · Answer 2

Thank you for your response, Brian,

I was able to determine the cause of the problem. It was due to permissions. The Windows account the job was running under did not have explicit permissions to execute "Ole Automation" stored procedures. I also had to include: "SET TEXTSIZE 2147483647" in the procedure, referenced in this article: https://dba.stackexchange.com/questions/127451/stored-procedure-working-when-run-manually-not-running-in-sql-agent-job.

Again,

Thank you for responding