Web API Procedure will not run in SQL Server Agent Job

  • 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

     

  • 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.

  • 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

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

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