sql-server - Maps API 正在运行，但突然停止。计划为 ERP 创建 SP

Question

Maps API 正在运行，但突然停止。得到？？？？？？用于地址和 NULL 用于其他所有内容。这在过去是有效的。使用这个：

DECLARE @Address as varchar(100),
    @City as varchar(25),
    @State as varchar(2),
    @PostalCode as varchar(10),
    @Country varchar(40),
    @County varchar(40),
    @GPSLatitude numeric(18,9),
    @GPSLongitude numeric(18,9),
    @MapURL varchar(1024)
    





    SET @Address = '333 W 35th st'
    SET @City = 'Chicago'
    SET @State = 'IL'

--构建Web API URL

DECLARE @URL varchar(MAX)
SET @URL =  'https://www.google.com/maps/api/geocode/xml?sensor=false&address='+
    CASE WHEN @Address IS NOT NULL THEN @Address ELSE '' END +
    CASE WHEN @City IS NOT NULL THEN ', ' + @City ELSE '' END +
    CASE WHEN @State IS NOT NULL THEN ', '+ @State ELSE '' END +
    CASE WHEN @PostalCode IS NOT NULL THEN ', ' + @PostalCode ELSE '' END +
    CASE WHEN @Country IS NOT NULL THEN ', ' + @Country ELSE '' END+ 
    '&key=I_HAVE_A_VALID_API' 
SET @URL = REPLACE(@Url, ' ', '+')



---CREATE THE OATH REQUEST

DECLARE @Response varchar(8000)
DECLARE @XML xml
DECLARE @Obj int
DECLARE @Result int
DECLARE @HTTPStatus int
DECLARE @ErrorMsg varchar(MAX)

Exec @Result = sp_OACreate 'MSXML2.ServerXMLHttp',@Obj OUT

BEGIN TRY
    EXEC @Result  = sp_OAMethod  @Obj, 'open', NULL, 'GET', @URL, false
    EXEC @Result  = sp_OAMethod  @Obj, 'setRequestHeader', NULL, 'Content-Type', 'application/x-www-form-urlencoded'
    EXEC @Result  = sp_OAMethod  @Obj, send, NULL, ''
    EXEC @Result  = sp_OAGetProperty @Obj, 'status', @HTTPStatus OUT
    EXEC @Result  = sp_OAGetProperty @Obj, 'responseXML.xml', @Response OUT
END TRY
BEGIN CATCH
    SET @ErrorMsg = ERROR_MESSAGE()
END CATCH

EXEC @Result = sp_OADestroy @Obj

IF (@ErrorMsg IS NOT NULL) OR (@HTTPStatus <> 200)
BEGIN
    SET @ErrorMsg = 'Error in spGeocode: ' + ISNULL(@ErrorMsg, 'Http result is: ' + CAST(@HTTPStatus AS varchar (10)))
    RAISERROR (@ErrorMsg, 16,1, @HTTPStatus)
    RETURN
END

 --CAPTURE THE RESPONSE
 BEGIN
 SET @XML = CAST(@Response AS XML)

 SET @GPSLatitude =@XML.value('(/GeocodeResponse/result/geometry/location/lat) [1]', 'numeric(18,9)')
 SET @GPSLongitude = @XML.value('(/GeocodeResponse/result/geometry/location/lng) [1]', 'numeric(18,9)')
 SET @City = @XML.value('(/GeocodeResponse/result/address_component[type="locality"]/long_name) [1]','varchar(40)')
 SET @State = @XML.value('(/GeocodeResponse/result/address_component[type="administrative_area_level_1"]/short_name) [1]','varchar(40)')
 SET @PostalCode = @XML.value('(/GeocodeResponse/result/address_component[type="postal_code"]/long_name) [1]','varchar(20)')
 SET @Country = @XML.value('(/GeocodeResponse/result/address_component[type="country"]/short_name) [1]','varchar(40)')
 SET @County = @XML.value('(/GeocodeResponse/result/address_component[type="administrative_area_level_2"]/short_name) [1]','varchar(40)')

 SET @Address = 
 ISNULL(@XML.value('(/GeocodeResponse/result/address_component[type="street_number"]/long_name) [1]','varchar(40)'), '???') + ' '+
 ISNULL (@XML.value('(/GeocodeResponse/result/address_component[type="route"]/long_name) [1]','varchar(40)'),  '???')

 SET @MapURL = 'https://maps.google.com/maps?f=q&hl=en&q=' + CAST (@GPSLatitude AS varchar(20)) + '+' + CAST(@GPSLongitude AS varchar(20))

 END

 BEGIN
 SELECT @Address as Address,
 @City as City,
 @State as State,
 @PostalCode as Zip,
 @Country as Country,
 @County as County,
 @GPSLatitude as Lat,
 @GPSLongitude as lon,
 @MapURL as map

 END

输出是地址、纬度和经度以及 MapURL。最终会发展成ERP中的存储过程。这在几周前有效，然后出于安全原因不得不将其关闭。

标签： sql-server