Solved

sp_send_dbmail  @body column

Posted on 2013-10-30
9
540 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
 
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
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.

 
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

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

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
In this article I will describe the Detach & Attach 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.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

863 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