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








LVL 26
Alan WarrenApplications DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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 WarrenApplications DeveloperAuthor 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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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 WarrenApplications DeveloperAuthor 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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.