ez pay emails - 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

General SQL Server Forums

New to SQL Server Administration

ez pay emails

Author

Topic

newdba
Starting Member

30 Posts

Posted - 2011-06-13 : 15:15:23

I have this procedure that sends an email out however, this has to be started manually. How do i incorporate a clause which starts it automatically on a specific day and time?

USE [ITStorage]
GO

/****** Object: StoredProcedure [dbo].[sp_EZPay_Email] Script Date: 06/13/2011 15:14:12 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[sp_EZPay_Email]
as

EXEC msdb.dbo.sp_send_dbmail
@profile_name='SQLAlerts',
@recipients = 'dtrivedi@musicarts.com; MNewsom@MusicArts.com',
@blind_copy_recipients = 'msamborsky@musicarts.com;thicks@musicarts.com',
@body='EZ Pay Decline Email Process Has Started ',
@subject = 'EZ Pay Decline Email Process Has Started '

create table #ezpayloc
(locations varchar(50), storeemail varchar(50))

insert into #ezpayloc SELECT distinct [LOCATION], +('mac'+(location)+'@maclocal.com')
FROM [ITStorage].[dbo].[vw_PAST_DUE_LESSONS_EZPAY] order by location

DECLARE @locs nvarchar(max)
DECLARE @query nvarchar(1000)
declare @email nvarchar(max)
declare @recepients nvarchar(1000)
declare @body nvarchar(1000)
declare @subject nvarchar(1000)

declare t_ezpay
cursor for select storeemail, locations from #ezpayloc

open t_ezpay

fetch next from t_ezpay into @email, @locs

while @@FETCH_STATUS = 0
BEGIN

Print 'Processing: ' + @email
Set @body='All attempts to charge the credit card on file for the following EZ Pay Customers has failed.
Please contact the customer, update the card on file and charge the current balance due to the new card.

'

set @query='set NOCOUNT ON SELECT
[CUSTOMER]
,[ACCOUNT]
,[CUSTOMER_NAME]
,[PHONE]
,[DAY]
,[TIME]
,[INSTRUCTOR]
,[STUDENT]
FROM [ITStorage].[dbo].[vw_PAST_DUE_LESSONS_EZPAY]
WHERE location= ' + @locs

-- THE FOLLOWING IS THE TEST EMAIL
-- EXEC msdb.dbo.sp_send_dbmail
-- @profile_name='EZPay',
-- @recipients = 'dtrivedi@musicarts.com',
-- @body=@body,
-- @query=@query,
-- @subject = @email

-- THE FOLLOWING IS THE PRODUCTION EMAIL

EXEC msdb.dbo.sp_send_dbmail
@profile_name='EZPay',
@recipients = @email,
@blind_copy_recipients = 'msamborsky@musicarts.com;thicks@musicarts.com',
@body=@body,
@query=@query,
@subject = 'EZ Pay Declines'

fetch next from t_ezpay
into @email, @locs

END

close t_ezpay
deallocate t_ezpay

drop table #ezpayloc

EXEC msdb.dbo.sp_send_dbmail
@profile_name='SQLAlerts',
@recipients = 'dtrivedi@musicarts.com; MNewsom@MusicArts.com',
@blind_copy_recipients = 'msamborsky@musicarts.com;thicks@musicarts.com',
@body='EZ Pay Decline Email Process Has Completed Successfully ',
@subject = 'EZ Pay Decline Email Process Has Completed Successfully '

GO

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-06-13 : 15:27:53

Why are you using a different userid? Dtrivedi is your other one (we can verify this stuff via user IP: http://www.sqlteam.com/forums/pop_profile.asp?mode=display&id=61846

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

Subscribe to my blog