How to use sp_execute_external_script to fetch USD Rate from an API and update a table?
0
votes
1
answer
223
views
I have this code that gives this error
-- Set the API URL
SET @url = 'https://www.banxico.org.mx/SieAPIRest/service/v1/series/SF43718/dato/oportuno?token=734b37b3a5099a9d2d39d06478d47e359a9568cd6693116d95b710e6b8be0008 ';
-- Use built-in SQL Server functions to make HTTP request and obtain response
-- Requires SQL Server 2016 or later
DECLARE @json NVARCHAR(MAX);
-- Use sys.dm_exec_query_stats to enable SQL Server to execute external scripts
-- Indb is the database name
USE lndb;
EXEC sp_execute_external_script
@language = N'Python',
@script = N'
import requests
url = "https://www.banxico.org.mx/SieAPIRest/service/v1/series/SF43718/datos/oportuno?token=734b37b3a5099a9d2d39d06478d47e359a9568cd6693116d95b710e6b8be0008 "
response = requests.get(url)
json_response = response.text
json_response',
@output_data_1_name = N'json',
@output_data_1 = @json OUTPUT;
-- Parse the JSON response using OPENJSON
DECLARE @usdRate DECIMAL(18, 6);
SELECT @usdRate = value
FROM OPENJSON(@json, '$.bmx.series.datos.dato')
WITH (value DECIMAL(18, 6) '$');
-- Display the result
SELECT @usdRate AS USDExchangeRate;
> Msg 297, Level 16, State 101, Procedure sp_execute_external_script, Line 1 [Batch Start Line 0]
The user does not have permission to perform this action.
Admin already gave me permission
Also what is sys.dm_exec_query_stats

Asked by Luis Avalos
(25 rep)
Dec 13, 2023, 03:52 PM
Last activity: Dec 14, 2023, 02:30 PM
Last activity: Dec 14, 2023, 02:30 PM