Dynamic API Query Consumption from a SQL Server Agent Job

Question

Question: I'm working with a stored procedure that consumes a dynamic API query using sp_OA* procedures and inserts data into a table. When I execute this stored procedure manually, it works perfectly and inserts data as expected. However, when I run the same stored procedure from a SQL Server Agent Job, the job executes successfully without any errors, but no data is inserted into the target table.

I've checked permissions and configurations like enabling Ole Automation Procedures, and the API endpoint is accessible. The stored procedure involves calling an external API, parsing JSON, and inserting data. The only difference observed so far is the execution context between manual execution and job execution.

What could be causing this discrepancy, and how can I troubleshoot or resolve it to ensure the SQL Server Agent Job inserts data correctly when consuming the API?

I have already tried:

• Verifying that Ole Automation Procedures are enabled.
• Checking that the account running the SQL Server Agent Job has proper permissions to execute sp_OA* procedures.
• Ensuring network access from the Agent's context to the API endpoint.

Any insights, similar experiences, or solutions would be greatly appreciated.

What I tried:

Manual execution of the stored procedure:

• I ran the stored procedure manually in SQL Server Management Studio (SSMS) under my user account.
• I verified that it successfully consumed the API, parsed the JSON response, and inserted data into the target table.

SQL Server Agent Job execution:

• I created a SQL Server Agent Job to execute the same stored procedure.
• Ensured that Ole Automation Procedures are enabled on the server.
• Verified that the API endpoint is accessible from the server and that the network allows connections from the Agent's context.
• Checked that the account under which the Agent Job runs has permissions for using sp_OACreate, sp_OAMethod and other related procedures.
• Monitored the job execution for errors and confirmed that the job completes without reporting any errors.

Environment and Permissions Checks:

• Confirmed that the SQL Server Agent service account or proxy account has sufficient privileges and network access.
• Verified the configuration settings such as enabling Ole Automation Procedures.

What I expect:

I expected that running the stored procedure within a SQL Server Agent Job would produce the same outcome as running it manually.

Specifically, the job should:

• Successfully call the external API.
• Parse the returned JSON data.
• Insert the processed data into the designated table.

The goal was to have the Agent Job replicate the manual execution behavior, resulting in new rows being added to the table with the data fetched from the API. However, despite the job completing without errors, no data was being inserted when run through the job, which is contrary to my expectations.