Database Mail - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

.NET Inside SQL Server (2005)

Database Mail

Author

Topic

penguin15793
Starting Member

10 Posts

Posted - 2010-10-05 : 13:35:13

Hello,

I need to know where I would start (tuts, recommendations, etc) to gain information on how to send an email automatically from a SQL server using an email address, message, and time all stored in one table on the server. I've read a little bit about database mail , but will this be able to do what I want?

Thanks!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-10-05 : 13:37:11

Yes it can. Could you show us a data example of the rows that will be in the table?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

penguin15793
Starting Member

10 Posts

Posted - 2010-10-06 : 11:54:31

Yes, so I will have one table, for example:

tblEmailData

with cols:

colMessage - varchar
colAddress - varchar
colProvider - varchar
colDateTime - datetime

A data entry would look like this:

"This is message", jamie830, @gmail.com, 1/2/10 5:50 PM

All commas represent a break to the next column in the row, and also i'm not sure about the formatting of the datetime so please excuse my pseudocode, but this is generally how its going to look.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-10-06 : 13:17:47

You'll need to create a SQL job that will execute every minute to see if any emails need to be sent out based upon the colDateTime column.

Why oh why are you prefixing your columns with col!? It's bad enough that the table is prefixed with tbl, but also doing it for columns is a bad practice.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

penguin15793
Starting Member

10 Posts

Posted - 2010-10-10 : 16:26:42

is a sql job like an automated stored procedure?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-10-11 : 12:43:10

Well it can be, but it can do all sorts of things. An Agent job is just a process that runs on a schedule. The process can be T-SQL, stored procedure, DOS/cmd commands, etc...

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

penguin15793
Starting Member

10 Posts

Posted - 2011-01-31 : 21:42:26

Hey, I was just working on this project. I got the database mail setup. Now i'm working on the job ( using T-SQL). This is what I have so far, however I still haven't figured out how to execute every minute..

@ID = (SELECT [ID] FROM dbo.maindata
WHERE [dateofmsg] <= GETDATE())
If @ID Not = ""
BEGIN
'pseudocode
@msg = (Select msg FROM dbo.maindata WHERE ID = @ID)
@recipient = Select recipient FROM dbo.maindata WHERE ID=@ID)

END

I know there are a lot of syntax errors in there, but hypothetically will this work? and can someone point me to a good t-sql syntax tut because i'm not sure how to formulate some of these sentences.

penguin15793
Starting Member

10 Posts

Posted - 2011-03-22 : 21:50:28

bump

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources