.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
david stephan
Gaurav Pal
Post New Web Links

using sp_send_dbmail

Posted By:      Posted Date: September 01, 2010    Points: 0   Category :Sql Server
When I execute sp_send_dbmail, SQL Server returns error message: Msg 14624, Level 16, State 1, Procedure sp_send_dbmail, Line 242. At least one of the following parameters must be specified. "@body, @query, @file_attachments, @subject". I have setup and configured database mail to send email. I use our exchange server to send emails. This is working fine. Here is the test script I am using: DECLARE   @profile_name char(100); DECLARE @recipients char(100); DECLARE   @subject char(250); Declare   @body char (250); SET   @profile_name = 'Email Notification'; SET   @recipients = 'name'@company.com'; SET   @subject = 'testing sp_send_dbmail'; SET   @body = 'this is the body of the message.';  I appreciate if somebody could explain why I am getting the error message. How do I fix this? Thanks in advance for your help. EXEC   msdb.dbo.sp_send_dbmail @profile_name, @recipients, @subject, @body

View Complete Post

More Related Resource Links

getting a email delivert status when sending email using msdb.dbo.sp_send_dbmail


Hi All,

Can some help me regarding following.

I have set a job in sql server 2008 to send reminder emails for product expiration.
Now I want a report which will specify the list of the emails sent with the email status details like queued ,sent,delivered, failed.

I can fetch the emails that are sent by querying the like following

select * from sysmail_mailitems SM inner join sysmail_profile SP on SM.Profile_Id = SP.Profile_Id
and SP.Name ='ReminderProfile'

The problem is only getting a perfect status of email whether it is delived or failed. How I can get those status ?
Please help.



Does user needs to be sysadmin on msdb dabase t send an email with attachment? I am asking this because we have an user user account which has sysadmin permission on msdb dabase which can execute email with attachment successfully. At the same time there is another user account which has just datbasemailuser permission on msd dabase which fails to send email with attachment. Can some one help? Thank you.  

how send mail with out help of sp_send_dbmail in sql server 2005

Hi all, I want to sent mail using sp_send_dbmail but the hosing have problem as it require admin permission on master db so  they told me not use this SP is there any other method so that i can send the mail using SQL Server 2005  Thanks and Regards Er.Pradipta Nayak

tracking sp_send_dbmail usage


I am trying to get a better handle on what processes are using sp_send_dbmail on my SQL 2008 server.  I can use the following to identify when an email is sent and what was sent:


select * from sysmail_log order by log_date desc

select * from sysmail_mailitems


The former will give me the spid.  How can I resolve the spid to figure out machine host name, user name, application name for the given spid?



sp_send_dbmail dashes separator line


I need to have an automated process to generate and send out csv data on a set schedule. Using the sp_send_dbmail routine is working great except for one small problem I am having trouble getting around.

I am specifying a query to be run and including the results as a file attachment. The customer wants a csv file with column names.

The attachment always includes a 'separator line' of dashes between the column header and the actual data. Is there any way to eliminate this line of dashes? When I use @query_result_header = 0 flag, it removes the line of dashes, but the column names are also removed as well.

I want to keep the column names, but just remove the line of dashes. Any ideas?


Problems using sp_send_dbmail stored procedure


I want to send an email if my stored procedure fails on any one of the 6 table inserts. The email is not working.

After each insert, I include a check for errors. If errors are found, the program breaks out of the cursor loop and does a GOTO to a Fail block.

This is the code snippet:

      BEGIN --begin Fail
  --#1 - send email to itsystems
  IF ( @err <> 0 ) -- Error found in one of the inserts
    --SET @recipients1 =
    SET @recipients1 =
    SET @subject1 = 'COOP DATABASE STORED PROCEDURE FAILURE -- Project Number' -- "' + RTRIM(LTRIM(@ProjectName)) + '"' ;
    SET @body1 = 'COOP stored procedure uspCorporateAdSubmitClaims failed.'
    --SET @body1 = 'COOP stored procedure uspCorporateAdSubmitClaims failed for project number '

+ LTRIM(RTRIM(@ProjectNumber)) + '.'  
         + CHAR(13) + CHAR(10) + 'No claims were created for project numb

SP_Send_DBMail Causes Error When Tracking CDC Changes


I wrote a server trigger that tracks DML changes to a database and sends me an email of the changes made. The problem is the when we do CDC related tasks, it blows up and returns an error saying I don't have permissions to execute the sp_send_dbmail. I execute the proc without any problem. I am using the fully qualified 3 partname and have sysadmin permissions on the instance. I've even tried executing as the sa account and it came back saying the sa account didn't have permissions to execute the procedure. Here are the errors I get:

Could not update the metadata that indicates table [SchemaName].[TableName] is enabled for Change Data Capture. The failure occurred when executing the command 'sp_cdc_create_change_table'. The error returned was 15517: 'Cannot execute as the database principal because the principal "dbo" does not exist, this type of principal cannot be impersonated, or you do not have permission.'. Use the action 

HELP! sp_send_dbmail error


I'm having trouble sending an email through DBMail.  I'm logged in a a SQL Login user named "ServiceUser".  I have added this user to the msdb database and as a rolemember to "DatabaseMailUserRole"



USE [msdb]




USE [msdb]


EXEC sp_addrolemember N'DatabaseMailUserRole', N'ServiceUser'



When I try to send the email

sp_send_dbmail Indicates Mail Queued but no Message Received


This is running on SQL Server 2008 R2 Enterprise 64 Bit Edition. In the following procedure, I am trying to pull elements of an email message from a table called EventNotice, and use those values when executing sp_send_dbmail:

 @EventNoticeID Int,
 @Profile_NameLocal varchar(10),
 @SubjectTextLocal varchar(200),
 @BodyLocal nvarchar(MAX),
 @Body_FormatLocal varchar(50),
 @ToLocal nvarchar(500),
 @PackageNameLocal varchar(100),
 @MachineNameLocal varchar(100)
 SET @PackageNameLocal = 'CreatePositivePay'
 SET @MachineNameLocal = 'NewMachine'

  SET @EventNoticeID = 3
   @Profile_NameLocal = Profile_Name,
   @SubjectTextLocal = @PackageNameLocal + ' executed on ' + @MachineNameLocal + '. ' + SubjectText,
   @BodyLocal  = BodyText,
   @ToLocal  = ToRecipient,
   @Body_FormatLocal = Body_Format

  FROM dbo.EventNotice
  WHERE EventNoticeID = @EventNoticeID
  EXEC msdb.dbo.sp_send_dbmail
   @profile_name = @Profile_NameLocal,

Help Identifying What Wrong W/ Query Syntax - sp_send_dbmail



I know that I have probably done more than a few incorrect things in this statement but it works when I run it in the studio so I am not sure why it will not work when trying to use it from within sp_send_dbmail. I am pretty much new and learning as I go but this one has me lost.

My Error:

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

Here is what I am trying to run from Studio to validate that the syntax is correct before attempting to set it as a scheduled task.

DECLARE @like1 varchar(100);
SET @like1 = '%-LOST-%'; 
DECLARE @like2 varchar(100);
SET @like2 = 'LOST%-'; 
SET @sql = (SELECT ProfileName, LoginUser, ProfileDate, Filename, Product, Version, StartUp, Inuse, ServiceStatus, ServiceStartType, LastUsedTime, Directory
	  FROM computers c left outer join software s on c.id=s.id and filename='Flash10e.ocx' 
	  WHERE ProfileDate > GetDate()-1 
	  and (ProfileName not like @like1 and ProfileName not like @like2)
	  and version <> '10,1,82,76'
	  SELECT ProfileName, LoginUser, ProfileDate, Filename, Product, Version, StartUp, Inuse, ServiceStatus, ServiceStartType, LastUsedTime, Directory
	  FROM computers c left outer join software s on c.id=s.id and filename='Flash10i.ocx' 
	  WHERE ProfileDate > GetDate()-1

Passing a variable in @attachment parameter of sp_send_dbmail..




@sql varchar(100)



@TodayDate as varchar(40

Using sp_send_dbmail , bcc recipient is getting duplicate emails


Hi There,

             i am using sql server 2008 r2 , when i use sp_send_dbmail  , the blind copied recipient receives two emails if there are more than 1 recipient , can any one let me know how we can fix this.

Thank you.

sp_send_dbmail can't attach files larger than 2MB


I use [sp_send_dbmail] to send a query result as an xml attachment. When I first started using sp_send_dbmail, my attachments were small XML files and they were delivered correctly and I was happy with it; those files were like 1MB in size.

However, it wasn't that easy at first too. sp_send_dbmail takes lots of arguments and one of them is query_result_width which is a number between 10 and 32767. When you use sp_send_dbmail to send xml attachments, it removes the CR/LF characters at the end of xml lines and turn the whole xml attachment into a single line file; then it will cut the line into 32767 character lines and it doesn't care where is it going to cut the line and you'll end up with a corrupt xml attachment which needs to be edited manually before it gets open in the Microsoft Excel...

I solved that problem by casting the resulting xml into nvarchar(max) and replacing "/>" with "/>CRLF" inside that nvarchar string to avoid those nasty line-breaks.

Now I have another problem. sp_send_dbmail will truncate my xml attachments in exactly 2,097,156 bytes...

I think it's originating from that nvarchar(max) casting. Does anyone know anything of a workaround to this?

This is what I use to send emails:

DECLARE @MailProfile	sysname;

Only some e-mail gets sent successfully from sp_send_dbmail (Database Mail) on SQL Server 2005


Hi, this is my first attempt on this forum to find an answer to what is happening when I use sp_send_dbmail on SQL Server 2005. I have been googeling but without success and I don't know really where to turn now.

This is the scenario:

I have set up a mail function on our SQL server. I have the e-mail addresses in one table and the content in another table. For each address I find in my table, I use the sp_send_dbmail Stored Procedure (SP) to compile a new message to the address.

When I later execute my own SP that does the above (takes the email and compiles a new message to the recipient), DatabaseMail90.exe starts on the server and the e-mails are being sent out. But this is where it gets tricky, because some of the mail are being distributed directly, probably because it is on the same domain and network. The complicated thing is that most - not all - external mail gets an error in the msdb.dbo.sysmail_mailitems table. But the confusion does not stop here. Some of the e-mails are being delivered even though it has a reported error in the msdb.dbo.sysmail_mailitems table.

This is the query I use to view sent messages status:

 sent_status, --0 new not sent

sp_send_dbmail fails when @query parameter and OLE Automation



I'm using OLE Automation to build disk drive information into a table, and then using sp_send_dbmail with the @query to do a select from the table and include the information in an email. 

Both parts run fine on their own, but when I run them together, without a GO between them, then I get the following failure:

Msg 22050, Level 16, State 1, Line 0
Error formatting query, probably invalid parameters
Msg 14661, Level 16, State 1, Procedure sp_send_dbmail, Line 504
Query execution failed: Error Intiailizing COM . CoInitialize failed with Hresult: 0x80010106


Previous forum discussion has suggested doing the 2 parts in different threads as a workaround, so I have created seperate stored procedures for each part.  But still no joy.  When I run without a GO it still gives the error.

For example,

exec [dbo].[sp_get_diskspace]

exec [dbo].[sp_dbmail_send_dm]

fails.... but:

exec [dbo].[sp_get_diskspace]


exec [dbo].[sp_dbmail_send_dm]


Since I want to run the above from a stored procedure I can't have the GO in that position.

Any workaround suggestions?

Here's an extract from the procs (procs were taken from other forum appends):

proc 1:


ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend