[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2917
  • Last Modified:

use xp_sendmail to execute a stored procedure with input parameters

Hi

I am trying to use xp_sendmail to execute a stored procedure with input parameters.

eg...
EXEC master..xp_sendmail @recipients = 'Alan Warren',
-- @query = 'select * from sometable',  -- this works fine
   @query = 'exec spDoNewRegister Mr, Donald, Fredrick, Duck, 5-Aug-1958, WEB',
   @subject = 'The subject',
   @message = 'msgBody',
   @dbuse = 'DataTesting'


The stored proc executes from query analyser, no problems.
  exec spDoNewRegister 'Mr', 'Donald', 'Fredrick', 'Duck', '5-Aug-1958', 'WEB'

Proc being executed by xp_sendmail expects these paramaters:

  @PersonTitle nvarchar(255),
  @PersonGivenName nvarchar(255),
  @PersonOtherNames nvarchar(255),
  @PersonSurname nvarchar(255),
  @PersonDOB smalldatetime,
  @Busprefix nvarchar(4)

Can his be done, if so how?
An example would be hepful.


Alan








0
Alan Warren
Asked:
Alan Warren
2 Solutions
 
jadedataMS Access Systems CreatorCommented:
Hey alanwarren!

  I can't even begin to propose a solution, but it's good to hear from you again!!
  (Please do not accept this comment as an answer to this question!)

regards
Jack
0
 
Alan WarrenAuthor Commented:
HI Jack,

Hows things with you?
I,ve been flat out like a lizard drinking.
have n't answered a Q for weeks.

Alan :)
0
 
jadedataMS Access Systems CreatorCommented:
been havin a spell like that myself brother!  (ain't it great!)
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
HilaireCommented:
Hi, I guess the code you posted doesn't work because MS wants to prevent people from executing SQL code that affects the data without beeing fully aware of it.

If you stored procedure doesn't perform any delete, insert or update statement (pure SELECT query), you could turn it into a user-defined function returning TABLE datatype  (recordset).
Then you could use it with

@query = 'select * from  udf_DoNewRegister (Mr, Donald, Fredrick, Duck, 5-Aug-1958, WEB'),

If the stored procedure performs inserts, update, delete statements, I think you should split the logic
Add an output parameter to the stored procedure to get the ID of the newly created record
add a 'Set @ID_out = @@IDENTITY' after the insert in the stored procedure

then, the logic will be
1) call the SP, last param is OUTPUT @ID_out
2) call xp_sendmail @query='select * from contacts where ID_PK=' + @ID_out

HTH

Hilaire


0
 
LowfatspreadCommented:
isn't the problem you've not quoted the paramters?
so that the SP doesn't receive them correctly

EXEC master..xp_sendmail @recipients = 'Alan Warren',
-- @query = 'select * from sometable',  -- this works fine
   @query = 'exec spDoNewRegister Mr, Donald, Fredrick, Duck, 5-Aug-1958, WEB',
   @subject = 'The subject',
   @message = 'msgBody',
   @dbuse = 'DataTesting'

e.g.
EXEC master..xp_sendmail @recipients = 'Alan Warren',
-- @query = 'select * from sometable',  -- this works fine
   @query = 'exec spDoNewRegister n''Mr'', n''Donald'',n''Fredrick'',n''Duck'', ''5-Aug-1958'',n''WEB''',
   @subject = 'The subject',
   @message = 'msgBody',
   @dbuse = 'DataTesting'


'' thats two single quotes..
and you probably need to indicate that they are nvarchars as well...

another way around your problem is to
insert the parm values into  a temp table
and have a procedure read that table and pass the parms to the other one...

hth  
0
 
Alan WarrenAuthor Commented:
Hi All,

Sorry about leaving this post open so long.

I have managed to get this working, by sending a mail message to the user account that is defined to start sql server and sql mail.

The mail message contains a specific subject.

I then created a stored procedure that interogates mail store for unread mail containing the specific subject.

If one is found I invoke xp_ProcessMail which parses the body of the email(valid xml) which creates a new member then fires a response email containing member logon and password using xp_sendmail.

After thoroughly testing this process and modifying to handle errors encoutered, I created a sql server agent job that runs the sp every few minutes.

The following has been modified for publication

Regards Alan :)

How it works:

Mesage sent from web page:

--To
somerecipient@somedomain.com

-- Subject
New Member Submission - from (Alan Warren)

-- Body
<data>
  <row>
   <CusTitle>Mr</CusTitle>
   <CusFName>Alan</CusFName>
   <CusOtherNames></CusOtherNames>
   <CusLName>Warren</CusLName>
   <CusDOB>1958-08-05T00:00:00</CusDOB>
  </row>
</data>

Stored procedure to process mail:

CREATE   procedure spTestAutoResponse
as

declare
  @hMessage varchar(255),
  @Message nvarchar(4000),
  @Subject varchar(255),
  @originator varchar(255),
  @originator_address varchar(255),
  @pos int,                           -- unused
  @ResponseText NVARCHAR(4000)


exec master..xp_findnextmsg @unread_only = true, @msg_id = @hMessage out


while @hMessage is not null begin
  exec master..xp_readmail
    @msg_id = @hMessage,
    @peek = true,
    @originator = @originator out,
    @originator_address = @originator_address out,
    @Subject = @Subject out,          -- can this be a filter?
    @message =@Message out


  if charindex('New Member Submission', @Subject, 1) > 0 begin
    -- Read the message again with peek set to false so message is marked as read
    exec master..xp_readmail
      @msg_id = @hMessage,
      @peek = false,
      @originator = @originator out,
      @originator_address = @originator_address out,
      @Subject = @Subject out,          -- can this be a filter?
      @message =@Message out


    /* -------------------------------------------------------------
    Parse the data out of the message body and call spDoNewRegister
    to insert a record into tbl_Customers
   
    The mail message body contains the details of the website user
    who wants to register as a new member

    The body text is xml, it contains one data row with 5 fields:
    -------------------------------------------------------------
    <data>
      <row>
       <CusTitle>Mr</CusTitle>
       <CusFName>Daffy</CusFName>
       <CusOtherNames>Black as Sin</CusOtherNames>
       <CusLName>Duck</CusLName>
       <CusDOB>1996-07-04T00:00:00</CusDOB>
      </row>
    </data>
    -------------------------------------------------------------

    The openxml with clause specifies tbl_Customers as its
    schema table - so tbl_Customers provides the transact sql  
    field definitions for the xml fields!

    -------------------------------------------------------------- */

    declare
      @xmlData nvarchar(4000),
      @xmlDoc int,
      @CusTitle nvarchar(8),
      @CusFName nvarchar(40),
      @CusOtherNames nvarchar(40),
      @CusLName nvarchar(40),
      @CusDOB smalldatetime
 
    -- get the xml data string from the message body
      -- for now the entire body is the xml, however
      -- the body may need to contain some text other than the xml?
      -- or the xml may need to be contained in an attachment?
    set @xmlData = @Message


    -- create an xml document object from the xml data string
    exec sp_xml_preparedocument @xmlDoc output, @xmlData


    -- read the xml document field values into local variables
    -- this populates the the local variables but does not return a grid
    -- Select * however does return a grid, go figure.
    select
      @CusTitle = CusTitle,
      @CusFName  = CusFName,
      @CusOtherNames = CusOtherNames,
      @CusLName = CusLName,
      @CusDOB = CusDOB
   

    from
      openxml(@xmlDoc, 'data/row', 2)
      with (
            CusLName nvarchar(50),
            CusFName nvarchar(50),
            CusOtherNames nvarchar(50),
            CusTitle nvarchar(8),
            CusDOB smalldatetime
      )

--       openxml(@xmlDoc, 'data/row', 2) with tbl_Customers
        -- openxml(@xmlDoc, 'data', 2) with tbl_Customers
        -- flags = 2 = Use the element-centric mapping
        -- with (id int)

    -- select the local variables populated from xml
    -- select @CusTitle, @CusFName, @CusOtherNames, @CusLName, @CusDOB
    exec spDoNewRegister
      @CusTitle = @CusTitle,
      @CusFName =@CusFName,
      @CusOtherNames = @CusOtherNames,
      @CusLName = @CusLName,
      @CusDOB = @CusDOB,
      @ResponseText = @ResponseText output

    exec sp_xml_removedocument @xmlDoc

     exec master..xp_sendmail
       @recipients = @originator_address,
       @subject = 'Welcome to Cash Australia',   -- may personalise this
       @message = @ResponseText
       --@dbuse = @dbName  --db_name  --db_name()  --@dbName
 


  end -- end if

  -- read next message (@msg_id will be null after last message is read)
  exec master..xp_findnextmsg
    @unread_only = true,
    @msg_id=@hMessage out
end -- end while

GO







0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now