0

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[0].datos[0].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

Admin already gave me permission

Also what is sys.dm_exec_query_stats

1
  • Shouldn't it be @output_data_1_name = N'json_response', Commented Dec 14, 2023 at 15:36

1 Answer 1

0

you should ask for this right:

USE <database_name>
GO
GRANT EXECUTE ANY EXTERNAL SCRIPT TO [UserName]

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.