SQL Query Email

[benchpolo]

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.

[debasisdas]

You cant send mail directly by writing SQL query only. You need to define procerdures for that and change some other settings.

[JinxT]

Try this:

CREATE PROCEDURE [dbo].[sp_send_cdosysm ail]
@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_OASetPropert y @iMsg, 'Configuration. fields("http://schemas.microso ft.com/cdo/configuration/sendusing").Val ue','2'
EXEC @hr = sp_OASetPropert y @iMsg, 'Configuration. fields("http://schemas.microso ft.com/cdo/configuration/smtpserver").Va lue', @server
EXEC @hr = sp_OAMethod @iMsg, 'Configuration. Fields.Update', null
EXEC @hr = sp_OASetPropert y @iMsg, 'To', @To
EXEC @hr = sp_OASetPropert y @iMsg, 'From', @From
EXEC @hr = sp_OASetPropert y @iMsg, 'Subject', @Subject
EXEC @hr = sp_OASetPropert y @iMsg, 'TextBody', @Body
EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL
EXEC @hr=sp_OADestro y @iMsg
GO

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

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

Jinx.

[ck9663]

If you're using sql 2005 you're in luck.

Read more here

-- CK