Emailing An Attachment From DTS
Feb 7, 2006
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
View 1 Replies
ADVERTISEMENT
Mar 14, 2004
Hey,
I would like to set up a job to send mail out to clients on a semi-regular basis. Is there a way to mail directly from MS SQL.
or some application which I can set to interface with MS SQL and send these mails out.
I'm currently using asp.net, however, doing it through the web is very time consuming, I would much rather use some application to generate the mails on the server as it currently takes close to 20 min to send through the web interface (and that's only to 900 members, we are looking at having at least 2000 members in a few weeks). What am I looking for is something I can call perhaps with transact sql so I can run it through an ms sql server job, however any solution you have would be fantastic.
If you don't have specific details, that's fine, I'm not even sure what apps etc to look into, even a name of some technology to use (or does MS SQL have a way of doing all of this itself?)
Thanks a lot for your help, life saver ;-)
View 8 Replies
View Related
Apr 13, 2000
I need to send the output of a report generated, automatically through email as an attachment to some customer.Is it possible?Can anyone help?
View 1 Replies
View Related
Dec 8, 2004
Hi everyone,
I am running SQL Server Enterprise Manager and i would like to have a query run everynite and send the results returned by the query off to an email address....
How can i do this?
View 3 Replies
View Related
Nov 5, 2004
Hi everyone I have a database that I am creating for another department. He emails applications to potential vendors. And I was wanting sql to do this for him. I was reading about xtended procedures XP_Sendmail, but this is the first time I have created an extended stored procedure. I need for it to email an attachment (the report)
View 1 Replies
View Related
Feb 19, 2007
I'm sort of new to SSIS so I apologize if this is a trivial question:
I setup a package and want it to email me if there is any errors. Right now I got it working to send me a pre-written message about the failure, but I want to load the full task failure message into variable and email it on the email body.
But so far I have not found a way to save the error messages into variables so I can email them. Anyone can help with that one?
View 4 Replies
View Related
Apr 5, 2007
I need to export a view into excel and mail to my manager daily morning. Is there a way i can automate this process. I know how to convert view to excel but i need a better solution to automate this process with emailing to my mgr. Please suggest a better approach of doing this.
Thanks,
View 5 Replies
View Related
Jan 5, 2001
Whenever a certain stored procedure executes I need to email both admin and clients (different messages). I have created an error within the SP and can RAISE the error and thus notify admin staff but how can i email the client also ? I can obtain the cient email address from thye system but how can i email them ? Can i use xp_sendmail within a stored procedure ? If so, how as i can't seem to get it working ?
thanks for help
View 1 Replies
View Related
Oct 20, 2002
Hi there!
Is there a way, I can setup create a script that I can schedule, which runs through my user databases and if e.g.:
1:) there is less than 20% free space it emails me.
and/or
2:) there is less than 200MB free space
it emails me.
Cheers
Henrik Hansen
View 6 Replies
View Related
Oct 21, 2004
Is there a way to have SQL server email the results of a Select query to someone?
View 1 Replies
View Related
Jan 8, 2008
Hi,
Is there a built in capability with sql server 2005 which sends emails to users upon a record insertion.
Thanks
View 2 Replies
View Related
Aug 31, 2007
Hi ,
Is it possible for us to send an excel file generated in the package to the users using Sendmail task or anyother task using SSIS?
Any help is greatly appreciated.
Thanks,
SVGP.
View 5 Replies
View Related
Jun 28, 2006
Hello,
I am trying to use €œSQL Server Business Intelligence Development Studio€? (what is the short name for this) to complete a check and then possibly email me depending on what happens.
The check is around two tables that I have. There is a chance that they will become out of sync with each other and so I want to know when a field (ACCOUNTID) has a value that is in one table and not the other. I can do the check but I would like the package to email me when there is a problem (and if possible email me the details) and do nothing when everything is OK.
Can anybody give me some hints to point me in the correct direction as I just seem to be chasing my tail at the moment,
Thanks,
View 5 Replies
View Related
Oct 26, 2006
Hello!
The problem is this.
I have many short messages in a table. I need to tigh them together in one long email message and email to the users.
But xp_sendmail is limited to 7,790.
How could I send longer messages? Or how could I devide the long message and send it in parts (i.e. separate consequtive emails)?
This doesn't work, it still cuts the messages off at around 7,790:
E. Send messages longer than 7,990 characters
This example shows how to send a message longer than 7,990 characters. Because message is limited to the length of a varchar (less row overhead, as are all stored procedure parameters), this example writes the long message into a global temporary table consisting of a single text column. The contents of this temporary table are then sent in mail using the @query parameter. CREATE TABLE ##texttab (c1 text)
INSERT ##texttab values ('Put your long message here.')
DECLARE @cmd varchar(56)
SET @cmd = 'SELECT c1 FROM ##texttab'
EXEC master.dbo.xp_sendmail 'robertk',
@query = @cmd, @no_header= 'TRUE'
DROP TABLE ##texttab
View 7 Replies
View Related
Sep 12, 2007
Hi Rafael,
I need to create a new excel file daily and then need to mail it.
The problem iam facing is that ,when i schedule a job the task that dumps data into excel(Dataflow task ) gives a validation error.Everytime i need to manually link the sheets of the excel to the corresponding tables.
Any idea why this is happening?So iam unable to schedule as a job...
Thanks,
Vani.
View 3 Replies
View Related
Feb 4, 2008
Hi All
I'm hoping someone here will be able to guide me in the right direction. I am trying to figure out a way of looping through table columns or a flat file in which there will be three parameters every time. With these parameters I am then wanting to run a report using two of these parameters to feed into the report and then email this out to an email address which would be my third parameter. Does anyone here have any idea how I would go about doing this?
Many thanks in advance.
View 5 Replies
View Related
Jun 19, 2014
I have downloaded MS's performance dashboard report and installed. I wanted these report should automatically send the performance report to my email.
View 1 Replies
View Related
Apr 8, 2015
I am trying to create an alert when there are more than 2500 connections to our ailing SQL Server.However, for now, I need to restart the SQL server service because users begin complaining they can't connect.
1) I created an operator - me.
2) I created a job which runs a query.
INSERT INTO Sessions_alert
SELECT host_name, program_name, login_name, count(c.session_id ) num_sessions, getdate()
FROM sys.dm_exec_connections c JOIN sys.dm_exec_sessions s on c.session_id = s.session_id
GROUP BY host_name,program_name,login_name ORDER BY 4 DESC
3) I created an alert - included the job from above. Type performance condition alert. Object - SQL Server General Statistics. Counter - User connections.Alert if counter rises above 50. Just testing. I really want to know when it gets past 2500.
4) I've set the alert to email and delay is between responses 2 minutes.
It history tables says number of occurences is 18964. However, I don't receive an email.
Shouldn't the alert send an email? Do I need to include email code in the job?
View 6 Replies
View Related
Jul 10, 2007
I want to send a deployed report (report manager), through email, by calling reportingServices webservice. But the requirement is that, time is not fixed for sending this email, so I want to trigger the sending of report as a one time event, no subscription to a daily time...
How should I go about it?
View 1 Replies
View Related
Sep 19, 2001
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
View 1 Replies
View Related
Sep 24, 2001
<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>
View 1 Replies
View Related
Mar 27, 2007
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.
View 3 Replies
View Related
Aug 14, 2006
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 Related
Feb 27, 2006
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
View 1 Replies
View Related
Apr 22, 2004
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
View 2 Replies
View Related
Aug 2, 2007
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
View 1 Replies
View Related
Sep 28, 2007
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.
View 1 Replies
View Related
Sep 7, 2007
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
View 5 Replies
View Related
Mar 27, 2008
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.
View 3 Replies
View Related
Sep 11, 2006
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
View 3 Replies
View Related
Aug 21, 2006
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.
View 9 Replies
View Related
Dec 11, 2006
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
View 5 Replies
View Related
Apr 18, 2008
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?
View 5 Replies
View Related
