Using MSXML2.ServerXMLHTTP To Grab Source of HTML Page and save to table

• smhunt

  Mr or Mrs. 500

  Points: 577

  More actions

  September 14, 2004 at 10:02 am

  #102202

  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