sending email with SQL

Question

Post reply

sending email with SQL

Ric-399929

SSChasing Mays

Points: 648
More actions
January 22, 2007 at 10:57 pm

#368875

Hi there,
I am no expert looking for advise if the following is posible and what should I look into in order to get it done.
Can I set a job for every night that would look at a date field of all my records in a table and if today - DateFieldValue = 30 days then I would send a email to sales@myCompany.com ?
If you have any links on the subject that would be great too.
Thanks in advance.
Ric

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply

SQLZ SSChampion Points: 12872 More actions · Answer 1

Hi Ric,

There are various ways you could do this. I'll provide one such way that will at least help you get started.

First of all you'll need to set up a database mail account within SQL Server. You can do this in a couple of ways but I suggest you look up database mail in books online as it's well documented there.

I've included an example script at the bottom of this post that will set up database mail for you, assuming you have access to an smtp mail server. You'll need to change various fields so that they are specific to your environment. I haven't gone into detail with what the stored procs do as they're well documented in BOL.

Now, once you've got database mail set up sending an email from within SQL Server is as easy as executing sp_send_dbmail procedure. I've included an example below.

Note that there are various different parameters for sp_send_dbmail so I suggest you review it's use in BOL.

Regarding your particular scenario, you just need to set up the job and determine what criteria needs to be met for you to fire off the mail. Perhaps something like:

if

exists(select 1 from mytable where datediff(day, DateFieldValue, getdate()) = 30)

begin

--send the mail

EXEC

msdb.dbo.sp_send_dbmail

@profile_name

= 'SQLMailProfile', --OR WHATEVER YOU CALLED THE PROFILE YOU CREATED

@recipients

= 'sales@myCompany.com',

@body

= 'a message.',

@subject

= 'a subject line'

end

This is obviously just a guess as to what you might need but hopefully it gives you an idea.

Anyway, here's an example of how you'd set up a db mail account and profile.

Hope this helps to get you started.........

EXECUTE
@account_name
@description
@email_address
@display_name
@mailserver_name
go
EXECUTE
@profile_name
@description
go

EXECUTE
@profile_name
@account_name
@sequence_number
go

Karl
source control for SQL Server

Ric-399929 SSChasing Mays Points: 648 More actions · Answer 2

Thanks for your reply this was very helpful information and got me going on the right track for sure. However I seem to be stuck. I set my Database Mail according to your information and as well the ones found here but when I try to send test emails I receive nothing at the other end. So my question is how do I troubleshoot this or what should I be looking at in order to find my problem? Not sure if the problem could be related to that but I am using SQL Sever Develloper Edition.

Thanks again,

Ric

Send test email

MohammedU SSCoach Points: 19644 More actions · Answer 3

Check the database mail log for any errors..

Right click Database Mail/View database Mail log...

MohammedU
Microsoft SQL Server MVP

Ric-399929 SSChasing Mays Points: 648 More actions · Answer 4

The logs I shows:

8h15 AM "DatabaseMail process is started".

8h25 AM "DatabaseMail process is shutting down".

I see no other kind of message.

Ric

MohammedU SSCoach Points: 19644 More actions · Answer 5

Are you using right email address?

SQL Server 2005 Books Online

Troubleshooting Database Mail: Mail queued, not delivered

http://msdn2.microsoft.com/en-us/library/ms190630.aspx

MohammedU
Microsoft SQL Server MVP

Ric-399929 SSChasing Mays Points: 648 More actions · Answer 6

To answer your last question, yes my email address used in my profile is fully fonctionnal.

After sending a test email I looked at my SMTP Mail Server Logs and I saw no entry of my test email. Also I checked my SQL Database Mail Logs and my email status was mark as sent.

So what steps would you recommend I do from here?

Thanks

alen teplitsky SSC-Dedicated Points: 30014 More actions · Answer 7

how many mail servers in the system? you might have to allow relaying.

Ric-399929 SSChasing Mays Points: 648 More actions · Answer 8

We only have one mail server and it is located on a different machine so in my profile the server name is something like 10.10.1.3

David Knapp SSC Enthusiast Points: 177 More actions · Answer 9

Did you ever get it running? I have a similar problem getting Database Mail going on SQL 2005.

Tommy Bollhofer SSChampion Points: 14940 More actions · Answer 10

What sort of problems are you having? The very first step I recommend in troubleshooting any SMTP related issues with database mail is to first confirm you can send mail from the MSSQL host via telnet - i.e. http://support.microsoft.com/kb/153119

Tommy

Follow @sqlscribe

Megistal SSCrazy Eights Points: 8787 More actions · Answer 11

Hi

Just a reminder that SQL is not a server to echange mail. What I mean by that is in some rare circumstances it can make hang the entire server / instance.

In many cases it would have been a wonderful solution but because of that bug could happen, we always developed workaround solution to prevent a possible hang of the instance / server.

If your server is critical, I would advise using something else for your mail.

Linda-272642 Ten Centuries Points: 1100 More actions · Answer 12

I had to use a relay in order for my database mail to work. There is another way you can email that is using xp_sendmail.

bring up sql server 2005 surface area configuration -> surface area configuration for features -> under sql mail enable sql mail stored procedures

then within your procedures you can add this code:

xp_sendmail {[@recipients =] 'recipients [;...n]'}

[,[@message =] 'message']

[,[@query =] 'query']

[,[@attachments =] 'attachments [;...n]']

[,[@copy_recipients =] 'copy_recipients [;...n]'

[,[@blind_copy_recipients =] 'blind_copy_recipients [;...n]'

[,[@subject =] 'subject']

[,[@type =] 'type']

[,[@attach_results =] 'attach_value']

[,[@no_output =] 'output_value']

[,[@no_header =] 'header_value']

[,[@width =] width]

[,[@separator =] 'separator']

[,[@echo_error =] 'echo_value']

[,[@set_user =] 'user']

[,[@dbuse =] 'database']

David Knapp SSC Enthusiast Points: 177 More actions · Answer 13

I thought SQL Mail required Office to be installed. That is what many of us want to get rid of. My Database Mail does not work either and the troubleshooting has been unhelpful.

Linda-272642 Ten Centuries Points: 1100 More actions · Answer 14

Linda-272642

Ten Centuries

Points: 1100

November 5, 2007 at 1:33 pm

#748403

are you using exchange server?