xp_sendmail question

[SeaninSeattle]

I'm a little confused...I want to use xp_sendmail to kick out some shipment notifications to customers, and generally understand the syntax, but do not understand where it specifies the table that contains the data.

Could someone give me a nice, easy example of a simple xp_sendmail statement against a table with some simple data in it (like name, email address, message), etc?

Thanks,
//sse

Sean Engle
Admin/DirIS

 

[tlbroadbent]

I recommend reading about xp_sendmail in SQL Books Online (BOL). Great resource with examples. The folloing comes directly from BOL.

This example sends the results of the query SELECT * FROM INFORMATION_SCHEMA.TABLES as a text file attachment to Robert King. It includes a subject line for the mail and a message that will appear before the attachment. The @width parameter is used to prevent line breaks in the output lines.

EXEC xp_sendmail @recipients = 'robertk',
@query = 'SELECT * FROM INFORMATION_SCHEMA.TABLES',
@subject = 'SQL Server Report',
@message = 'The contents of INFORMATION_SCHEMA.TABLES:',
@attach_results = 'TRUE', @width = 250


Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[SeaninSeattle]

Thanks Terry -

I've set up SQL Mail, and have it running. My only question now is how do I tie the xp_... commands with a table of shipping confirmations I want to mail out?

I mean, I would imagine that I need to set the field that has the email address in as the recipient - but how can I get it to keep looping through the table until all the addresses have had mail sent to them (along with the other relevant field info - tracking numbers, etc.)?

Thanks!
//sse Sean Engle
Admin/DirIS

 

[tlbroadbent]

The best answer depends on when you send the confirmations. If you want to send the confirmation message the moment the order ships, you could add a trigger to the table and send the email when the ship flag (or date or other indicator) is set.

If you want to send batches of messages, you could write a stored procedure to read the Orders table and send messages for all orders shipped in the past 24 hours or whatever criteria you choose.

Trigger Example: Assume you have the following tables.

Orders: Orders and shipments
Customers: Customer info including Email Address

[tt]Create Trigger Trg_Update_Insert_Orders On Orders
For Update, Insert

As

Declare @Ordnum Int, @Shipdate Datetime
Declare @Mail Varchar(60), @Compname Varchar(60),
Declare @Subj Varchar(80), @Msg Varchar(240)

Select
@OrdNum=I.Orderno,
@Shipdate=I.Shipdate,
@Mail=C.EmailAddress,
@Compname=C.Companyname
From Inserted I
Join Company C
On I.Companyid=C.Id

If @Shipdate Is Not Null
Begin

-- Create Message... Add More Information As Needed.
Select
@Subj='Shipment Confirmation For '+@Compname,
@Msg='Your Order From Xyz Corp. Shipped On ' +
Convert(Char(10),@Shipdate,101)+'.'

Exec Master.Dbo.Xp_Sendmail
@Recipients=@Mail,
@Subject=@Subj,
@Message=@Msg
End[/Tt]

------------------------------------------------------------

Stored Procedure Example: Assume the same tables exist

Create Procedure Sp_ConfirmationEmail As

Declare @Ordnum Int, @Shipdate Datetime
Declare @Mail Varchar(60), @Compname Varchar(60),
Declare @Subj Varchar(80), @Msg Varchar(240)

Set Nocount On

Declare Shippedcursor Cursor For
Select Orderno, Shipdate, EmailAddress, Companyname
From Orders O
Join Customers C
On O.Customerid=C.Id
Where Shipdate Is Not Null
And Confrmationsent=0

Open ShippedCcursor
Fetch Next From ShippedCursor Into @Ordnum, @Shipdate, @Mail, @Compname

While (@@Fetch_Status <> -1)
Begin

-- Create Message... Add More Information As Needed.
Select
@Subj='Shipment Confirmation For '+@Compname,
@Msg='Your Order From Xyz Corp. Shipped On '+Convert(Char(10),@Shipdate,101)+'.'

Exec Master.Dbo.Xp_Sendmail @Recipients=@Mail,
@Subject=@Subj,
@Message=@Msg

Fetch Next From ShippedCursor Into @Ordnum, @Shipdate, @Mail, @Compname

End

Close ShippedCursor
Deallocate ShippedCursor
Set Nocount Off Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions.