By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,358 Members | 3,856 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,358 IT Pros & Developers. It's quick & easy.

SQL Query Email

benchpolo
100+
P: 142
I have a data table that contains STATUS and ADJCODE. I need to write s SQL query that counts records by STATUS and ADJCODE and send an email to a group of people with result of the counts.

I know how to count and group records as far as writing the SQL query, but I donít have any idea how to incorporate the email part.

Please advise.

Thanks.
Jun 26 '08 #1
Share this Question
Share on Google+
3 Replies


debasisdas
Expert 5K+
P: 8,127
You cant send mail directly by writing SQL query only. You need to define procerdures for that and change some other settings.
Jun 27 '08 #2

P: 9
Try this:

CREATE PROCEDURE [dbo].[sp_send_cdosysmail]
@From varchar(100) ,
@To varchar(100) ,
@Subject varchar(100)=" ",
@Body varchar(4000) =" ",
@server varchar(25) = 'YourServer'

AS
Declare @iMsg int
Declare @hr int
Declare @source varchar(255)
Declare @description varchar(500)
Declare @output varchar(1000)


EXEC @hr = sp_OACreate 'CDO.Message', @iMsg OUT
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', @server
EXEC @hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', null
EXEC @hr = sp_OASetProperty @iMsg, 'To', @To
EXEC @hr = sp_OASetProperty @iMsg, 'From', @From
EXEC @hr = sp_OASetProperty @iMsg, 'Subject', @Subject
EXEC @hr = sp_OASetProperty @iMsg, 'TextBody', @Body
EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL
EXEC @hr=sp_OADestroy @iMsg
GO

Then execute with: exec sp_send_cdosysmail 'from address','to address', 'title', Message'

Though you do need a mail server..and adapt code as needed.

Jinx.
Jun 27 '08 #3

ck9663
Expert 2.5K+
P: 2,878
If you're using sql 2005 you're in luck.

Read more here

-- CK
Jun 29 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.