Databse mail sending blank mails

Question

Post reply

Databse mail sending blank mails

vihi.koyott

SSC-Addicted

Points: 483
More actions
April 8, 2010 at 3:51 am

#233491

Hi,
I have a 2 step SQL Server job.
Both these steps send DB mail (basically the query o/p) on successful completion.
Now the problem is that the particular job was running smoothly till yesterday when suddenly,the DB mail started sending blank emails in step-1(query output is shown as blank) while its working fine for step-2.
Tried restarting the SQL Server Agent and also the DB mail but no results !!!
Dunno whats the problem as there was no change in the code or anything .
I'm cluless......Pls help !!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

you might be appending a null to the variable being used for the @body element;

ie SET @body = @firstname + ' ' + @lastname + ' a bunch of info'

if @firstname or @lastname are null, @body will become null; check that first. you might need to wrap stuff with the ISNULL function to fix it.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

vihi.koyott SSC-Addicted Points: 483 More actions · Answer 2

Hi,

Thanks a lot for the reply.

I tried using the above solution given by you.

But still not working 🙁

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 3

Hi buddy, please post your code that sends out the database mail.. Without seeing your code how can we guess what the loop-hole is??

Go thro this following post to know and get better help from us :

FORUM POSTING ETIQUETTES - JEFF MODEN

[/url]

Cheers!!

vihi.koyott SSC-Addicted Points: 483 More actions · Answer 4

The same thing was running fine before.So i doubt its got anything to do with the code

Anyways here's the code

declare @mail_recepients varchar(max)

set @mail_recepients = 'vihitha.koyott@geodesic.com'

declare @profile sysname

set @profile = 'reporting'

declare @sql_query varchar(max)

set @sql_query = '

create table #temp

(

[Type] [varchar](35),

[TillDate] [int],

[OnDate] [int]

)

declare @till int

declare @on int

set @till=0

set @on=0

select @till=count(*) from SMS.SMS.table_UserCredits

where convert(varchar,timeofsubscription,102) <= convert(varchar,dateadd(day,-1,getdate()),102)

select @on=count(*) from SMS.SMS.table_UserCredits

where convert(varchar,timeofsubscription,102) = convert(varchar,dateadd(day,-1,getdate()),102)

insert into #temp ([Type],[TillDate],[OnDate]) values (''Registrations'',isnull(@till,0),isnull(@on,0))

set @till=0

set @on=0

select @till=count(*) from SMS.SMS.table_UserCredits where username like ''91%''

and convert(varchar,timeofsubscription,102) <= convert(varchar,dateadd(day,-1,getdate()),102)

select @on=count(*) from SMS.SMS.table_UserCredits where username like ''91%''

and convert(varchar,timeofsubscription,102) = convert(varchar,dateadd(day,-1,getdate()),102)

insert into #temp ([Type],[TillDate],[OnDate]) values (''National_registrations'',isnull(@till,0),isnull(@on,0))

set @till=0

set @on=0

select @till=count(*) from SMS.SMS.table_UserCredits where username not like ''91%''

and convert(varchar,timeofsubscription,102) <= convert(varchar,dateadd(day,-1,getdate()),102)

select @on=count(*) from SMS.SMS.table_UserCredits where

username not like ''91%'' and

convert(varchar,timeofsubscription,102) = convert(varchar,dateadd(day,-1,getdate()),102)

insert into #temp ([Type],[TillDate],[OnDate]) values (''International_registration'',isnull(@till,0),isnull(@on,0))

set @till=0

set @on=0

select @on=count(*) from SMS.SMS.table_UserCredits where isreg=1 and

convert(varchar,timeofsubscription,102) = convert(varchar,dateadd(day,-1,getdate()),102)

insert into #temp ([Type],[TillDate],[OnDate]) values (''Activation'',-1,isnull(@on,0))

set @till=0

set @on=0

select @on=count(*) from SMS.SMS.table_UserCredits where isreg=1

and username like ''91%''

and convert(varchar,timeofsubscription,102) = convert(varchar,dateadd(day,-1,getdate()),102)

insert into #temp ([Type],[TillDate],[OnDate]) values (''National Activvation'',-1,isnull(@on,0))

set @till=0

set @on=0

select @on=count(*) from SMS.SMS.table_UserCredits where isreg=1

and username not like ''91%''

and convert(varchar,timeofsubscription,102) = convert(varchar,dateadd(day,-1,getdate()),102)

insert into #temp ([Type],[TillDate],[OnDate]) values (''International Activation'',-1,isnull(@on,0))

set @till=0

set @on=0

select @on=count(distinct username) from SMS.SMS.record where

datentime >= convert(varchar,dateadd(day,-1,getdate()),102) and datentime < convert(varchar,dateadd(day,0,getdate()),102)

and username not like ''[ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz]%'' and username like ''91%''

insert into #temp ([Type],[TillDate],[OnDate]) values (''National Active'',-1,isnull(@on,0))

set @till=0

set @on=0

select @on=count(distinct username) from sms.sms.record where

datentime >= convert(varchar,dateadd(day,-1,getdate()),102) and datentime < convert(varchar,dateadd(day,0,getdate()),102)

and username not like ''[ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz]%'' and username not like ''91%''

insert into #temp ([Type],[TillDate],[OnDate]) values (''International Active'',-1,isnull(@on,0))

set @till=0

set @on=0

select @till=count(*) from SMS.SMS.Record where

username not in (''munduSMS'',''geodesic'')

and convert(varchar,datentime,102) <= convert(varchar,dateadd(day,-1,getdate()),102)

select @on=count(*) from SMS.SMS.Record where

username not in (''munduSMS'',''geodesic'')

and convert(varchar,datentime,102) = convert(varchar,dateadd(day,-1,getdate()),102)

insert into #temp ([Type],[TillDate],[OnDate]) values (''MessagesSent'',isnull(@till,0),isnull(@on,0))

set @till=0

set @on=0

------International msgs sent

select @on=count(*) from SMS.SMS.Record where username like ''91%'' and mobileno not like ''+91%''

and username not in (''munduSMS'',''ChAnDaMa'',''IndiaMRT'',''spyder'')

and ( datentime between ''2009-09-09'' and convert(varchar,dateadd(day,0,getdate()),102) )

select @till=count(*) from SMS.SMS.Record where username not like ''91%''

and username not in (''munduSMS'',''ChAnDaMa'',''IndiaMRT'',''spyder'')

and ( datentime between ''2009-09-09'' and convert(varchar,dateadd(day,0,getdate()),102) )

insert into #temp ([Type],[TillDate],[OnDate]) values (''International messages sent'',isnull(@on+@till,0),0)

set @till=0

set @on=0

select @on=count(*) from SMS.SMS.Record where username not like ''91%''

and username not in (''munduSMS'',''ChAnDaMa'',''IndiaMRT'',''spyder'')

and convert(varchar,datentime,102) = convert(varchar,dateadd(day,-1,getdate()),102)

select @till=count(*) from SMS.SMS.Record where username like ''91%'' and mobileno not like ''+91%''

and username not in (''munduSMS'',''ChAnDaMa'',''IndiaMRT'',''spyder'')

and convert(varchar,datentime,102) = convert(varchar,dateadd(day,-1,getdate()),102)

update #temp set Ondate = isnull(@on+@till,0) where Type = ''International messages sent''

set @till=0

set @on=0

-----------Downloads

select @till=count(*) from SMS.SMS.WAPDownload where

convert(varchar,datentime,102) <= convert(varchar,dateadd(day,-1,getdate()),102)

select @on=count(*) from SMS.SMS.WAPDownload where

convert(varchar,datentime,102) = convert(varchar,dateadd(day,-1,getdate()),102)

insert into #temp ([Type],[TillDate],[OnDate]) values (''Downloads'',isnull(@till,0),isnull(@on,0))

set @till=0

set @on=0

------ Purchase Till date

select @till=sum(Credit_Amount) from SMS.SMS.Purchase_History where status = ''Completed''

----- Purchase on date

select @on=sum(Credit_Amount) from SMS.SMS.Purchase_History where status = ''Completed'' and

convert(varchar,datetime,102) = convert(varchar,dateadd(day,-1,getdate()),102)

insert into #temp ([Type],[TillDate],[OnDate]) values (''Purchase'',isnull(@till,0),isnull(@on,0))

set @till=0

set @on=0

select [Type],[TillDate],[OnDate] from #temp

drop table #temp

go

'

EXEC msdb.dbo.sp_send_dbmail @profile_name=@profile,

@recipients = @mail_recepients,

@subject = 'Daily report',

@body= 'User details,till date/On date',

@query = @sql_query,

@query_result_width = 300

RBarryYoung SSC Guru Points: 143329 More actions · Answer 5

Add the command "EXEC(@sql_query);" right before your send_dbmail. Then make sure that the job step is logging all of its output to a table or file and run it again. Then check the Job Step output and let us know what it is...

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]