reallly really need help with this

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 2008 Forums

SQL Server Administration (2008)

reallly really need help with this

Author

Topic

dtrivedi
Posting Yak Master

153 Posts

Posted - 2010-07-20 : 14:03:24

This query run fine. it sends me the emails that i need.

here is the query:

use ANALYTICS

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

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

select * from #ezpayloc

SELECT distinct [LOCATION], +('mac'+(location)+'@maclocal.com')
FROM vw_PAST_DUE_LESSONS_EZPAY] order by location
GO

DECLARE @locs varchar(max)
declare @email varchar(max)

declare t_ezpay
cursor for select storeemail from #ezpayloc
declare t_ezpay
cursor for select locations from #ezpayloc
open t_ezpay

fetch next from t_ezpay into @email

fetch next from t_ezpay into @locs

while @@FETCH_STATUS = 0
BEGIN

Print 'Processing: ' + @email

EXEC msdb.dbo.sp_send_dbmail
@recipients = 'dtrivedi@musicarts.com',
@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 balance to the new card.',
@query='SELECT [LOCATION],[CUSTOMER],[ACCOUNT],[CUSTOMER_NAME],[PHONE]
FROM vw_PAST_DUE_LESSONS_EZPAY]' ,
@subject = 'EZ Pay'

fetch next from t_ezpay
into @email

END

close t_ezpay
deallocate t_ezpay

drop table #ezpayloc

now what i need it to do is with each time the query fetches i need it to take the results and email it to the location specific to it

for ex if it runs
location cutomer
0011 1234
0011 1251
0011 4250
0012 1506
0012 1508

so when it send an email to location 0011, location 0011 should only get their info as it the first 3 results

does that make sense?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-07-20 : 14:39:13

You will need to build @query.

DECLARE @query = varchar(1000)

SET @query = 'SELECT [LOCATION],[CUSTOMER],[ACCOUNT],[CUSTOMER_NAME],[PHONE]
FROM vw_PAST_DUE_LESSONS_EZPAY] WHERE '...

Then use @query = @query in sp_send_dbmail.

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

Subscribe to my blog

dtrivedi
Posting Yak Master

153 Posts

Posted - 2010-07-20 : 15:09:23

thanks alot!

dtrivedi
Posting Yak Master

153 Posts

Posted - 2010-07-20 : 15:17:17

where does the set @query command go...im getting this error

Msg 22050, Level 16, State 1, Line 0
Error formatting query, probably invalid parameters
Msg 14661, Level 16, State 1, Procedure sp_send_dbmail, Line 504
Query execution failed: Msg 137, Level 15, State 2, Server DWH01, Line 2
Must declare the scalar variable "@locs".

dtrivedi
Posting Yak Master

153 Posts

Posted - 2010-07-20 : 15:18:15

THIS IS WHAT IT LOOKS LIKE RIGHT NOW!

use ANALYTICS

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

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

select * from #ezpayloc

SELECT distinct [LOCATION], +('mac'+(location)+'@maclocal.com')
FROM vw_PAST_DUE_LESSONS_EZPAY] order by location
GO

DECLARE @locs varchar(max)
DECLARE @query varchar(1000)
declare @email varchar(max)

declare t_ezpay
cursor for select storeemail from #ezpayloc
declare t_ezpay
cursor for select locations from #ezpayloc
open t_ezpay

set @query='SELECT [LOCATION],[CUSTOMER],[ACCOUNT],[CUSTOMER_NAME],[PHONE]
FROM vw_PAST_DUE_LESSONS_EZPAY] where location=@locs'

fetch next from t_ezpay into @email

fetch next from t_ezpay into @locs

while @@FETCH_STATUS = 0
BEGIN

Print 'Processing: ' + @email

EXEC msdb.dbo.sp_send_dbmail
@recipients = 'dtrivedi@musicarts.com',
@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 balance to the new card.',
@query=@query,
@subject = 'EZ Pay'

fetch next from t_ezpay
into @email

END

close t_ezpay
deallocate t_ezpay

drop table #ezpayloc

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-07-20 : 16:38:48

I can't figure out your code, otherwise I would provide better help. Your cursor is jacked. You can't declare it twice. You should be doing it once and putting both columns into both variables at the same time. Please check out cursor examples in BOL. Once we get that part squared away, we'll work on the other issues with your code.

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

Subscribe to my blog

dtrivedi
Posting Yak Master

153 Posts

Posted - 2010-07-20 : 16:49:10

ok how's this

use ANALYTICS

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

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

select * from #ezpayloc

SELECT distinct [LOCATION], +('mac'+(location)+'@maclocal.com')
FROM [ANALYTICS].[dbo].[vw_PAST_DUE_LESSONS_EZPAY] order by location
GO

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

set @query='SELECT [LOCATION],[CUSTOMER],[ACCOUNT],[CUSTOMER_NAME],[PHONE]
FROM [ANALYTICS].[dbo].[vw_PAST_DUE_LESSONS_EZPAY] where location=@locs'

fetch next from t_ezpay into @email

fetch next from t_ezpay into @locs

while @@FETCH_STATUS = 0
BEGIN

Print 'Processing: ' + @email

EXEC msdb.dbo.sp_send_dbmail
@recipients = 'dtrivedi@musicarts.com',
@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 balance to the new card.',
@query=@query,
@subject = 'EZ Pay'

fetch next from t_ezpay
into @email
END

close t_ezpay
deallocate t_ezpay

drop table #ezpayloc

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-07-20 : 16:53:21

Try like this:


use ANALYTICS

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

insert into #ezpayloc SELECT distinct [LOCATION], +('mac'+(location)+'@maclocal.com')
FROM [ANALYTICS].[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 @query='SELECT [LOCATION],[CUSTOMER],[ACCOUNT],[CUSTOMER_NAME],[PHONE] 
FROM [ANALYTICS].[dbo].[vw_PAST_DUE_LESSONS_EZPAY] where location= ' + @locs


EXEC msdb.dbo.sp_send_dbmail
@recipients = 'dtrivedi@musicarts.com',
@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 balance to the new card.',
@query=@query,
@subject = 'EZ Pay' 


fetch next from t_ezpay
into @email, @locs
END

close t_ezpay
deallocate t_ezpay

drop table #ezpayloc

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

Subscribe to my blog

dtrivedi
Posting Yak Master

153 Posts

Posted - 2010-07-21 : 08:23:13

thank you so much that worked perfect. i actually almost had it :)

now when its send the emails the email looks like this

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 balance to the new card.
LOCATION CUSTOMER ACCOUNT CUSTOMER_NAME PHONE
---------- --------------- --------------- ------------------------------ --------------------
7340 7340A0003935 5AD07 DANA BAKER 6317154814
7340 734000A04385 5ER22 RAY MAY 6314511250

(2 rows affected)

NOW IS THERE A WAY TO PUT MORE SPACE BETWEEN THE QUERY RESULT AND THE BODY OF THE EMAIL IT SELF
ALSO IS THERE A WAY TO ELIMINATE ON THE BOTTOM WHERE IT SAYS (2 ROWS AFFECTED)??

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-07-21 : 13:38:09

If you want to format the email, then you are going to need to look into the HTML formatting for Database Mail. Check sp_send_dbmail in SQL Server Books Online for details. BOL even has an example.

I prefer to use bcp.exe to create the file just how I want it, and then I use sp_send_dbmail to attach my file. I use this method when I care about the formatting such as csv data. I use your method when I don't care about the formatting.

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

Subscribe to my blog

dtrivedi
Posting Yak Master

153 Posts

Posted - 2010-07-21 : 13:44:52

thanks tara it seems as though my boss doesn't want to use html but wants to have it format
can he have his cake and eat it too?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-07-21 : 13:54:40

Database Mail has very limited options. If your boss doesn't like what it provides, then you'll need to look into an alternative to Database Mail. There are other email options out there for SQL Server, they just aren't built into SQL Server like Database Mail is. You should google around to see what your options are.

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

Subscribe to my blog

dtrivedi
Posting Yak Master

153 Posts

Posted - 2010-07-21 : 14:18:42

hmph well thanks i will talk to him and see what he wants me to do
this is a career change for me and my 1st week on the job
i guess it only get harder n harder
thanks for ur help

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-07-21 : 14:30:21

Since you've started a new thread on this and you've got help from Rob there, I'm going to lock this topic.

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

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources