Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

sending a temp variable value in email...

Posted on 2013-10-22
5
Medium Priority
?
250 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 2000 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 2000 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’s Wrong with Your Cloud Strategy ?

Even as many CIOs are embracing a cloud-first strategy, the reality is that moving to the cloud is a lengthy process and the end-state is likely to be a blend of multiple clouds—public and private. Learn why multicloud solutions matter in this webinar by Nimble Storage.

Question has a verified solution.

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

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…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…

636 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