July 3, 2008 at 3:15 am
Hello everybody,
I have a table containing a date field.
What I want : every day a check is systematicaly done : when some rows from this table corresponding to the now date, an email was send to a specific user.
How can I do it?
Thanks a lot for your help
Steff
July 4, 2008 at 10:14 am
Begin with:
Sending SQL Server Notifications with CDOSYS
By : Desiree Harris
http://www.sql-server-performance.com/articles/dev/sending_sql_notifications_CDOSYS_p1.aspx
CDO: Collaborative Data Objects, being replaced by WebMail but I have no experience with the latter.
[font="Courier New"]
-- References to the CDOSYS objects are at the following MSDN Web site:
-- MSDN ->
-- MSDN Library ->
-- Win32 and COM Development ->
-- Messaging and Collaboration ->
-- CDO For Windows 2000
-- http://msdn.microsoft.com/en-us/library/ms527568(EXCHG.10).aspx
[/font]
One thing I found confusing was the parameters used by the CdoConfiguration Module: it looks likes a Web page address, but trying to connect to values such as
cdoSMTPServer:
'"http://schemas.microsoft.com/cdo/configuration/smtpserver"'.
Trying to view with a browser what appears to be a URL will net you the CONTENTS NOT FOUND results.
------------------------------------
[font="Courier New"]
1. CREATE THE CDO MESSAGE OBJECT
------------------------------------
DECLARE
@iMsg int,
@hr int,
@source varchar(255),
@description varchar(500),
@output varchar(1000),
@ls_Body varchar(4000)
EXEC @hr = sp_OACreate 'CDO.Message', @iMsg OUT[/font]
@hr allows you query the results to obtain a description of errors returned by system.
[font="Courier New"]
2. CONFIGURE THE CDO MESSAGE OBJECT
----------------------------------------
CDO USES A CONFIGURATION MODULE WITH OVER 30 FIELDS, THE FOLLOWING ARE NEEDED HERE:
-- cdoSMTPConnectionTimeout
-- cdoSMTPServer
-- cdoSMTPServerPickupDirectory
-- cdoSMTPServerPort
-- cdoSendUsingMethod
DECLARE
@ks_cdoSMTPConnectionTimeout varchar(255),
@ks_cdoSMTPServer varchar(255),
@ks_cdoSMTPServerPickupDirectory varchar(255),
@ks_cdoSMTPServerPort varchar(255),
@ks_cdoSendUsingMethod varchar(255),
@ks_cdoSendUsingPort varchar(1)
SET @ks_cdoSMTPConnectionTimeout = -- ACTUAL NAMES OF THE CONFIGURATION FIELDS ** NOT ** URL's
'"http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout"'
SET @ks_cdoSMTPServer =
'"http://schemas.microsoft.com/cdo/configuration/smtpserver"'
SET @ks_cdoSMTPServerPickupDirectory =
'"http://schemas.microsoft.com/cdo/configuration/smtpserverpickupdirectory"'
SET @ks_cdoSMTPServerPort =
'"http://schemas.microsoft.com/cdo/configuration/smtpserverport"'
SET @ks_cdoSendUsingMethod =
'"http://schemas.microsoft.com/cdo/configuration/sendusing"'
SET @ks_cdoSendUsingPort = '2'
[/font]
3. CONFIGURE THE CDO MESSAGE OBJECT: USE A PORT INSTEAD OF USING PICK-UP DIRECTORY
[font="Courier New"]
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields(@ks_cdoSendUsingMethod).Value','2'
[/font]
4. CONFIGURE THE CDO MESSAGE OBJECT: SET THE SMTP SERVER NAME; SAME AS DB SERVER NAME
[font="Courier New"]
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields(@ks_cdoSMTPServer).Value', @@SERVERNAME
[/font]
5. CONFIGURE THE CDO MESSAGE OBJECT: UPDATE THE CONFIGURATION MODULE OF CD0SYS
[font="Courier New"]
EXEC @hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', null
[/font]
6. SET THE EMAIL PARAMETERS
[font="Courier New"]
EXEC @hr = sp_OASetProperty @iMsg, 'To', @ls_To
[/font]
7. THE MESSAGE
[font="Courier New"]EXEC @hr = sp_OASetProperty @iMsg, 'TextBody', @ls_Fileslist
-- FOR HTML USE 'HTMLBody' NOT 'TextBody'.
[/font]
8. SEND THE E-MAIL
[font="Courier New"]
EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL[/font]
9. DON'T FORGET TO CLEAN-UP
[font="Courier New"]
IF (@iMsg IS NOT NULL) -- if @iMsg is NOT NULL then destroy it
EXEC @hr = sp_OADestroy @iMsg[
/font]
This should be enough for the fishing lesson. Get your own fish now. (Translation: I think it is a good idea to give pointers and starting points, then letting some work to be done by the person asking for assistence, instead of delivering the fish already cooked.)
July 4, 2008 at 12:43 pm
Forgot to tell you, the hint I have posted to SEND emails does NOT require that Outlook be installed and running on the MS SQL 2K host machine.
Of course, those at the receiving end will need Outlook or equivalent to receive the email.
Regards
July 7, 2008 at 7:44 am
/*
This may be another option for you.
Use code below if you don't have CDO mail - or are not allowed to create objects in SQL.
This requires that dbmail is set up on your SQL server.
1. Create a new sql job.
2. You can put the code in the step as below, or in a sql procedure and execute the procedure from the job step.
3. Set the job schedule to run once a day.
*/
If EXISTS (select *FROM MyTable WHERE CONVERT(CHAR(10),tabledate,101) = CONVERT(CHAR(10),GETDATE(),101))
Begin
EXEC msdb.dbo.sp_send_dbmail
@recipients = 'recipient@MyCompany.com',
@subject = 'Rows with current date found in MyTable',
@query = 'Select field1, field2, field3 from MyTable where CONVERT(CHAR(10),tabledate,101) = CONVERT(CHAR(10),GETDATE(),101)',
@importance = 'Normal'
End
ELSE
Begin
EXEC msdb.dbo.sp_send_dbmail
@recipients = 'recipient@MyCompany.com',
@subject = 'No rows with current date found in MyTable today',
@importance = 'Normal'
End
July 8, 2008 at 3:49 am
Thank to all for your advices.
Now, I can swim like a fish 😉
Steff
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply