Solved

sending a temp variable value in email...

Posted on 2013-10-22
5
244 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
  • 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
In this article I will describe the Backup & Restore 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 an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

821 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