Solved

sending a temp variable value in email...

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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

Suggested Solutions

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

895 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now