Sample Header Ad - 728x90

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