I wrote a procedure in Sql Server 2000 which sends an E-Mail to any E-Mail account. But I not able to send an attachment with the E-Mail. I dont have the code for attachment in the same procedure, as I searched on Google I not able to find out. Please help me for the same.
Waiting for your reply.
I'm trying to use the xp_sendmail and include a txt file as an attachment.
I can't get the procedure to work with the attachment.....any help would be greatly appreciated. this is what I have been trying to get to work.....Thanks!! Scott
exec master.dbo.xp_sendmail
@recipients = 'xyz@email.com',
@query = 'SELECT * FROM ape_pt_temp',
@subject = 'SQL Server Report',
@message = 'file attached',
@attach_results = 'true', @width = 250
Hi,
I am using the following stored procedure to send a mail with attachment. But the mail is sent without the attachment. Can anyone help me?
CREATE PROCEDURE DBO.sp_Send_Mail_test(
@p_From as nvarchar(50),
@p_To as nvarchar(50),
@p_Subject as nvarchar(255),
@p_Body as varchar(1000),
@p_CC as text = null,
@p_BCC as text = null,
@p_Attachment varchar(500)=null
)
AS
Declare @Message int
Declare @hr int
Declare @source varchar(255)
Declare @description varchar(500)
EXEC @hr = sp_OACreate 'CDO.Message', @Message OUT
EXEC @hr = sp_OASetProperty @Message, 'From',@p_From
EXEC @hr = sp_OASetProperty @Message, 'To', @p_To
EXEC @hr = sp_OASetProperty @Message, 'Subject', @p_Subject
EXEC @hr = sp_OASetProperty @Message, 'TextBody', @p_Body
EXEC @hr = sp_OAMethod @Message, 'CDO.Message.Attachment.Update', Default, @p_Attachment
If @p_CC is not null
BEGIN
EXEC @hr = sp_OASetProperty @Message, 'CC',@p_CC
END
If @p_BCC is not null
BEGIN
EXEC @hr = sp_OASetProperty @Message, 'BCC',@p_BCC
END
EXEC @hr = sp_OAMethod @Message, 'Send', NULL
EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description out
EXEC @hr = sp_OADestroy @Message
IF @hr <> 0
BEGIN
SELECT hr=convert(varbinary(4),@hr), Source=@source, Description=@description
RETURN
END
Regards,
Bharathram G
Hi,
I am able to send normal mails from SQL Server with no attachments.
But when i am trying to send mail with an attachment i get following error.
Server: Msg 18025, Level 16, State 1, Line 0
xp_sendmail: failed with mail error 0x80004005
I am running SQL Server 2000 , SP3.
select @@version
---------------------------------
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
exec master..xp_msver
------------------------------------------
Index Name Internal_Value Character_Value
------ -------------------------------- -------------- ------------------------------------------------------------------------------------------------------------------------
1 ProductName NULL Microsoft SQL Server
2 ProductVersion 524288 8.00.760
3 Language 1033 English (United States)
4 Platform NULL NT INTEL X86
5 Comments NULL NT INTEL X86
6 CompanyName NULL Microsoft Corporation
7 FileDescription NULL SQL Server Windows NT
8 FileVersion NULL 2000.080.0760.00
9 InternalName NULL SQLSERVR
10 LegalCopyright NULL © 1988-2003 Microsoft Corp. All rights reserved.
11 LegalTrademarks NULL Microsoft® is a registered trademark of Microsoft Corporation. Windows(TM) is a trademark of Microsoft Corporation
12 OriginalFilename NULL SQLSERVR.EXE
13 PrivateBuild NULL NULL
14 SpecialBuild 49807360 NULL
15 WindowsVersion 143851525 5.0 (2195)
16 ProcessorCount 1 1
17 ProcessorActiveMask 1 00000001
18 ProcessorType 586 PROCESSOR_INTEL_PENTIUM
19 PhysicalMemory 254 254 (266850304)
20 Product ID NULL NULL
(20 row(s) affected)
Any help would be helpful.
Regards
Jay
Dear Gurus
I have got a requirement.
I need to send mail with an attachment.
for eg.
An employee has requested leave a mail should go to the person he is reporting to as a mail and with this mail
an attachment of the leave request should be sent.
When the person who recevies mail he opens the attahcment and apporves the leave request or rejects a return mail to go to all concerned.
Hi All,
In my SSRS report there is a column with email field, when the user clicks on it -- it should open the outlook and in the attachment field there should be a screenshot of that report
i think anything i could do is only in the jump to url in the navigation tab......Please suggest me the solution or any other alternatives.
Thanks in advance for help
Warm Regards,
Chanduu.
I am able to use a variable filename in my send mail task as the message source when I specify variable as the message source type. But I would rather use the variable in the attachment e.g. @[User::filename].
When I do that, I get the message that the file doen't exist. How is it possible to specify a variable as the attachment?
Den
Ok i have a programmer who wants to send out the data from a table as an email attachment via sql mail without having to write a com component. Does any one know the procedures on how to do this (i.e stored procedures and triggers ect and anything else i need or should know).
Please dont answer this unless you have some genuine help as I dont want to post this every time someone comes up with a smart ass comment!
Thanks guys
I have a C# program that sends an e-mail with an Excel attachment. The Excel file is populated with the results of a SQL stored procedure before the e-mail program is executed. The entire process is in a DTS package (populate a temporary table, export the table to Excel, then send the e-mail). It is possible that the stored procedure will return no results, in which case the Excel file will not be populated.I'd like to know whether it is possible (and if so, how) to add some code to my e-mail program that will count the number of populated rows in the Excel file and if that number is <2 (the header row will always be populated), to quit and not send the e-mail.
An alternative would be for the DTS package to terminate if the stored procedure returns 0 records.
Thanks!
HI !
I need help to fix SSIS Send Mail Task Error.
I have a
Data Flow
Read from Sql Data base and then put the query result into excel file
(OLE DB Source --> Excel Destination)
Send Mail Task
Email excel file from the data flow
Control Flow :
Data Flow --> Send Mail Task
The package throws an error below. I tried to disable my antivirus because i thought it's throwing an error because of the antivirus.
But, it's still throwing the same error. If i tried to send email without attachment, it works just fine.
Is there anybody know how to fix this?
Progress: The SendMail task is initiated. - 0 percent complete
[Send Mail Task] Error: An error occurred with the following error message: "The operation has timed out.".
Progress: The SendMail task is completed. - 100 percent complete
Task Send Mail Task failed
I have below table in the snapshot, My task is to send the same output as a attachment in Excel to email. i used the below procedure
Â
EXEC [msdb]..sp_send_dbmailÂ
@profile_name='TSSSendMail',
@recipients='mymailID@gmail.com.com',
@subject='DB Mail',
@body='HI Team',
@execute_query_database = 'DbName',
@query = 'EXEC J16ReimbursementFortnightly1TO15 1',
@attach_query_result_as_file=1,
@query_attachment_filename = 'SummaryReimbursement.xls'
But I am not getting Proper Output, is seems like CSV Format, but i want it proper tabular format in Excel.
Hello All,
Hopefully someone out there will have an idea as this is driving me nuts.
I want to send a dynamic files in attachment files ny send mail task that file name has change follow datetime.
I try to use the expression but I can't use it.
please tell me for this problem.
Any suggestions appreciated,
Thanks.
I have to send mail with HTML format and attaching multiple files dynamically via send mail task.
View 10 Replies View RelatedI've set up DB mail and sent a test e-mail and that comes through fine.
I set up an Operator with email Name: DWhelpton@k-and-s.com;MWeaver@k-and-s.com
I created a job and set up the notifications to e-mail the operator on failure.
When the job runs and fails, I do not get an e-mail and I get the following exception in the db mail log:
Date 2/2/2007 8:35:00 AM
Log Database Mail (Database Mail Log)
Log ID 402
Process ID 3936
Last Modified 2/2/2007 8:35:00 AM
Last Modified By NT AUTHORITYSYSTEM
Message
1) Exception Information
===================
Exception Type: Microsoft.SqlServer.Management.SqlIMail.Server.Common.BaseException
Message: Could not retrieve item from the queue.
Data: System.Collections.ListDictionaryInternal
TargetSite: Microsoft.SqlServer.Management.SqlIMail.Server.Controller.ICommand CreateSendMailCommand(Microsoft.SqlServer.Management.SqlIMail.Server.DataAccess.DBSession)
HelpLink: NULL
Source: DatabaseMailEngine
StackTrace Information
===================
at Microsoft.SqlServer.Management.SqlIMail.Server.Controller.CommandFactory.CreateSendMailCommand(DBSession dbSession)
at Microsoft.SqlServer.Management.SqlIMail.Server.Controller.CommandFactory.CreateCommand(DBSession dbSession)
at Microsoft.SqlServer.Management.SqlIMail.Server.Controller.CommandRunner.Run(DBSession db)
at Microsoft.SqlServer.Management.SqlIMail.IMailProcess.ThreadCallBack.MailOperation(Object o)
What step am I missing?
<CODE>
<FONT face="Verdana, Arial, Helvetica" color=midnightblue size=2>For some reason
this proc doesn't send attachment. Please advise.
<P></P>
<P><PRE id=code><FONT id=code face=courier size=2></pre>
<P></P><P>CREATE PROCEDURE [dbo].[sp_SendCDONTSMail]<BR>@Help [BIT] = 0,<BR>@From [VARCHAR](8000) = NULL,<BR>@To [VARCHAR](8000) = NULL,<BR>@Cc [VARCHAR](8000) = NULL,<BR>@Bcc [VARCHAR](8000) = NULL,<BR>@Subject [VARCHAR](8000) = NULL,<BR>@Body [VARCHAR](8000) = NULL,<BR> @Filename [VARCHAR](8000) = NULL,<BR> @Importance int = 0,<BR>@MailFormat [BIT] = 0,<BR>@BodyFormat [BIT] = 0<BR>AS<BR>DECLARE<BR>@Error [VARCHAR](150),<BR>@object [INT],<BR>@hr [INT]</P><P>IF @Help = 1<BR>BEGIN<BR>PRINT '<BR>Purpose:<BR>This porcedure will send an email using CDONTS.dll.<BR>Use as a replacement to xp_sendmail. It will allow you<BR>to send HTML emails from SQL<BR>'<BR>RETURN 1<BR>END</P><P>IF ((@From IS NULL OR @From = '') OR (@To IS NULL OR @To = '') OR (@Subject IS NULL OR @Subject = '') OR (@Body IS NULL OR @Body = '')) AND @Help = 0<BR>BEGIN<BR>SET @Error = 'sp_SendCDONTSMail requires parameters @From, @To, @Subject, and @Body.' + CHAR(13) + 'Please execute ''sp_SendCDONTSMail 1'' for syntax assistance.'<BR>RAISERROR(@Error, 16, 1)<BR>RETURN -1<BR>END</P><P>EXEC @hr = sp_OACreate 'CDONTS.NewMail', @object OUT<BR>EXEC @hr = sp_OASetProperty @object, 'From', @From<BR>EXEC @hr = sp_OASetProperty @object, 'To', @To<BR>EXEC @hr = sp_OASetProperty @object, 'CC', @Cc<BR>EXEC @hr = sp_OASetProperty @object, 'Bcc', @Bcc<BR>EXEC @hr = sp_OASetProperty @object, 'Subject', @Subject<BR>EXEC @hr = sp_OASetProperty @object, 'Body', @Body<BR>EXEC @hr = sp_OASetProperty @object, 'MailFormat', @MailFormat<BR>EXEC @hr = sp_OASetProperty @object, 'BodyFormat', @BodyFormat<BR>exec @hr = sp_OASetProperty @object, 'Importance', @Importance<BR>EXEC @hr = sp_OAMethod @object, 'AttachFile', @FileName <BR>EXEC @hr = sp_OAMethod @object, 'Send'<BR>EXEC @hr = sp_OADestroy @object</P></FONT></FONT>
</CODE>
I have a DTS package that runs 4x a day and generates an excel spreadsheet, renames that spreadsheet with a datetime stamp and then places it into a folder on our network. I have been asked to email that spreadsheet to someone everytime the package runs.
My question is what would be the best way to handle this and how do I ensure that whatever process I define grabs the correct file? The folder that the Excel file is being placed into has multiple files in it. Is there a way to tell SQL Server which file to grab and email?
Any thoughts or suggestions would be greatly appreciated! Thanks!
Frank
I am new to sql and want to write a stored procedure to email a database group an attachment of a report that was created. Can someone please point me in the write direction thanks.
View 4 Replies View RelatedI am using nt4.0 sp5 with sql7.0 sp3 and exchange client.
Does anyone know how to keep xp_sendamil form putting a copy of the mail message in the sent mail folder or an automated process for deleting all mail in the sent folder? As it stands now I must manually delete all messages from the sent mail folder on a daily basis.
Thanks,
Ed.
Lokendra writes "I have configured the Database mail profile and account in Sql Server 2005 but the mail is not sending and showing the following error message:
Error,235,The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2007-03-05T15:16:07). Exception Message: Cannot send mails to mail server. (Mailbox name not allowed. The server response was: Sorry<c/> that domain isn't in my list of allowed rcpthosts.).
),3000,90,,3/5/2007 3:16:07 PM,sa
but while in the same mail set up in previous instance of sql server 2005 the message was sending very well. After installing new instance of sql server 2005 the problem is arising.
Anybody can tell me that what I can do so that i can send mail using the SMTP databasemail account."
Dear all,
I have switched off the firewall settings on my system and as suggested im entering the minimal information and data to send the mail.
but still the Mail Task is failing..
plz suggest.
Hi Everyone,
Hopefully someone out there will have an idea as this is driving me nuts.
I've setup a task to email on success/failure and keep receiving the following message when executed:
Progress: The SendMail task is initiated. - 0 percent complete
[Send Mail Task] Error: An error occurred with the following error message: "Failure sending mail.".
Progress: The SendMail task is completed. - 100 percent complete
Task Send Mail Task failed
When I configure Outlook Express on the same machine with the same settings it works.
On the SMTP Connection Manager I have left the default name, tested with both an IP address and Server Name, and no authentication or SSL.
On the Send Mail Task, it uses the above connection. The To: , From: , Subject fields are populated. Message SourceType is DirectInput, MessageSource is Test, Priority is Normal and no attachments or expressions etc.
Nothing useful is logged in the Event Viewer even with full logging turned on.
Any suggestions appreciated,
Thanks.
Hi,
We have a DTS package in the old SS2000 that we are still using in SS2005. We change the old xp_sendmail to use the new sp_send_dbmail. This runs fine without attachment. But with attachment, I'm encountering an error when running the package.
The task reported failure on execution.
The client connection security context could not be impersonated. Attaching file requires an integrated client login.
Syntax error or access violation.
I cannot find much topic on the net about this error.
Thanks very much for any help.
Regards
Hi,
In SSIS Scipt task, with all variables assigned.
With the below code i was able to send mails, But after receiving it, there is no Attachment.
N.B:- Similar issue is raised by some other guy in
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2157275&SiteID=1
"http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2155181&SiteID=1"
and i dont think he got the resolution.
Public Sub Main()
Dim smtpServer As String = Dts.Variables("MailSMTPServer").Value.ToString
Dim htmlMessageTo As String = Dts.Variables("ToLine").Value.ToString
Dim htmlMessageFrom As String = Dts.Variables("FromLine").Value.ToString
Dim htmlMessageSubject As String = Dts.Variables("MailSubj").Value.ToString
Dim htmlMessageBody As String = Dts.Variables("MailBody").Value.ToString
Dim htmlMessageLog As String = Dts.Variables("ErrorLog2").Value.ToString
Dim htmlMessage As MailMessage
Dim mySmtpClient As SmtpClient
Dim m As New System.Net.Mail.MailMessage
Dim AttachLog As Attachment = New Attachment(htmlMessageLog)
htmlMessage = New MailMessage(htmlMessageFrom, htmlMessageTo, htmlMessageSubject, htmlMessageBody)
mySmtpClient = New SmtpClient("<SMTP Server name>")
m.Attachments.Add(AttachLog)
mySmtpClient.Credentials = New NetworkCredential("<emailid>", "<password>")
mySmtpClient.Send(htmlMessage)
Dts.TaskResult = Dts.Results.Success
End Sub
Please let me know, how can i see Attachment in my mail.
Thanks.
hi
I am encountering the same problem above and I did exacly as described . But it is not working.
I must send emails every month with dynamically named files as attachments.
The files are named according to the date on which they are generated.
For example on the first of November 2007, the file will be named myfile_1_11_2007.
I have created a variable called DynamicFileName with package scope, data type string and default value: d:\tests\
In "Send Mail Task Editor" Dialog Box, I have specified the following:
smtpConnection: smtptest.server.com
From :nemo@smtptest.server.com
To: nemo@smtptest.server.com
Subject: Dynamic File Email
MessageSourceType: Variable
MessageSource: blank
Priority: blank
Attachments: blank
In Expressions, I have specified:
FileAttachments: @[User:ynamicFileName] + "myfile_" + (DT_STR, 4, 1252) DAY( GETDATE() ) + "_" + (DT_STR, 4, 1252) MONTH( GETDATE() ) + "_" + (DT_STR, 4, 1252) YEAR ( GETDATE() ) + ".csv"
When I execute the package, I get the following errors:
-----------------------------------------------------------------------------------------
Error at Send Mail Task [Send Mail Task]: Either the file "d:\tests\myfile_1_7_2007.csv" does not exist or you do not have permissions to access the file.
Error at Send Mail Task: There were errors during task validation.
---------------------------------------------------------------------------------------------------
Of course, the file does not exist. It will exist at tun-time. How can I tell the Send Mail Task to use a filename that is dynamic ?
By the way, once I have specified the code for FileAttachments, on trying to edit the Send Mail Task Properties, I can see that the Atachments field has been set to "d: estsmyfile_1_7_2007.csv by itself: I never typed it there !! It seems that the task executes the code even before it is run. If I remove the attachment path manually, on running the dts, I get an error saying that "either the file does not exist or you do not have permission to access the file.
I would be most grateful if anyone could be of help
thanks
Hi all,
I create a subscription to send the report (in pdf format) to users once every week. The subscription works, all users manage to receive the email but some of them do not have the pdf file attached to the email. Anyone know what is wrong??
Daren
Hello
I'm using sql server 2005 express edition. I had so security problems that i got helps from this forum. Now i have another one :
In my system i'm using a database with sa password, it means to login and work with database structure and data user should enter sa password.
But when i deAttach db and transfer it to another computer, I can easily attach db to customers sql server without knowing sa password.
I want to say : Everybody can attach my db to their system without knowing sa password and without need to create user, I need security in customer side not at mine.
Please help me what's wrong? I know there is a solution that i don't know!
Thank you for helping me.
I hace an Execute SQL task which calls a sproc which contains the sp_send_mail system sproc to e-mail a set of query results . It is OK as long as the query results are small, but I get this error if they get too big: - File attachment or query results size exceeds allowable value of 1000000 bytes.
any way to change (and where to change) this value.
Dave
I have a ETL job that generate a text file ( with timestamp. ABCfile041208.txt).
and I would like to create a send email task to send this file as an attachement.
How can I do this? the file name is going to change everyday..
Can you show me some example?
Hiii,
I am delivering report in Excel format every Monday. I am also including link with that email.
sometime, it delivers excel sheet correct and sometime it just gives blank sheet in attachment and the strange thing is that We can see data through the included link, but not in excel attachment.
Any idea??
If any of you passed through this problem plzzz help me out...
Hello,
The Database Mail feature is already enabled on the server, also I have a mail account on the other server.
The problem I faced is that I need to send mail from my SQL Server using a created email account on the other server. How should I configure my email to do that ?
Should I use a sysmail_add_account procedure to enable account, also set profile and profile account ? Does this way creates server mail account that is binded with other email account?
The mail should be sent from my SQL Server.
Thank You.
Hi all....
Our company use yahoo business mail server for our corporate mails. I know that I can configure database mail with SMTP. But when I try to configure database mail account with yahoo bizmail, I cannot do that. It gets configured but when I test it it doesn't send any mails. Do I need to have any special condiguration for this. SMTP address is smtp.bizmail.yahoo.com. Also I have specified the Authentication using my user name and password. Please help
Thanks
Rajesh
I keep getting a generic "Error Sending Mail" error. For testing purposes I am just trying to send using my own email account. What goes in the SMTP Server box in the connection manager? I have tried:
A - the exchange server address (SOMETHING.us.company.com)
B - The SMTP properties I see when I look at the properties of my email address: (my.name@abcd.efgh.company.com)
C - Just the end portion of the SMTP properties: (abcd.efgh.company.com)
D - My email address (my.name@company.com)
I don't know what to enter, or what is giving me such a generic error message.
Hello
I have got a script which gives the mail to the dba mail box when database backup fails.
In the script I want to make a change so that I get the particular database name , on what ever database i implement.
Can you tell me some suggestions.
The script I am using is :
use master
go
alter PROCEDURE dbo.SendMail
@to VARCHAR(255),
@subject VARCHAR(255),
@message VARCHAR(8000)
AS
BEGIN
SET NOCOUNT ON;
DECLARE
@rv INT,
@from VARCHAR(64),
@server VARCHAR(255);
SELECT
@from = 'testsql2000@is.depaul.edu',
@server = 'smtp.depaul.edu';
select @message = @message + char(13) + Char(13) + @@servername + '-'+ db_name()+ '-' + 'Backup Status Failed' + Char(13)
EXEC @rv = dbo.xp_smtp_sendmail
@to = @to,
@from = @from,
@message = @message,
@subject = @subject,
@server = @server;
END
GO
--- After the above script is run the following should be given in the 2nd step when
--- the backup jobs are scheduled ------
exec master.dbo.sendmail
@to = 'dvaddi@depaul.edu',
@subject =' Test sqlserver 2000',
@message = '' ;
Thanks