Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

sp_send_dbmail  @body column

Posted on 2013-10-30
9
Medium Priority
?
614 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 400 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 40

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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
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 40

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 40

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 40

Accepted Solution

by:
lcohan earned 1600 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 Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

In this article I will describe the Backup & Restore 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.
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.
Loops Section Overview
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

580 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