VB icon

Add E-Mail Job to SQL Server

Email
Submitted on: 1/5/2015 3:35:00 PM
By: Lewis E. Moten III (from psc cd)  
Level: Intermediate
User Rating: By 9 Users
Compatibility: ASP (Active Server Pages)
Views: 1994
 
     This script helps start you off to learning how to automate some of your processes with SQL . For this example, I have chosen to send Email through SQL Server using vbScript. Messages are pulled from a table and deleted once they are sent. You need to create a table in the master database called "Email" and assign the fields "To", "From", "Subject" and "Body". The script will do the rest. If you "SA" account has a password, you will need to modify the script to relflect those changes.
 
code:
Can't Copy and Paste this?
Click here for a copy-and-paste friendly version of this code!
				
'**************************************
' Name: Add E-Mail Job to SQL Server
' Description:This script helps start you off to learning how to automate some of your processes with SQL . For this example, I have chosen to send Email through SQL Server using vbScript. Messages are pulled from a table and deleted once they are sent. You need to create a table in the master database called "Email" and assign the fields "To", "From", "Subject" and "Body". The script will do the rest. If you "SA" account has a password, you will need to modify the script to relflect those changes.
' By: Lewis E. Moten III (from psc cd)
'**************************************

<%
Dim lObjConn
Dim vbs
Dim lStrSQL
Set lObjConn = Server.CreateObject("ADODB.Connection")
lObjConn.Open _
	"Provider=SQLOLEDB.1;" & _
	"Data Source=LOCALHOST;" & _
	"Initial Catalog=msdb;" & _
	"User ID=sa;" & _
	"Password=;"
lStrSQL = "sp_add_job " & _
	"@job_name = 'SendMailJob'," & _
	"@enabled = 1," & _
	"@description = 'Sends e-mail messages'," & _
	"@start_step_id = 1," & _
	"@category_name = '[Uncategorized (Local)]'"
	
Set lObjRs = lObjConn.Execute(lStrSQL)
vbs = GetScript("LOCALHOST", "Master", "")
vbs = Replace(vbs, "'", "''")
lStrSQL = "sp_add_jobstep " & _
	"@job_name = 'SendMailJob', " & _
	"@step_id = 1, " & _
	"@step_name = 'Find and Send Mail', " & _
	"@subsystem = 'ACTIVESCRIPTING', " & _
	"@command = '" & vbs & "'"
lObjConn.Execute lStrSQL
lStrSQL = "sp_add_jobschedule " & _
	"@job_name = 'SendMailJob', " & _
	"@name = 'Every 10 Minutes', " & _
	"@enabled = 1, " & _
	"@freq_type = 4, " & _
	"@freq_interval = 1, " & _
	"@freq_subday_type = 0x4, " & _
	"@freq_subday_interval = 10"
lObjConn.Execute lStrSQL
lStrSQL = ""
Set lObjConn = Nothing
Function GetScript(ByRef pStrDataSource, ByRef pStrInitialCatalog, ByRef pStrSAPassword)
	GetScript = _
		"Dim lObjConn" & vbCrLf & _
		"Dim lObjRs" & vbCrLf & _
		"Dim lStrSQL" & vbCrLf & _
		"Dim lObjMailer" & vbCrLf & _
		vbCrLf & _
		"Const adOpenForwardOnly = 0" & vbCrLf & _
		"Const adLockPessimistic = 2" & vbCrLf & _
		"Const adCmdText = 1" & vbCrLf & _
		vbCrLf & _
		"lStrSQL = ""SELECT [From], [To], [Subject], [Body] FROM [Email]""" & vbCrLf & _
		vbCrLf & _
		"Set lObjConn = CreateObject(""ADODB.Connection"")" & vbCrLf & _
		"Set lObjRs = CreateObject(""ADODB.Recordset"")" & vbCrLf & _
		"lObjConn.Open _" & vbCrLf & _
		"	""Provider=SQLOLEDB.1;"" & _" & vbCrLf & _
		"	""Data Source=" & pStrDataSource & ";"" & _" & vbCrLf & _
		"	""Initial Catalog=" & pStrInitialCatalog & ";"" & _" & vbCrLf & _
		"	""User ID=sa;"" & _" & vbCrLf & _
		"	""Password=" & pStrSAPassword & ";""" & vbCrLf & _
		vbCrLf & _
		"lObjRs.Open lStrSQL, lObjConn, adOpenForwardOnly, adLockPessimistic, adCmdText" & vbCrLf & _
		vbCrLf & _
		"While Not lObjRs.EOF" & vbCrLf & _
		"	Set lObjMailer = CreateObject(""CDONTS.NewMail"")" & vbCrLf & _
		"	lObjMailer.From		= lObjRs(0) & """"" & vbCrLf & _
		"	lObjMailer.To		= lObjRs(1) & """"" & vbCrLf & _
		"	lObjMailer.Subject	= lObjRs(2) & """"" & vbCrLf & _
		"	lObjMailer.Body		= lObjRs(3) & """"" & vbCrLf & _
		"	lObjMailer.Send" & vbCrLf & _
		"	lObjRs.Delete" & vbCrLf & _
		"	lObjRs.MoveNext" & vbCrLf & _
		"	Set lObjMailer = Nothing" & vbCrLf & _
		"Wend" & vbCrLf & _
		vbCrLf & _
		"lObjRs.Close" & vbCrLf & _
		"lObjConn.Close" & vbCrLf & _
		vbCrLf & _
		"Set lObjRs = Nothing" & vbCrLf & _
		"Set lObjConn = Nothing"
End Function
%>
done . . .<BR><BR>
You may wish to open SQL Enterprise Manger and find the
"Management" folder under your database. Find "SQL Server Agent" with
a child node called "Jobs". A new job called "SendMailJob" should be
present.


Other 63 submission(s) by this author

 


Report Bad Submission
Use this form to tell us if this entry should be deleted (i.e contains no code, is a virus, etc.).
This submission should be removed because:

Your Vote

What do you think of this code (in the Intermediate category)?
(The code with your highest vote will win this month's coding contest!)
Excellent  Good  Average  Below Average  Poor (See voting log ...)
 

Other User Comments


 There are no comments on this submission.
 

Add Your Feedback
Your feedback will be posted below and an email sent to the author. Please remember that the author was kind enough to share this with you, so any criticisms must be stated politely, or they will be deleted. (For feedback not related to this particular code, please click here instead.)
 

To post feedback, first please login.