Solved

use xp_sendmail to execute a stored procedure with input parameters

Posted on 2003-11-11
6
2,898 Views
Last Modified: 2008-02-01
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
Comment
Question by:Alan Warren
6 Comments
 
LVL 32

Expert Comment

by:jadedata
Comment Utility
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
 
LVL 26

Author Comment

by:Alan Warren
Comment Utility
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
 
LVL 32

Expert Comment

by:jadedata
Comment Utility
been havin a spell like that myself brother!  (ain't it great!)
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 26

Accepted Solution

by:
Hilaire earned 350 total points
Comment Utility
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
 
LVL 50

Assisted Solution

by:Lowfatspread
Lowfatspread earned 150 total points
Comment Utility
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
 
LVL 26

Author Comment

by:Alan Warren
Comment Utility
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now