Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2006
    Posts
    66

    Unanswered: sp not returning output

    i have the following stored procedure which does a hit to a server and returns the response

    it is hitting the server but the problem is the response is null


    any ideas what i'm doing wrong

    Code:
    /****** Object:  Stored Procedure dbo.http_geturl    Script Date: 2/12/2007 2:00:40 PM ******/
    ALTER procedure [dbo].[http_geturl]( @sUrl varchar(8000), @response varchar(8000) out) 
    As
    Declare   @obj   int  ,@hr   int  ,@status int  ,@msg varchar(255) 
    
       exec @hr = sp_OACreate 'MSXML2.ServerXMLHttp', @obj OUT 
       if @hr < 0
       begin
       /* Raiserror('sp_OACreate MSXML2.ServerXMLHttp failed', 16, 1) */
        return 
       end 
       
       exec @hr = sp_OAMethod @obj, 'Open', NULL, 'GET', @sUrl, false 
       if @hr <0 
       begin 
        set @msg = 'sp_OAMethod Open failed' 
        goto eh 
       end 
       
       exec @hr = sp_OAMethod @obj, 'send' 
       if @hr <0 begin  set @msg = 'sp_OAMethod Send failed' goto eh end 
    
      exec @hr = sp_OAGetProperty @obj, 'status', @status OUT 
      if @hr <0 
      begin  
       set @msg = 'sp_OAMethod read status failed' 
       goto eh 
      end 
      if @status <> 200  
        begin set @msg = 'sp_OAMethod http status ' + str(@status) 
        goto eh 
      end 
       
      exec @hr = sp_OAGetProperty @obj, 'responseText', @response OUT 
      if @hr <0 
      begin  
       set @msg = 'sp_OAMethod read response failed' 
       goto eh 
      end 
      exec @hr = sp_OADestroy @obj 
      return 
    
    --Error handling code
    eh: 
      exec @hr = sp_OADestroy @obj 
      /*Raiserror(@msg, 16, 1) */
      return
    GO
    
    SET ANSI_NULLS OFF
    GO
    SET QUOTED_IDENTIFIER OFF
    GO

  2. #2
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    My first cursory glance would suggest that you use OUTPUT rather than just "OUT" on your parameter definitions...

    Take a look at BOL "Returning Data Using OUTPUT Parameters".

    Just a guess, forgive me if OUT is shorhand for "OUTPUT", but I could find no reference to that, and always use "OUTPUT" m'sef
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  3. #3
    Join Date
    Jun 2006
    Posts
    66
    thnaks i tried that but made no difference

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •