Solved

sp_send_dbmail  @body column

Posted on 2013-10-30
9
535 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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
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

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
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…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

760 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

19 Experts available now in Live!

Get 1:1 Help Now