none
XML query in a stored procedure gives me error

    Soru

  • 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
    09 Ocak 2012 Pazartesi 23:01

Tüm Yanıtlar

  • Could you please share your XML content?
    10 Ocak 2012 Salı 05:44
  • 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
    10 Ocak 2012 Salı 14:10