Solved

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

Posted on 2004-08-02
14
300 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 5

Author Comment

by:bullrout
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 3

Expert Comment

by:Younkman
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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 500 total points
Comment Utility
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
Comment Utility
I appreciate the help, I should be able to find documentation on the subject.

Sean
0

Featured Post

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.

Join & Write a Comment

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

744 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

17 Experts available now in Live!

Get 1:1 Help Now