Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2003

    Unanswered: sp_OAMethod Question

    Hi, I'm trying to use sp_OA to call a third party COM object from within a SQL Server stored procedure. The method returns true/false based on its success; however, four of the input parameters have their values changed from within the method. These values need to be retained, and accessible from the sql procedure I am running.

    This is the syntax of the Com method I am using:

    GeocodeAddress(geocodeHandle as long,
    firm as string,
    street as string,
    city as string,
    state as string,
    zip as string,
    status as integer,
    numCandidates as integer,
    numCloseCandidates as integer) as Boolean

    The parameters geocodeHandle, status, numCandidates, and numCloseCandidates are modified in the procedure call... and true/false is returned based on whether or not the method completed successfully.

    I am having a difficult time understanding how to call this in SQL Server from the sp_oaMethod function call. I successfully create the object, and have successfully executed a more straightforward Method call. I do not know which parameters should be labeled "output" or whether I the parameters can retain their values, as assigned by the GeoCodeAddress method at all. This is my "best guess" at what should work, but I am getting a "type_mismatch" error, when I check the sp_OAGetErrorInfo procedure:

    ...a number of previous (working) statements to initiate the object

    declare @geocodeHandle bigint
    declare @status int
    declare @numCandidates int
    declare @numCloseCandidates int
    declare @firm varchar(50)
    declare @x int
    declare @object int
    declare @retval int

    exec @x = sp_OAMethod @object, 'GeocodeAddress', @retval Output, @geocodeHandle, NULL ,'814 NW 22nd Ave', 'Portland','OR', '97210', @status, @numCandidates, @numCloseCandidates

    Any help would be VERY appreciated!
    Thanks in advance

  2. #2
    Join Date
    Jul 2002
    Hi ,
    I am post here the way I use sp_OAMethod for the component SMTPsvg.Mailer (search in google if you do not know it / how to run it in VB /ASP ) .
    Hope it can help you.


    @Subject varchar(50),
    @BodyText varchar(255)
    --EXEC sp_SEND_ALERT_MAIL 'Testing Send Mail ' ,'This is the body '

    DECLARE @MAILFROM as varchar(200)
    SET @MAILFROM = ''
    DECLARE @REMOTE_HOST as varchar(200)

    declare @pObj int, @AspMail int
    declare @source varchar(30), @desc varchar(200)
    declare @property varchar(30), @result int

    exec @AspMail=sp_OACreate "SMTPsvg.Mailer", @pObj OUT
    if @AspMail <> 0 goto Err

    exec @AspMail=sp_OASetProperty @pObj, "RemoteHost",@REMOTE_HOST
    if @AspMail <> 0 goto Err

    exec @AspMail=sp_OASetProperty @pObj, "FromName",@MAILFROM
    if @AspMail <> 0 goto Err

    exec @AspMail=sp_OASetProperty @pObj, "FromAddress",@MAILFROM
    if @AspMail <> 0 goto Err

    exec @AspMail=sp_OASetProperty @pObj, "SubJect",@Subject
    if @AspMail <> 0 goto Err

    exec @AspMail=sp_OASetProperty @pObj, "BodyText",@BodyText
    if @AspMail <> 0 goto Err

    --Add AddRecipient
    exec @AspMail=sp_OAMethod @pObj, "AddRecipient", @property OUT, '', ''
    if @AspMail <> 0 goto Err

    print '@property'
    print @property
    exec @AspMail=sp_OAMethod @pObj, "SendMail", @property OUT
    if @AspMail <> 0 goto Err

    select "Result"=@result
    goto Done

    Print ' '
    Print '## ERROR ##'
    exec sp_OAGetErrorInfo null, @source OUT, @desc OUT
    select hr=convert(binary(4),@AspMail), source=@source, description=@desc
    goto Done

    exec sp_OADestroy @pObj
    return @AspMail


Posting Permissions

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