Sending Emails Throught DTS Packages In Sql Server 2005
Jan 23, 2007
Hai,
I am working on the DTS Package in SQL Server 2005 , actaully the DTA Packages Use OutLook to Send the email to the Mailing List , now that Out Look Stuff is not working Fine , soo I came to know that we can use Sp_send_dbMail stored Procedure insteds of Outlook , i am trying to do that my Email contains the attaachment to the Text file from the shared Folder.
I am not Getting the Correct Help when i am trying .
So can some one Helpe Providind Some examples and suggetions .
We have the need to be able to send emails from our SQL 2005 server with the from address field determined at run time from a table. What are my options? We are running a clustered SQL 2005 x64 standard edition on Win 2003 Enterprise x64.
1. Database Mail - with all the hoopla about how nice it is to have a proper SMTP mailer in SQL, it still seems to lack this functionailty. IMHO a HUGE miss. 2. sp_send_cdosysmail - We used this when we had a SQL 2000 on W2K and it worked just fine. On our current environment, the stored procedure works for a period of time and then suddenly and out of no where we see this message in its log files:
CDO.Configuration.1 - A dynamic link library (DLL) initialization routine failed. sp_OAGetErrorInfo for sp_OASetPropery sendusing
After which no emails are sent until I restart the SQL Server service. We have tried both the sendusing method of 1 (local pickup) as well as 2 (remote SMTP server) and both are exhibiting the same behavior.
Hi I am using asp.net framwork 2 with sql 2005 express edition. Can anyone please provide me any sample of sending automated emails (like welcoming people when they register to my website, or if their personal details have been updated....). or any e-book/website ref will do. I am not very expert in sql/db and smtp (or other email) stuffs. So please help me. With thanks
I'm new to programming and to databases so apologies if this sounds like a stupid question.
I have a html page with a simple javascript function that uses the href mailto tag to send an email to a normal email address. I was wondering if it would be posssible to send the email directly to an SQL Server database, without having to pass through an intermediate parsing app. It would mean assigning an email address to the database. Is this possible with SQL Server? I know there would still be problems with parsing the email content to the appropriate fields, but would the sending of the email to the database in itself be possible?
I haven't much experience using databases and I don't currently have access to the SQL Server Express database that will be used to store the email content, so I'm not sure how difficult an operation this would be. I've looked on the internet but I haven't been able to find anything. I was hoping someone here might have done something similar, either with SQL Server or with any other database.
I need to send multiple emails to recipients one at a time what is the best way to do this?for example i do a select statement to get 5 email addresses, now i need to be able to fire off an email with verbiage to each one of them separately. The reason they have to be separate is in the verbiage i need to have there name in the 'Dear Jondoe1,' Here is my second dilemma. How would you insert a name in the verbiage for each email?
First of all, I am very new to SSIS. I came across a requirement that I need create files and send emails, I have done experiments on these two individually. But together this is what I need to do and create a package that does all.. From the result of a SQL query above, in a location create excel files Administration.xlsx, Cafeteria.xlsx, Front Office.xlsx that has their own data rows, then email those files to the appropriate employee. Person1 and person2 will receive Administration.xlsx, manager will receive Cafeteria.Xlsx and sec will receive Front Office.xlsx.
Is it possible to create a dtsx package that does them all?
I have the following code on a form: <asp:SqlDataSource ID="building_a" runat="server" ConnectionString="<%$ ConnectionStrings:vol1ConnectionString4 %>" SelectCommand="SELECT [E_MAIL] FROM [Sheet4$] WHERE ([BUILDING] LIKE '%' + @BUILDING+ '%')" OnSelecting="building_a_Selecting"> <SelectParameters> <asp:Parameter DefaultValue="a" Name="BUILDING" Type="String" /> </SelectParameters> </asp:SqlDataSource> <asp:SqlDataSource ID="building_b" runat="server" ConnectionString="<%$ ConnectionStrings:vol1ConnectionString4 %>" SelectCommand="SELECT [E_MAIL] FROM [Sheet4$] WHERE ([BUILDING] LIKE '%' + @BUILDING+ '%')" OnSelecting="building_b_Selecting"> <SelectParameters> <asp:Parameter DefaultValue="b" Name="BUILDING" Type="String" /> </SelectParameters></asp:SqlDataSource> <asp:TextBox ID="subj" runat="server" Width="500px">Subject</asp:TextBox><br /><br /> <asp:TextBox ID="messg" runat="server" Rows="20" TextMode="MultiLine" Width="500px">Message</asp:TextBox><br /><br /> <asp:Button ID="bldga" runat="server" Text="Building A" OnClick="Button1_Click" /><br /><br /><asp:Button ID="bldgb" runat="server" Text="Building B" OnClick="Button2_Click" /><br /><br /> <asp:Label ID="resultLabel1" runat="server" ForeColor="red"></asp:Label> And I have the following code behind:protected void Button1_Click(object sender, EventArgs e) {SmtpClient smtpClient = new SmtpClient(); MailMessage message = new MailMessage(); try {MailAddress fromAddress = new MailAddress("me@mydomain.com", "My Name");MailAddress toAddress = new MailAddress("them@theirdomain.com"); message.From = fromAddress; message.To.Add(toAddress); message.Subject = subj.Text;message.IsBodyHtml = false; message.Body = messg.Text;smtpClient.Host = "myhost"; smtpClient.Send(message);resultLabel1.Text = "Email sent!"; }catch {resultLabel1.Text = "Send failure"; } } The queries get e-mail addresses from a SQL database for all people who work in a particular building. The buttons will send an email if I give a To address. How do I write the code so that clicking on the "Building A" button will send the e-mail to the addresses from the query results individually? Any ideas?
I have a job that runs every 3 minutes. The email that I received said the job failed at 10:30 a.m. but when I run the "All executions report" I see a skip in the times from 10:27 to 10:33. That failed Job is not logged as an execution.I looked in the system event log and I do not see anything odd at that time.
I am looking for a way to have SQL server accept incomming e-mails and store the contents of the e-mail into the database. I was doing some research on the subject of recieving emails from a SMTP server with the server having an account, but it looks like there are no information on the subject.
Is this possible? Or do I have to use some sort of work around hack?
hello guysI'm trying to access a sql server database through an asp.net web application and I get the following message"login failed for user 'MYCRAYASPNET' " where MYCRAY is the server name. I enabled mixed mode authentification to the server and created a user "ASPNET" for the database. But while creating the user, enterprise manager never asked me to supply a password.does anybody know what's going on here?Thanks for any input.Naby.
Hi all, I 'm in VietNam, i want to use SQL server 2000 to connect to SQL server in China throught the internet. I did it in many way but it did not work, please show me. Best regards
Hi, I have a need to shoot and automated mail at regular intervals to clients. I got the following code for sending email from SQL Server but get an error.
ALTER PROCEDURE [dbo].[sp_SMTPMail]
@SenderName varchar(100),
@SenderAddress varchar(100),
@RecipientName varchar(100),
@RecipientAddress varchar(100),
@Subject varchar(200),
@Body varchar(8000),
@MailServer varchar(100) = 'localhost'
AS
SET nocount on
declare @oMail int --Object reference
declare @resultcode int
EXEC @resultcode = sp_OACreate 'SMTPsvg.Mailer', @oMail OUT
SQL Server blocked access to procedure 'sys.sp_OACreate' of component 'Ole Automation Procedures' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ole Automation Procedures' by using sp_configure. For more information about enabling 'Ole Automation Procedures', see "Surface Area Configuration" in SQL Server Books Online.
I have subscription by email problem on the following configuration:
Windows XP prof. SQL Server 2005 Enterprise Configured Database Mail with remote SMTP server to send email successfully Configured SSRS to use remote SMTP server
Edited the Rsreportserver.config file as following (by the instructions from MSDN)
I scheduled my subscription and tested it. I didn't receive any mail from the setting email address, but got an error message in ReportServerService log file as following.
Error sending mail, CDO error -2147220978, will not resend Error sending email. System.Runtime.InteropServices.COMException (0x8004020E): The server rejected the sender address. The server response was: 530 authentication required - for help go to http://help.yahoo.com/help/us/mail/pop/pop-11.html
I did a little search, but couldn't find any solution for it. Is anybody successfully to get the Reporting Services subscription by email function working through MS SQL Server Management Studio or if you have a solution for this issue, would you mind to share the experience with me. I will be very appreciated.
How do you tranfer data between two SQL Server 2005 databases???? I swear I looked all over the place, but I don't see how it is done in SQL Server 2005.
---------------------- MS Win XP Pro 2002 SP2 MS SQL Server 2005 MS Visual Studio 2005 ----------------------
Can anyone help me (even by pointing me to a documentation) in order to schedule Packages (from file system source) in SQL Server 2005.
I've configured providers logging, but still the error file doesn't give me any explanation why the error happens: "#Fields: event,computer,operator,source,sourceid,executionid,starttime,endtime,datacode,databytes,message OnPreValidate,PC1234,NT AUTHORITYSYSTEM,D_AGR,{8A4FA774-F5F0-40DE-AB16-A93F27950E09},{8A918844-8E43-403D-A606-C8CB4B7D8238},31/08/2006 16:42:55,31/08/2006 16:42:55,0,0x,(null)"
I've also done the same on the Step properties under 'Logging'
In Management Studio I've added my login name and I'm the only user using the machine and I manage both Visual Studio and Management Studio
The error coming up on the job history is as follow: "Executed as user: PC1234SYSTEM. The package execution failed. The step failed." "The job failed. The Job was invoked by User UK1USER123. The last step to run was step 1 (step1)."
By the way the package (.dtsx) runs fine in BI Visual Studio)
I installed SP2 to one of our servers to see what we'd need to do for our Production systems. The server in question is used for a log shipped copy of production, SSRS, SSIS and general duty stuff. I got errors in two areas during the installation -- 1 dealing with SSNS (don't use it yet on that server) and the second being the Client product.
After a reboot I went into a Business Studio solution I've been working on (and using) for several months. None of my packages are working--each having the following problem:
Unable to cast COM object of type 'Microsoft.SqlServer.Dts.Runtime.Wrapper.PackageNeutralClass' to interface type 'Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSContainer90'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{8BDFE892-E9D8-4D23-9739-DA807BCDC2AC}' failed due to the following error: Library not registered. (Exception from HRESULT: 0x8002801D (TYPE_E_LIBNOTREGISTERED))..
It seems to be in the designer as it is unable to display the the graphical version but it does let me switch to the code view.
What is the easiest way to troubleshoot and/or fix this?
Hi, folks. I'm fairly new to SQL Server, so forgive any stupidityinherent in this question. I work at a university and a certainfaculty member would like to have her students create a simple auctionweb site as an exercise. As part of the assignment, she wants theauction site to be able to send an email to a bidder when they havebeen outbid.So she has asked me if this is possible, and how to do it. I've read abit about sending an automated email from SQL Server, but I can't findanything about how to trigger the email based upon a field comparisonin the database. I also don't know how to incorporate a field value asa variable into the body of the email.Of course, I don't expect anyone to solve my entire problem in thisforum. I was just hoping that someone could point me in the rightdirection. Can anyone give me any advice about where I should belooking for such info? Can you recommend a web page, or some otherreference material? Is there a specific terminology I should be usingwhen I search? I just don't seem to be able to find what I need.Please reply to Join Bytes! -- I don't read newsgroups regularly.Many thanks in advance!--Sue
We are converted our DTS 2000 packages to Sql Server 2005 SSIS. I am getting following error on my ActiveX script that got converted. I am new to SSIS and DTS. Never ever worked with ActiveX also. So any help would be appreciated. Following is the script followed by error I get:
I am using the 3-tiered architecture design (presentation, business laws, and data acess layers). I am stuck on how to send the image the user selects in the upload file control to the BLL and then to the DAL because the DAL does all the inserts into the database. I would like to be able to check the file type in the BLL to make sure the file being uploaded is indeed a picture. Is there a way I can send the location of the file to the BLL, check the filetype, then upload the file and have the DAL insert the image into the database? I have seen examples where people use streams to upload the file directly from their presentation layer, but I would like to keep everything seperated in the three classes if possible. I also wasn't sure what variable type the image would be in the function in the BLL that receive the image from the PL. If there are any examples or tips anyone can give me that would be appreciated.
My home machine has sql 2005 and Visual Studio 2005 installed. I uninstalled VS 2005 (RTM) and installed VS 2008 (RTM) in its place. I then found that the SSIS templates were missing. Lesson: don't uninstall VS 2005 if you want to use SSIS 2005!
1. Is there any "workaround" to this issue?
2. (If the answer to 1 is "No") I will install VS 2008 side-by-side with VS 2005 on the same box. When the SSIS issue is finally resolved, can I uninstall VS 2005 without doing damage to the VS 2008 install?
I believe I have made a complete install of Sql Server 2005 on my dev. machine, including all client tools, and can run SSIS packages from the dev. environment.
However DtExec refuses to run any package and always throws the "insufficient version" error (as does the "RunPackage" component, probably for the same reason).
I have repeatedly attempted to find something more to install from the Sql Server 2005 eval release that will allow DTExec to run without the "insufficient version" error, but can find nothing else to install from the eval. The install wizard (and the "change" wizard) indicates everything is installed and will not build a list of any additional components to install.
Are we sure that DtExec will install and run from a sql server 2005 eval distribution?
Licenses are in the pipeline (not that pipleine) but we need to start testing our SSIS solution in the meantime.
p.s. Kudos to the dev. team, it really is an excellent tool!
We manage some SSIS servers, which has only SSIS and SSIS tools installed on them and not the sql server DB.
SSIS packages and configuration files are deployed on a NAS. We run the SSIS packages through DTEXEC by logging in to the server.
We want to allow developers to run their packages on their own on the server, but at the same time we dont want to give them physical access on the server i.e we do not want to add them into RDP users list on server properties. We want them to allow running their packages remotely on the server.
One way We could think of is by using powershell remoting and we are working on that. But is there any other way or any tool already present for the same.
When records are initially created, they are given statuses of New, Pending, Closed
By default, the status is New.
If no action has been taken after 15 days since the records were created, users are sent a reminder.
I have this part working.
The problem is that as long as the status is not equal to closed, start sending reminders every 10 days.
Here is what I am working with:
SELECT [ID] , [Name], [Email], Status FROMmyTable WHERE Status_Name != 'Closed' AND Status_Name IS NOT NULL and Status_Name = 'New' and Status='Pending' AND CONVERT(Char,DateAdd(day,-15,getdate()),101) = CONVERT(Char,Date_Entered,101)
I've previous posted a message with error building the expression of the dtexec.exe string. But now, the problem is other, and I've decided to create other threat.
Here is what I'm doing: I've a SP that receives every parameter so I can build my dtexec.exe statement to be executed by a variable with something like this exec @rc = master.dbo.xp_cmdshell @CMD
Till now I have no problem executing it, not from a variable, but just with the statement itself, like this: exec master.dbo.xp_cmdshell 'C:PROGRA~2MICROS~290DTSBINNDTEXEC.EXE /SQ PACKAGE /SET "Package.Variables[PERIOD].Value";20070101'.
But now, I have this SP that builds the string into a variable and then I just want to execute it throught the variable like this: exec @rc = master.dbo.xp_cmdshell @CMD.
When I make a SELECT @CMD just before the exec @rc = master.dbo.xp_cmdshell @CMD, so it prints the builded string, copy it and pasted to exec master.dbo.xp_cmdshell 'copied string' it works fine. But the step of exec @rc = master.dbo.xp_cmdshell @CMD returns the error: The filename, directory name, or volume label syntax is incorrect.
Why is this, if I copy the generated string and execute it by the other way it works fine, but executing the generated string throught the variable it gives me that error?
