Post reply

Problems calling XMLHttp within Stored Procedure using Extded stored procedure

  • September 14, 2004 at 10:05 am

    #60193

    I'm trying to use 'MSXML2.ServerXMLHTTP' though extended store procedures to retrieve the html source of parsed asp pages to be stored into a database for later use in a mail que system

    When I execute the following code in Query Analyzer the  @vResponseText variable is returned NULL

    DECLARE

     @vPointer INT,

     @vResponseText VARCHAR(8000),

     @vStatus INT,

     @vStatusText VARCHAR(200)

    EXEC sp_OACreate 'MSXML2.ServerXMLHTTP', @vPointer OUTPUT

    EXEC sp_OAMethod @vPointer, 'open', NULL, 'GET', 'http://tlcpet.com/default.asp'

    EXEC sp_OAMethod @vPointer, 'send'

    EXEC sp_OAMethod @vPointer, 'responseText', @vResponseText OUTPUT

    EXEC sp_OAMethod @vPointer, 'Status', @vStatus OUTPUT

    EXEC sp_OAMethod @vPointer, 'StatusText', @vStatusText OUTPUT

    EXEC sp_OADestroy @vPointer

    Select @vStatus, @vStatusText, @vResponseText

    However, when i execute the following code (omiiting the output variable and selecting 'responseText' directly) I get the source of the page as intended (Not NULL)

    DECLARE

     @vPointer INT,

     @vResponseText VARCHAR(8000),

     @vStatus INT,

     @vStatusText VARCHAR(200)

    EXEC sp_OACreate 'MSXML2.ServerXMLHTTP', @vPointer OUTPUT

    EXEC sp_OAMethod @vPointer, 'open', NULL, 'GET', 'http://tlcpet.com/default.asp'

    EXEC sp_OAMethod @vPointer, 'send'

    EXEC sp_OAMethod @vPointer, 'responseText' --, @vResponseText OUTPUT

    EXEC sp_OAMethod @vPointer, 'Status', @vStatus OUTPUT

    EXEC sp_OAMethod @vPointer, 'StatusText', @vStatusText OUTPUT

    EXEC sp_OADestroy @vPointer

    Select @vStatus, @vStatusText, @vResponseText

    I need to be able to 'responseText'  into the OUTPUT variable @vResponseText so that I can then insert it into a table

    Can someone see where I'm going wrong? XMLHttp IS properly installed and functioning (the latter example and many other applications on this server use the object succesfully)

     

    Thank in advance, I'm stumped on this

  • September 14, 2004 at 12:54 pm

    #523048

    Short answer: You're overrunning @vResponseText.

    Long answer: If you add in

    EXEC sp_OAGetErrorInfo @vPointer

    after the responseText line, you'll see that srv_convert (which is probably called in sp_OAMethod) is throwing an error. Well, SQL Server is being really nice and trapping that for you... Anyhow, check here for more info and a possible solution: http://www.experts-exchange.com/Web/Web_Languages/XML/Q_20802912.html

    HTH,



    -Brandon

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply