XML query in a stored procedure gives me error

Question

I have this stored procedure:

USE [RGCOMMON]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[LatLng2Addr]
    @Lat Numeric(12,6),
    @Lng Numeric(12,6)
AS
BEGIN
 SET NOCOUNT ON;
  
    IF OBJECT_ID('#XML') IS NOT NULL DROP TABLE #XML
    CREATE TABLE #XML ( yourXML XML )

    DECLARE @URL VARCHAR(8000), @XML xml, @Obj int, @Result int, @HTTPStatus int
    SELECT @URL = 'http://maps.google.com/maps/api/geocode/xml?sensor=false&latlng='+Convert(Varchar, @Lat)+','+Convert(Varchar, @Lng)

    EXEC @Result = sp_OACreate 'MSXML2.XMLHttp', @Obj OUT
    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

    INSERT #XML (yourXML) EXEC @Result = sp_OAGetProperty @Obj, 'responseXML.xml'
    SELECT @XML=yourxml FROM #XML
   
    DELETE RGCOMMON..OutgoingMails2
    INSERT INTO RGCOMMON..OutgoingMails2 SELECT @XML.value('(/GeocodeResponse/result/formatted_address)[1]','varchar(50)')
END

 

If i invoke in a query window in SQL management studio in this way, works perfectly:

declare @lat numeric(12,6), @lng Numeric(12,6)

SELECT @lat=10.060850
SELECT @Lng=-68.110379

EXEC RGCOMMON..LatLng2Addr @lat, @lng

but if i add exactly the same code in a job in SQL Agent, gives me this error: Ejecutado como usuario: MULTISERVER\Administrador. Análisis de XML: línea 19, carácter 23; fin de entrada inesperado [SQLSTATE 42000] (Error 9400).  No se pudo realizar el paso. What im doing wrong?

Thanks.

---

Bernardo Salazar

Answer 1

Could you please share your XML content?

Answer 2

Hi Tsathiyan...

As you can appreciate in the body of the stored procedure that i posted, the XML data is obtained dinamically from a web service (google geocoding web service), i take the XML data returned by the service and save to temp table (#XML), and later i select one value (@XML.value('(/GeocodeResponse/result/formatted_address)[1]','varchar(50)'))

I dont know why works perfectly in a query window, but if i invoke in the same way by a job, fails.

---

Bernardo Salazar