Solved

sending a temp variable value in email...

Posted on 2013-10-22
5
247 Views
Last Modified: 2013-11-06
what may be causes of a variable not being able to sent out by database mail.. some variables- no problems.. but other times it says it sent/queued it.. but the email is not reached
0
Comment
Question by:25112
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 5

Author Comment

by:25112
ID: 39591526
the example i have it mind is below.. can you run it in your environment to test and see if it works?
--------------------
SET NOCOUNT ON
 USE tempdb
 
DECLARE @MSG NVARCHAR(4000)
SET @MSG =''
 
DECLARE @BeginTime datetime  
DECLARE @FinishTime datetime  

SELECT @BeginTime = create_date from sys.databases where name = 'tempdb'
SELECT @FinishTime =  GETDATE()  

      CREATE TABLE #ErrLog
 (LogDate DATETIME, ProcessInfo NVARCHAR(50) ,[Text] NVARCHAR(1000))
 
 INSERT INTO #ErrLog

EXEC master.dbo.xp_readerrorlog 0, 1, 'Starting', 'up', @BeginTime, @FinishTime , 'asc'
/*example to just get something from log for this case in point.*/
 
  SELECT @MSG = @MSG + NCHAR(13)+ CAST(LogDate as nvarchar(50))+' # '+ Text FROM #ErrLog

SELECT @MSG

  /*just to make sure it does bring some records back.*/

  /*if i put here 'SET @MSG ='1234'' THEN IT DOES SEND THE EMAIL as 1234.. but as such it is not sending anything  */
 


   IF @MSG IS NOT NULL
   BEGIN
       EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'profilename',  
    @recipients = 'email id',
    @body = @MSG,  
    @subject = 'Error logged example' ;
       END
ELSE
   PRINT 'No Error'
   
     DROP TABLE #ErrLog
0
 
LVL 51

Assisted Solution

by:Mark Wills
Mark Wills earned 500 total points
ID: 39593242
Well, if you are stringing together a message and if one of those is NULL then the whole lot is NULL

So, maybe there is an error in :

 SELECT @MSG = @MSG + NCHAR(13)+ CAST(LogDate as nvarchar(50))+' # '+ Text FROM #ErrLog


Maybe that should become something more like

SET @MSG = @msg + isnull( (select top 1 convert(varchar,logdate) + '#' + text from #Errlog), 'No errors')

and no, I have not tested so might need to double check.
0
 
LVL 5

Author Comment

by:25112
ID: 39597499
Mark-thanks for the input .. i agree that is a good practise, and i should add it.. but if you look closely that query will always return something.. that is why i am bewildered why it won't be sent by email..
------------------
please run the below.. (only part of the code from above... and you should always get the results back:
------------------


SET NOCOUNT ON
 USE tempdb
 
DECLARE @MSG NVARCHAR(4000)
SET @MSG =''
 
DECLARE @BeginTime datetime  
DECLARE @FinishTime datetime  

SELECT @BeginTime = create_date from sys.databases where name = 'tempdb'
SELECT @FinishTime =  GETDATE()  

      CREATE TABLE #ErrLog
 (LogDate DATETIME, ProcessInfo NVARCHAR(50) ,[Text] NVARCHAR(1000))
 
 INSERT INTO #ErrLog

EXEC master.dbo.xp_readerrorlog 0, 1, 'Starting', 'up', @BeginTime, @FinishTime , 'asc'
/*example to just get something from log for this case in point.*/
 
  SELECT @MSG = @MSG + NCHAR(13)+ CAST(LogDate as nvarchar(50))+' # '+ Text FROM #ErrLog

SELECT @MSG
0
 
LVL 51

Accepted Solution

by:
Mark Wills earned 500 total points
ID: 39598257
Yes I agree in essence.

But the only condition that exists that might prevent an email is:

"   IF @MSG IS NOT NULL "

and if you were to hard code the '1234' then it does get sent.

there are two possible conditions that might exists when building @MSG. If there is a NULL anywhere in the concatenation then the entire string ends up being NULL, or multiple rows that may error the concatenation (though the table doesn't support multi-row). There is a third (least likely) possibility that locking might have prevented retrieval.

on the light of it, yes, I agree it should work OK, but then there is the problem that occasionally things don't get sent.

So, let's also consider the possibility that nothing was returned in that (undocumented) procedure.

Now, the startup event gets logged before the create of tempdb, and I believe the tempdb would be the one of last things being "started" so the create date of tempdb might well be greater than any starting up messages...

Maybe we also need the params for the procedure to be more robust.

e,g,

SET NOCOUNT ON
 USE tempdb
  
DECLARE @MSG NVARCHAR(4000) 
SET @MSG =''
  
DECLARE @BeginTime datetime  
DECLARE @FinishTime datetime  

SELECT @BeginTime = dateadd(day,datediff(day,0,create_date),0) from sys.databases where name = 'tempdb'  -- essentially just the DATE part
SELECT @FinishTime =  GETDATE()  

-- SELECT @BeginTime, @FinishTime

IF object_id('tempdb..#errlog','U') is NULL           -- so we can rerun often 
   CREATE TABLE #ErrLog (LogDate DATETIME, ProcessInfo NVARCHAR(50) ,[Text] NVARCHAR(1000)) 
 
INSERT INTO #ErrLog 

EXEC xp_readerrorlog 0, 1, N'Starting', N'up', @BeginTime, @FinishTime , 'asc' 

/* search strings *should be* Nvarchar*/
/* example above to just get something from log for this case in point.*/
/* add in extra robustness for NULL handling */
 
SET @MSG = @MSG + isnull( (select top 1 NCHAR(13)+ CAST(LogDate as nvarchar(50))+' # '+ Text FROM #ErrLog),'No Rows') 

SELECT @MSG

Open in new window


so, let's put in those checks and controls that we can (and arguably should under the guise of best practise) to handle some possible exceptions that we know would stop an @MSG from being sent.
0
 
LVL 5

Author Comment

by:25112
ID: 39627644
i beefed up the code with enhanced parameters like you mentioned.. it worked.. still don't know what was lacking before..

thanks Mark
0

Featured Post

What Is Transaction Monitoring and who needs it?

Synthetic Transaction Monitoring that you need for the day to day, which ensures your business website keeps running optimally, and that there is no downtime to impact your customer experience.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question