?
Solved

Emailing the results of a select statement in a stored procedure?

Posted on 2004-08-02
14
Medium Priority
?
326 Views
Last Modified: 2008-02-01
Hi There,

I would like to know how I can email the results this select statement using SQL server? Does anyone have any code samples?

Sean

CREATE PROCEDURE Getrenewal

AS
Select domain_name,domain_name_renewal from domain_names
where domain_name_renewal >= {fn CURDATE()}
AND domain_name_renewal < DATEADD(DAY,30, {fn CURDATE()})
GO
0
Comment
Question by:bullrout
  • 6
  • 4
  • 4
14 Comments
 
LVL 9

Expert Comment

by:crescendo
ID: 11695400
If you use Exchange Server in your network, you can use the built-in SQL MAIL functionality. Look up SQL Mail in Books Online, the stored procedure xp_sendmail does what you want.
0
 
LVL 5

Author Comment

by:bullrout
ID: 11695426
Do you have any code samples? I wanna try to get this one done before I get too tired.
0
 
LVL 9

Expert Comment

by:crescendo
ID: 11695439
If you don't have Exchange Server, you can write a DOS command-line script to run the query, send the output to a text file, and email the file.

OSQL is a command line utility to run the query, look it up in Books Online.

BLAT is a free command-line program for sending an email. It's very easy to use and set up. See

http://sourceforge.net/projects/blat/
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!

 
LVL 5

Author Comment

by:bullrout
ID: 11695482
I am using a remote server with limited permissions for developers, I would rather contain all of the functionality within the stored procedure if possible.

Sean
0
 
LVL 9

Expert Comment

by:crescendo
ID: 11695508
It's definitely not something to do when you are tired! :)

Look at these articles, especially the first:

http://www.winnetmag.com/Windows/Articles/ArticleID/124/pg/2/2.html
http://support.microsoft.com/default.aspx?scid=kb;EN-US;263556
0
 
LVL 3

Expert Comment

by:Younkman
ID: 11695520
You can use a DTS package to send the data if you like.

First, create a DTS package that exports that query to a text file or an Excel spreadsheet or whatever.
Then create another DTS package that first executes the original DTS package, and then, on Success, perfoms a Send Mail Task, sending the email to the recipient with the exported data attached.

If you have any questions or if this is unclear to you, please let me know.

Thanks
Y
0
 
LVL 9

Expert Comment

by:crescendo
ID: 11695542
You need lots of permissions on the server to set it up. You need to create a mail profile, probably install Outlook or similar. Do you have permission to do that?

The BLAT option is very simple if you have an SMTP server you can relay through, so that may be your best option as you can do it on your local machine if you have access to the tables.
0
 
LVL 3

Expert Comment

by:Younkman
ID: 11695639
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_3_sqlmal_6gq4.asp

Sorry, didn't notice you wanted everything within the SP.

Heres one on how to configure your mail profiles:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_1_server_0r1w.asp

Then heres some pseudo-code to get you started with your sp. (some from here http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_xp_aa-sz_6hbg.asp)

CREATE PROCEDURE send_Getrenewal_email

AS
exec xp_startmail
EXEC xp_sendmail 'email_alias', @query = 'Getrenewal'
exec xp_stopmail
GO

Cheers
Y

0
 
LVL 5

Author Comment

by:bullrout
ID: 11695654
hi all,

I am allowed to create DTS packages, and the current DB that I'm using does not allow me to access xp_sendmail, but I should be able to grab the procedure of my development machine and then re-create the proc on the remote server?

I will try this first.

BTW younkman, the information that you mentioned regarding the DTS package, do you have any samples?
0
 
LVL 3

Expert Comment

by:Younkman
ID: 11695716
Kinda, but they are not simple text like a query or an sp.  I can walk you through the steps, though:

Open enterprise manager
click on data transformation services
click on local packages
click Action > New package
click task > Execute Package task (#10)
Choose the DTS package that exports your data to the text file
The click task > send mail task (#8)
Fill in the information and attach the file exported by the other DTS
CTRL click the both of them, then click Workflow and selec On Success.

Seemed to work fine for me.  Lemme know if you run into any snags.
Cheers.
Y
0
 
LVL 5

Author Comment

by:bullrout
ID: 11695723
Thanks for the help guys, I will try to have a go at it, tomorrow morning....

let you know how it all goes. Sean
0
 
LVL 5

Author Comment

by:bullrout
ID: 11761049
Hi Younkman,

I managed to create a DTS package and the email the results in an excel spread sheet to myself, what I really wanted to do is to email the results of the query (such as password information) to the user email. I can't use the administrator email stored procedures because the administrator has locked access, do you know if I can send a formatted email through DTS containing the results of a query?

Sean
0
 
LVL 3

Accepted Solution

by:
Younkman earned 2000 total points
ID: 11762661
Hmm.  Well, I would use the sendmail procedure to do something like that.  If you can't use that, you can script just about anything with activeX or visual basic.  
http://www.sqldts.com/default.aspx?235 (which I got from here: http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21019078.html)
Theres a link showing how you can change the body text, if you like.  
http://www.winnetmag.com/SQLServer/Articles/ArticleID/38658/pg/2/2.html
Theres another possible solution.

Of course, you can always simply send them the email as an attachment, but it doesn't look as nice.  If you can't use sendmail, try scripting.

Cheers,

Younkman
0
 
LVL 5

Author Comment

by:bullrout
ID: 11771513
I appreciate the help, I should be able to find documentation on the subject.

Sean
0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

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

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
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.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

850 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