Customizing The From Address When Sending Emails From SQL 2005
Dec 17, 2007
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.
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 .
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.
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?
in our development and testing environment we always use (nolock) on every table to minimize locks.
I have found out that we can do the following:
Go to Tools -> Options -> Query Execution -> SQL Server -> Advanced
Set the drop down box to READ UNCOMMITTED for SET TRANSACTION ISOLATION LEVEL
This removes the need to have the NOLOCK hint on every table.
However, I have some reservations about this. If I create a stored procedure in MS with these settings, will they apply on the production server when I deploy it? A window appeared when I changed the settings stating something like 'it would only affect the queries run on this instance of Management Studio, but I need to be sure.
A colleague suggested that sometimes setting session defaults like this causes them to be written into coded scripts and installed procedures written in management studio and this is not obvious therefore you get inconsistent results across users apparantly running the very same script.
Can anyone please advise me as to whether this setting is a good/bad idea in a development/testing/production environment?
one of the requirements is that if the report takes a long time to run the user can start report processing and later when that report is processed user can see that in his 'My reports' section,
What will be the best way to do this, my main concerns are -
1 How can I use scheduling to accomplish this.
2 How to save the report, as snappshot or what...
3. How can I find in my Web app. that report is ready or not and show a link to it.
i'm trying to configure the Axis Scale Interval with the Values of the dataset(Category Fields) and not a defined or a auto scale values, i want each graph bar have an Axis value.
In my Case i have in the Category Fields (the X Axis) day type Values and i want each graph bar with a day. I''d dropped the field date to category fields but i can't get the days all followed i only can get the days by some stupid axis auto interval.
I need to place a jpeg image of our company logo in the middle section of the Report Manager heading between the wording of "reporting services" and Home | My Subscriptions | Site Settings | Help
I am using SQL Reporting Services 2005 with SQL Server 2005 SRV Pack 2
Is there any way to customize reporting services error and provide a user friendly message? We use Reporting Services 2000 and the error message is: Reporting Services Error
An error has occurred during report processing. (rsProcessingAborted) Get Online Help
Query execution failed for data set 'dsasSource'. (rsErrorExecutingCommand) Get Online Help
I have not been able to find any substantive documentation on connecting a DataSource to the database via the IP address of the SQL Server. Does anyone know of a good reference on the topic? I want to be able to access the data on the server while developing and testing locally on my laptop (not on the same network).
I am trying to connect to SQL Server Express 2005 from a remote connection through the internet using an IP address in a string like this;
Data Source=190.190.200.100,1433;Network Library=DBMSSOCN;Initial Catalog=myDataBase;User ID=myUsername;Password=myPassword;
the error I am getting is this
A connection was successfully established with the server, but then an error occurred during the login process. (provider: TCP Provider, error: 0 - The specified network name is no longer available.)
anyone have any suggestions or maybe another way to achieve this type of connection?
Background: I am a post-graduate student developing a model of the way HIV/AIDS spreads in communities. The data sets I am using are large (eg census data - 50m records) and the manipulation is complex (eg deciding which individuals will begin a sexual partnership, and with whom).
Problem: I need to process a table of data in code written in C# that will insert the results which are also a table of data into a table in the database.
In the example set up below, a table with rows comprising an Integer and a Real is the input. The output is a table where each row also comprises an Integer and a Real. Each row of input, say (Inti, Reali) leads to Inti rows of output. The example below gives a simple example of this.
The T-SQL code below sets up the problem, and includes simplest possible T-SQL that would lead to the requisite Output, so that you could compare your solution's code's with it:
-- Set up tables to use in example
CREATE TABLE InputTable
(
Int1 INT NOT NULL,
Real1 REAL NOT NULL
)
GO
CREATE TABLE OutputTable
(
Int1 INT NOT NULL,
Real1 REAL NOT NULL
)
GO
--Initialise the Input Table
INSERT INTO InputTable (Int1, Real1) VALUES (3, 0.5)
INSERT INTO InputTable (Int1, Real1) VALUES (5, 0.99)
--Manually put Data into Output table
--I do know how to code this in T-SQL using cursors and loops
--I wish to code this part in CLR
INSERT INTO OutputTable (Int1, Real1) VALUES (1, 0.5)
INSERT INTO OutputTable (Int1, Real1) VALUES (2, 0.5)
INSERT INTO OutputTable (Int1, Real1) VALUES (3, 0.5)
INSERT INTO OutputTable (Int1, Real1) VALUES (1, 0.99)
INSERT INTO OutputTable (Int1, Real1) VALUES (2, 0.99)
INSERT INTO OutputTable (Int1, Real1) VALUES (3, 0.99)
INSERT INTO OutputTable (Int1, Real1) VALUES (4, 0.99)
INSERT INTO OutputTable (Int1, Real1) VALUES (5, 0.99)
--This should show the output
SELECT * FROM InputTable
SELECT * FROM OutputTable
--The output should be
Int1Real1
3 0.5
5 0.99
Int1Real1
1 0.5
2 0.5
3 0.5
1 0.99
2 0.99
3 0.99
4 0.99
5 0.99
Additional Information: I would be grateful if your solution could indicate
* whether the code to be written is a class, stored procedure or user-defined function
* the answer could be included in entirity begining with "using system..."
* example T-SQL that can be used to execute the code
Current knowledge:I can write T-Sql. I have written C# user defined functions and stored procedures, assembled them and run them from within SQL-Server 2005.
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 am attempting to use dbmail from an application that logs in to my database using an application role. Since the application role does not exist outside the database, I created an spSendEmail in the database with "execute as login='mailagent'" in it: I set the database to trustworthy, created a 'mailagent' account and added it to the msdb database with the databasemailuser role rights.
Email works just fine on the server when I use the execute as login='mailagent' to fire off the msdb..sp_send_dbmail. But from the .NET application, I get the error: "Cannot execute as the server principal because the principal 'mailagent' does not exist, this type of principal cannot be impersonated, or you do not have permission." When I run my spSendEmail stored procedure from the calling database, I get the same error.
I am trying to send a file from SQL 2005 back to our AS400 DB2 db. First time trying to get this to work in SSIS.
I have a couple fields that are datetime in SQL Server and see SQL server sees datetime fields from DB2 as string so I am converting them to Strings using derived columns. That part seems to work fine. Get no errors on that part.
The conversions seem like they are working but it is faling when it tries to send it to as/400. I get the following error: any ideas on what is wrong?
[OLE DB Destination [1569]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "IBMDA400 File Rowset" Hresult: 0x80004005 Description: "CPF5029: Data mapping error on member DSSCNTL. ". An OLE DB record is available. Source: "IBMDA400 File Rowset" Hresult: 0x80004005 Description: "CPF5035: Data mapping error on member DSSCNTL. ". An OLE DB record is available. Source: "IBMDA400 File Rowset" Hresult: 0x80004005 Description: "CPF5035: Data mapping error on member DSSCNTL. ".
I even created a sql table with same field sizes as one I am sending to AS400 and its sucessful there. So seem like it mght have somthing to do with sending to AS400 but not sure what.
Hello, I've been banging my head trying to figure this one out so I appreciate any help you guys can provide.
I've setup and configured SQL 2005's Database mail and have sent a few plain and simple text emails and I receive it fine at the destination.
I now want to send mime messages but I'm not sure what I'm doing wrong. I took the following source from an email that linked-in sent me which was a mime message: "
Mime-Version: 1.0
Content-Type: multipart/alternative;
boundary='----=_Part_434343432'
------=_Part_434343432
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit
Plain Text Goes Here
------=_Part_434343432
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 7bit
<html>
<body>
<b>HTML Goes here</b>
</body>
</html>
------=_Part_434343432--
" and put the above text in the @body parameter of the stored proc msdb.dbo.sp_send_dbmail and submitted it.
When I received the email, it didn't appear as html. Instead, I just got the mime message above as plain text in my email as if someone just typed it and sent it to me. What do I need to do for SQL Database Mail to send the above as a MIME message?
I have noticed in the database logs that someone is trying to log into our SQL Server express databases, is there a way of allowing only IP address on a list to access the database. We really only need the local machine and the office using SQL Express management studio to access the database.
I would appreciate any help sometimes the database runs very slow and we do not know what is causing this.
We are running server 2003 and SQL Server express.
I'M HAVING AN ISSUE, UNDERSTANDING, THE CONNECTION STRING. I WANT TO CONNECT TO AN INSTANCE OF SQLEXPRESS ON A REMOTE SERVER WITH A FIXED IP ADDRESS THE TCP PORT IS OPEN TO 1433 I OPENED THE PORT ON THE ROUTER AND THE WINDOWS 2003 FIREWALL
MY CODE IS AS FOLLOWS: S = "Provider=SQLNCLI;" S = S & "DATA SOURCE=44.66.777.888,1433SQLEXPRESS;" S = S & "INITIAL CATALOG=TESTDB;" S = S & "Persist Security Info=false;" S = S & "UID=TEST999;" S = S & "Pwd="TEST999888;" CnMgt.ConnectionString = S CnMgt.Open S
I'VE FOLLOWED THE STEPS OUTLINED IN ARTICLE 914277
CAN SOMEONE HELP? THANK YOU
PS. IF THE CLIENT IS LOCAL, I HAVE NO ISSUE OPENING THE DATABASE. I DO NEED TO OPEN THE DB FROM FROM CLIENTS.
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.
Hi, looking for some help on job notification emails in SQL 2005.
I've created a backup job in the Maintenance Plan section of SQL2005, and added a "Notify Operator" task to notify me when the job completes. I've setup Database Mail, and I am getting the notification email whenever I run the job. So this is working fine.
The problem is when I got to the 'Jobs' folder, and into the properties of my backup job, I set it up to notify me from the notifications option, but I am not getting any emails. For now I have set it to notify me when the job "succeeds".
Any ideas why it works from the Maintenance Plan section, but not the "Jobs" notification section? Am I forgetting something?
I have recently migrated a SQL2000 instance to SQL2005, and would now like to setup all the existing jobs to notify me if they fail.