Solved

use xp_sendmail to execute a stored procedure with input parameters

Posted on 2003-11-11
6
2,900 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
ID: 9728358
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
ID: 9728450
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
ID: 9728462
been havin a spell like that myself brother!  (ain't it great!)
0
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
LVL 26

Accepted Solution

by:
Hilaire earned 350 total points
ID: 9730378
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
ID: 9733436
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
ID: 9834618
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

809 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