.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

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

Posted By:      Posted Date: April 10, 2011    Points: 0   Category :

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

View Complete Post

More Related Resource Links

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

SQL Server Agent Job And Database Mail - Error formatting query, probably invalid paramters


We have a stored procedure that we are trying to call with a SQL Server Agent Job. The stored procedure calls Database Mail. This is SQL 2008. The execute task is simply:


USE [Production]




When the job runs, it is erroring out immediately, with the error:

Execute as user: NT AUTHORITY\NETWORK SERVICE. Error formatting query, probably invalid paramters [SQLSTATE 42000] (Error 22050). The step failed.

When we go into SQL Server Manager and just execute the SQL query, it works though. Something within the SQL Server Agent job,  and Database Mail is causing problems. Any ideas? Thanks.



Messenger service is not in Windows 2008 but Sql Server 2008 is asking for it to run Database Mail


so how do you make Database Mail work in SQL Server 2008 without messenger service

Pat M


PatM Reno

SQL and Outlook: Enable Database Access and Updates Through Exchange and Any E-mail Client


Using Microsoft technologies, you can insert, edit, query, and delete database entries using any e-mail client such as Hotmail, Outlook, Yahoo, or even WAP phone. While e-mail is certainly a powerful and widely used tool, it is usually not integrated with an application for performing any tasks other than sending reminders. The application scenario described here, an e-mail-based SQL update program, uses a simple data model; however, this solution will apply to any data model that you are working with. It will also eliminate the need for complex n-tier Internet applications and serves as a low maintenance solution for providing data access.

Alok Mehta and Daniel Williams

MSDN Magazine January 2002

I cannot send mail using gmail smtp server


 Here is my code

Dim message As System.Net.Mail.MailMessage = New System.Net.Mail.MailMessage()
        message.To.Add("from textbox field")
        message.Subject = "Testing"
        message.From = New System.Net.Mail.MailAddress("sender email ")
        message.Body = "Just Testing"

        Dim smtp As System.Net.Mail.SmtpClient = New System.Net.Mail.SmtpClient()

        Dim client As SmtpClient = New SmtpClient()
        smtp.Credentials = New System.Net.NetworkCredential("username", "password")
        smtp.Port = 25
        smtp.Host = "smtp.gmail.com"
        smtp.EnableSsl = True
        smtp.DeliveryMethod = SmtpDeliveryMethod.Network
        lblmsg.Text = "Your Email has send"


Failure in restoring an database from tape - SQL Server 2005

Hello everyone, we are trying to restore one of our datebase from tape using the T-Sql commands. Using the GUI results in an timeout. The command we are using to start the restore is: RESTORE DATABASE RECOV FROM Tape = '\\.\tape0' WITH  FILE=2, RECOVERY, NOUNLOAD, STATS=5, MOVE 'xxx_OPL_xxxx_ZEELAND' TO 'K:\RECOVER\data\RECOV.mdf', MOVE 'xxx_OPL_xxxx_ZEELAND' TO 'K:\RECOVER\log\RECOV_log.ldf', REPLACE the restore proces start without any problem, only ending with this: Processed 2598808 pages for database 'xxx_OPL_xxxx_ZEELAND', file 'xxxx_PROD_Data' on file 2. Processed 3 pages for database 'xxxx_OPL_xxxx_ZEELAND', file 'xxxx_PROD_Log' on file 2. Msg 3229, Level 16, State 2, Line 1 Request for device '\\.\tape1' timed out. Msg 3213, Level 16, State 1, Line 1 Unable to unload one or more tapes. See the error log for details. Msg 3013, Level 16, State 1, Line 1 RESTORE DATABASE is terminating abnormally. In the eventlog, the following is recorded: BackupTapeFile::UnloadTape: PrepareTape(UNLOCK) failure on backup device '\\.\tape0'. Operating system error 21(The device is not ready.). For as far we can judge, the tapedrive is functioning without problems. Using the same tape in an other tapedrive on an different server results in the same error. Hardware used: HP Proliant Server (DL580) with additional SCSI interface card HP StorageWorks LTO 4 tapedriv

Modify Subject line of Notification e-mails sent by Database Mail

Hello All, I am using database mail for SQL Server Agent Job notification.  It is working just great. However the e-mails that I receive have a specific format of subject line as below. SQL Server Job System: 'TEST' completed on \\Server_Name I wanted to see if I can modify this to send e-mails with this subject line. SQL Server Job System: 'TEST' completed on <Server_Name> Is this possible?  If so can someone help me with the steps? Thanks.

My Sql server 2005 Database in recovery mode for 3 -4 hours every day

Hi , Some of our Sql Server 2005 databases remain in recovery mode after midnight for almost 3 to 4 hours. So when my application access them error "Databse in recovery" and for same database this happens again and again every night. Please suggest why and how this can be solved?

how to use sql database remotely(sql server 2005)

hi friends... when i want to connect my pc to sql server then the error comes........ A network-related or instance-specific error occurred while stabling a connection to sql server, The server was not found or was not accessible. Verify that the instance name is correct and that sql server is configured to allow remote connections,(provider: Named Pipes Provider,error:40-Could not open a connection to Sql server)(Microsoft Sql Server,Error:233)   so plz check it and give me advise how can i connect sql server remotely   thanks friends    

Can't restore database backup file in my database ? using sql server 2005. please help

VERY IMPORTANT i am trying to restore database.bak in sql server 2005 (i know the database.bak was also generated in sql 2005 server) i am trying to restore back up database .bak into the new database i just created in sql server 2005 i have saved my database .bak into c drive and when i select database .bak "From Device", it doesn't get populated in the list below and i see nothing and it keeps on prompting a message "You must select a restore source" Here's the screen shot:   PLEASE HELP..it's really important (i tried restoring database in sql server 2008 and it was sucessful but i am facing this problem in sql server 2005 only)  

Can SSAS 2005 run with Sql Server 2008 database engine?

Are there any known issues of running SSAS 2005 with SQL Server 2008 database engine?

SSIS 2005 - Send Mail Task - signature appended to email is garbled - unicode problem?

Hi, I'm pretty new to SSIS so go easy on me. I have a Send Mail Task to notify if a file cannot be imported - the mailbody is created on the previous step by a VB.NET script task to include the name of the file and the path it's been archived to. The problem I'm having is that while the body of the email I've created is displaying fine, our company's Exchange server appends a signature to all emails, and this is coming up as undisplayable characters, presumably due to some kind of unicode encoding problem. I've tried casting the email body in an expression to DT_STR (doesn't work as DT_STR "cannot be converted to a supported type" which seems a bit odd but never mind), DT_WSTR (garbled signature), DT_TEXT/DT_NTEXT (strange error on this one - "Attempted to read or write protected memory") none of those ideas worked, and I'm a bit stumped now. Can anyone help? I'm using SSIS 2005 with SP3

Database Mail configuring the maximum mail size (not attachment 'MaxFileSize')

Hi can anyone tell me if it's possible to configure the maximum size of an email when using database mail?  I know it's possible to use sysmail_configure_sp to set 'MaxFileSize', I want to prevent an email being sent the size of the body of the email is very large. In my case the body is based on a query result set inserted as html. Thanks      

Database Mail error

Hello I am new to SQL Server 2008. I am upgrading from 2000 to 2008 and currently working on Database Mail. I can send a test mail to say an email address like abc@xyz.com If i create an operator with the above email address, I am getting this error (in Database Mail log) when i send a test mail to Operator.  Message The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 3 (2010-09-07T10:31:24). Exception Message: Cannot send mails to mail server. (A recipient must be specified.). ) Any idea why Database Mail is able to send email when email address is specified and fails to send email to the same address when used in operator? Thank You, V

Uable to successfully install SQL Server 2005 (Express Edition) Service Pack 3 (KB955706)

After repeated attempts, I am unable to install the MSSQL Server 2005 (Express Edition) Service Pack 3 (KB955706).  From Live Update, the installation initializes but then reports that it was unable to install.  The first couple of times I received Error Code 0x65B, but that doesn't come up any longer. I have ACT! Database version 2007 (9.0) and it is unable to access the database to open it because it uses the MSSQL??  Also, there used to be an MSSQL icon below in my system tray, but that no longer appears.  When I reboot it will be on briefly, but then goes away.. MS Customer Support has attempted to help (Case ID SRX1135612820) but now tell me that this is beyond their expertise.  They had me try to reload MMSQL 2005 but no change. Please help! Thanks!!  Terry

database size limit about SQL Server 2005 Express

I know that there is a size limit (4 GB) for SQL Server 2005 Express, but I am not quite sure if the limit is applied for only ONE database data file or for ALL the database data, suppose I have two databases DB1 and DB2, so which of the following statement is correct? A. The size limit for DB1 is 4GB, and the size limit for DB2 is also 4GB, so if DB1 is 3G and DB2 is 3.5G,that will be OK. B. The total size for both DB1 and DB2 can not be larger than 4GB, which means if DB1 is 3G, then DB2 can not be large than 1G.
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