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

SQL Server Administration (2008)

email questions

Author

Topic

dtrivedi
Posting Yak Master

153 Posts

Posted - 2010-07-21 : 12:21:02

below is the output!!

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)

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)??

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-07-21 : 12:30:53

Add SET NOCOUNT ON to the top of the query you're passing to sp_send_dbmail. You can add extra line breaks to the end of the body to add space.

2 observations:

1. While this is a different problem than what you started with: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=147548
I don't think it warrants a new thread. You didn't include any detail in this thread that you're using Database Mail and sending a query.

2. STOP USING ALL CAPS IN YOUR REQUESTS. IT'S ANNOYING AND MAKES IT HARDER TO READ.

dtrivedi
Posting Yak Master

153 Posts

Posted - 2010-07-21 : 13:25:13

Hi Rob

sorry caps was an oversight

i started a new thred because no one replied on the other one
also i tried to add line breaks by '<br>' but that didn't work
now what?

dtrivedi
Posting Yak Master

153 Posts

Posted - 2010-07-21 : 13:35:00

hi rob
here is the script where should non set non count on go?

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 NOCOUNT ON
set @query=
'SELECT [LOCATION],[CUSTOMER],[ACCOUNT],[CUSTOMER_NAME],[PHONE]
FROM [ANALYTICS].[dbo].[vw_PAST_DUE_LESSONS_EZPAY] where location= ' + @locs

SET NOCOUNT ON

EXEC msdb.dbo.sp_send_dbmail
@recipients = 'dtrivedi@m123.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

robvolk
Most Valuable Yak

15732 Posts

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

quote:
i started a new thred because no one replied on the other one

That's not a polite practice. You may notice most of your threads were locked as duplicates. As I mentioned earlier it's better to keep it in a single thread so that the history and details are maintained.

quote:
here is the script where should non set non count on go?

It belongs in the @query parameter:

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

dtrivedi
Posting Yak Master

153 Posts

Posted - 2010-07-21 : 14:29:13

Hi Rob

Thanks alot!
I will be more careful in the future
I am new at this
I just did a carrer change just the first week on the job.

dtrivedi
Posting Yak Master

153 Posts

Posted - 2010-07-21 : 16:12:28

ok so next part of this i need it display the emails that it's going to in the subject line i did + @email after 'EZPay" subject but am getting a syntax error

dtrivedi
Posting Yak Master

153 Posts

Posted - 2010-07-21 : 16:44:56

i figured it out

nevermind

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources