Solved

sp_send_dbmail  @body column

Posted on 2013-10-30
9
541 Views
Last Modified: 2013-11-06
msdb says  @body should be nvarchar...
but running the below gives error..

Msg 103, Level 15, State 4, Line 4
The identifier that starts with 'Oct 25 2013  5:33AM # Starting up database 'master'. Oct 25 2013  5:33AM # Starting up database 'mssqlsystemresource'. Oct 25 201' is too long. Maximum length is 128.

if you delete a couple of characters, it will run fine.. how can we enforce the nvarchar (max) as suggested in
http://technet.microsoft.com/en-us/library/ms190307(v=sql.100).aspx

 EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'profilename',  
    @recipients = 'email@dot.com',
    @body = [Oct 25 2013  5:33AM # Starting up database 'master'. Oct 25 2013  5:33AM # Starting up database 'mssqlsystemresource'. Oct 25 201],  
    @subject = 'Error logged in server' ;
0
Comment
Question by:25112
  • 4
  • 4
9 Comments
 
LVL 9

Assisted Solution

by:Valliappan AN
Valliappan AN earned 100 total points
ID: 39612051
Did you enclose the body with quotes:

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'profilename',  
    @recipients = 'email@dot.com',
    @body = '[Oct 25 2013  5:33AM # Starting up database ''master''. Oct 25 2013  5:33AM # Starting up database ''mssqlsystemresource''. Oct 25 201]',  
    @subject = 'Error logged in server' ;
0
 
LVL 5

Author Comment

by:25112
ID: 39612383
Thanks- that is a valid point.....
 So, I used a
   SELECT @MSG = '''['+REPLACE(@MSG ,'''','''''')+']'''

   to get the below (2 variations)..

 no error.. but still I am not getting the emails delivered.. do you see any errors/issues with the below 2 text being passed?

1)
@body =  '[ Oct 25 2013  5:33AM # Starting up database ''master''. Oct 25 2013  5:33AM # Starting up database ''mssqlsystemresource''. Oct 25 2013  5:33AM # Starting up database ''model''. Oct 25 2013  5:33AM # Starting up database ''EOS''. Oct 25 2013  5:33AM # Starting up database ''msdb''. Oct 25 2013  5:33AM # Starting up database ''YATCodes''. Oct 25 2013  5:33AM # Starting up database ''FLOWIP''. Oct 25 2013  5:33AM # Starting up database ''FLOWIP1''. Oct 25 2013  5:33AM # Starting up database ''OTUDA''. Oct 25 2013  5:33AM # Starting up database ''VADAIA''. Oct 25 2013  5:33AM # Starting up database ''DURSIA''. Oct 25 2013  5:33AM # Starting up database ''CAT''. Oct 25 2013  5:33AM # Starting up database ''PEMS''. Oct 25 2013  5:33AM # Starting up database ''ASEP''. Oct 25 2013  5:33AM # Starting up database ''JEEVHIST''. Oct 25 2013  5:33AM # Starting up database ''ReportServer''. Oct 25 2013  5:33AM # Starting up database ''ReportServerTempDB''. Oct 25 2013  5:33AM # Starting up database ''IPPS''. Oct 25 2013  5:33AM # Starting up database ''aspnet''. Oct 25 2013  5:33AM # Starting up database ''integration''. Oct 25 2013  5:33AM # Starting up database ''tempdb''. Oct 25 2013  5:33AM # Starting up database ''SEPA''. Oct 25 2013  5:33AM # Starting up database ''SEPATemp''. Oct 25 2013  5:33AM # Starting up database ''Datae''. Oct 25 2013  5:33AM # Starting up database ''DSP''. Oct 25 2013  5:33AM # Starting up database ''ATGLOW''. Oct 25 2013  5:33AM # Starting up database ''PUGALVOM''. Oct 25 2013  5:34AM # Starting up database ''ENRAZA''. Oct 25 2013  5:34AM # Starting up database ''KODUTHAR''.]',  

2)
@body =  'Oct 25 2013  5:33AM # Starting up database ''master''. Oct 25 2013  5:33AM # Starting up database ''mssqlsystemresource''. Oct 25 2013  5:33AM # Starting up database ''model''. Oct 25 2013  5:33AM # Starting up database ''EOS''. Oct 25 2013  5:33AM # Starting up database ''msdb''. Oct 25 2013  5:33AM # Starting up database ''YATCodes''. Oct 25 2013  5:33AM # Starting up database ''FLOWIP''. Oct 25 2013  5:33AM # Starting up database ''FLOWIP1''. Oct 25 2013  5:33AM # Starting up database ''OTUDA''. Oct 25 2013  5:33AM # Starting up database ''VADAIA''. Oct 25 2013  5:33AM # Starting up database ''DURSIA''. Oct 25 2013  5:33AM # Starting up database ''CAT''. Oct 25 2013  5:33AM # Starting up database ''PEMS''. Oct 25 2013  5:33AM # Starting up database ''ASEP''. Oct 25 2013  5:33AM # Starting up database ''JEEVHIST''. Oct 25 2013  5:33AM # Starting up database ''ReportServer''. Oct 25 2013  5:33AM # Starting up database ''ReportServerTempDB''. Oct 25 2013  5:33AM # Starting up database ''IPPS''. Oct 25 2013  5:33AM # Starting up database ''aspnet''. Oct 25 2013  5:33AM # Starting up database ''integration''. Oct 25 2013  5:33AM # Starting up database ''tempdb''. Oct 25 2013  5:33AM # Starting up database ''SEPA''. Oct 25 2013  5:33AM # Starting up database ''SEPATemp''. Oct 25 2013  5:33AM # Starting up database ''Datae''. Oct 25 2013  5:33AM # Starting up database ''DSP''. Oct 25 2013  5:33AM # Starting up database ''ATGLOW''. Oct 25 2013  5:33AM # Starting up database ''PUGALVOM''. Oct 25 2013  5:34AM # Starting up database ''ENRAZA''. Oct 25 2013  5:34AM # Starting up database ''KODUTHAR''.',
0
 
LVL 39

Expert Comment

by:lcohan
ID: 39612407
I usually add a variable and build/fill that for body and also suggest to add the body type like below

declare @bodystr nvarchar(max)
set @bodystr = '[Oct 25 2013  5:33AM # Starting up database ''master''. Oct 25 2013  5:33AM # Starting up database ''mssqlsystemresource''. Oct 25 201]'

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'profilename',  
    @recipients = 'email@dot.com',
    @body = @bodystr,
    @body_format = 'HTML', --or TEXT
    @subject = 'Error logged in server' ;
0
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
LVL 5

Author Comment

by:25112
ID: 39612429
lcohan, that is what i intend to do... the below is how i am trying to get it.. any thoughts what i am doing wrong?

 
DECLARE @MSG NVARCHAR(4000)  
SET @MSG =''
   
    CREATE TABLE #Err
 (LogDate DATETIME, ProcessInfo NVARCHAR(50) ,[Text] NVARCHAR(1000))
 
 INSERT INTO #Err
EXEC master.dbo.xp_readerrorlog 0, 1, 'Starting', 'up'
 
  SELECT @MSG = @MSG + NCHAR(13)+ CAST(LogDate as nvarchar(50))+' # '+ Text FROM #Err
  SELECT @MSG = '''['+REPLACE(@MSG ,'''','''''')+']'''

    EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'profile',  
    @recipients = 'email',
    @body = @MSG,  
    @subject = 'Some logged Messages' ;
     
DROP TABLE #Err
0
 
LVL 39

Expert Comment

by:lcohan
ID: 39612633
Not sure what is your problem because I used the code you posted above, updated profile and recepients to my email addres and it works fine in my SQL 2005 SP3:

(7 row(s) affected)
Mail queued.
0
 
LVL 39

Expert Comment

by:lcohan
ID: 39612641
You could check sent/unsent mail like:

use msdb
go

select * from msdb..sysmail_unsentitems
go

select top 100 * from msdb..sysmail_sentitems s
order by s.sent_date desc
go


And try restart mail like:


exec msdb..sysmail_stop_sp;
GO
exec msdb..sysmail_start_sp;
GO
0
 
LVL 5

Author Comment

by:25112
ID: 39614074
I got something like

(29 row(s) affected)
Mail queued.

too

i did

exec msdb..sysmail_stop_sp;
GO
exec msdb..sysmail_start_sp;
GO
to make sure...


 msdb..sysmail_unsentitems
is empty for me..
and i can see the sent item logged in
msdb..sysmail_sentitems

so no error message, but i dont get an email...

but if i do another email to the same id with a plain text, it works fine:

    EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'profile',  
    @recipients = 'email id',
    @body = 'Good morning',  
    @subject = 'hi- test' ;
and i got the email quickly...

in sysmail_sentitems, what is the 'body_format' shown when you collect from the error log and sent an email to yourself? I see 'TEXT'.. but of course, I have not seen that email yet..

in the recipients field in sysmail_sentitems, i have confirmed that the email id is correct and same for both emails, the one send a parameter text (which i did not get yet) and the other sending a plain text (which i got)

is there any other place where you can see why the email may have been stopped from reaching me?
0
 
LVL 39

Accepted Solution

by:
lcohan earned 400 total points
ID: 39615120
In that case it could be some spam filter or....the mail maybe so big that exceeds max allowed in your database mail configuration (which I believe its 1MB by default) or some restriction on your mail account. That also means you may have some large number of errors in the log file and can you please run the query alone to see what you get?

Regardless of that - what you have here it's not a SQL Code issue or SQL sp_send_db,mail issue.


DECLARE @MSG NVARCHAR(4000)  
SET @MSG =''
   
    CREATE TABLE #Err
 (LogDate DATETIME, ProcessInfo NVARCHAR(50) ,[Text] NVARCHAR(1000))
 
 INSERT INTO #Err
EXEC master.dbo.xp_readerrorlog 0, 1, 'Starting', 'up'
SELECT @MSG = @MSG + NCHAR(13)+ CAST(LogDate as nvarchar(50))+' # '+ Text FROM #Err
SELECT @MSG = '''['+REPLACE(@MSG ,'''','''''')+']'''
PRINT (@MSG);
0
 
LVL 5

Author Comment

by:25112
ID: 39627462
thanks- the network team did some  checks and now it works.. so it was some restriction on your mail account..

thanks again!
0

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
kill process lock Sql server 9 54
Help with query 3 26
Oracle - Query link database loop 8 39
sql 2014,  lock limit 5 32
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
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.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…

803 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