?
Solved

sending a temp variable value in email...

Posted on 2013-10-22
5
Medium Priority
?
249 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

Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

Question has a verified solution.

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

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

770 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