Automatic Import Of Emails
Jul 14, 2006
Is there a way to automatically import incoming emails into a SQL
Server Database?
I know it is inefficient, but we have to run a number of rules on the
email after it is received. Store it in a database and be able to
connect other things, meetings, contacts, etc. Moving it back and
forth in chunks can be difficult and the overhead could spiral out of
control.
So we were thinking of moving it to the DB as it was retrieved.
What do you think would be a better solution to retrieve it?
Does my email make sense?
View 2 Replies
ADVERTISEMENT
Mar 22, 2001
Dear all,
I'm new to SQL server. I would like to ask how to do the following. Now I have many CSV files in a directory, which are uploaded through ftp from my client computers daily. How can I automatically import those CSV files to the SQL server? Can I do it by a script or a stored procedure or something else?
Your help would be much appreciated.
Thanks,
Roland Lee
View 2 Replies
View Related
Jul 20, 2005
Hi y'allI'm having some trouble with the following script I use in adts-package. I use it to automatically import a dat-file of which thename changes every day. Today the name f.e. is P0000020.dat, tomorrowit's P0000021.dat and the day after P0000022.dat etc etc The error Iget is end of instruction expected.This is the script:'************************************************* *********************' Visual Basic ActiveX Script'************************************************* ***********************Function Main()Dim oPkg, oDataPumpDim sSourceTable, sDestinationTable' Derive the new table namessSourceTable ="" & GetNamesDestinationTable = sSourceTable' Get reference to the DataPump TaskSet oPkg = DTSGlobalVariables.ParentSet oDataPump = oPkg.Tasks("DTSTask_DTSDataPumpTask_1").CustomTask' Set the new valuesoDataPump.SourceObjectName = sSourceTableoDataPump.DestinationObjectName = "[Test].[test].[tblXafaxOutput]"' Clean UpSet oDataPump = NothingSet oPkg = NothingMain = DTSTaskExecResult_SuccessEnd FunctionFunction GetNameDim number, counter, filecounter = 1number = DTSGlobalVariables("NextNumber").Valuenumber = number + 1For counter = 1 To 7 - Len(number)file = file & "O"Next tellerfile = "P" & file & getal & ".dat"DTSGlobalVariables("NextNumber").Value = numberGetName = fileEnd FunctionI've used this script before in another dts-package where thefile-name needs to be based up-on the date and it worked just fine.You can find the script at:http://groups.google.be/groups?hl=n...l e.com#link10I hope anyone can help me.thanx in advancePiedroPS I've my english is crap it's because it ain't my mothertongue.
View 1 Replies
View Related
Oct 17, 2006
I just got sql 2005. Can someone point me in the right direction on getting started importing data. I need to be able to load ascii data into SQL 2005 on a weekly basis. This ascii file will have the same data structure but data will change weekly.
I have heard to do this automatically (basically with a click of a button) you should use a DTS package. I also see this SSIS. I am trying to understand what to use for this import process. I would really appreciate any help on this. Thanks.
View 1 Replies
View Related
Feb 15, 2008
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.
Any advice would be helpful
Thanks
View 1 Replies
View Related
Mar 12, 2007
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
View 6 Replies
View Related
Sep 14, 2004
How would I go about checking incoming e-mails? For example, on a certain e-mail address, I would get e-mails formatted in a certain way. According to the response, some scripts need to run/ some sql tables updates etc. How can one do this in (ASP) .NET with SQL Server? Anyone did this kind of stuff before?
View 3 Replies
View Related
Feb 14, 2006
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.
Thanks in advance.
View 2 Replies
View Related
Sep 11, 2014
I'm trying to remove emails from a table but the simple update query I wrote doesn't seem to do it. I figured I'd test one record instead of the entire dbase. I've probably given more info than needed (the select statement) but wanted to give a frame of reference.
SELECT
Name.ID,
EMAIL,
COMPANY,
MEMBER_TYPE
FROM
APSCU_PROD.dbo.Name
[Code] ....
View 9 Replies
View Related
Jul 23, 2005
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
View 3 Replies
View Related
Jul 20, 2005
Just wondering if there are other people getting mail from Pinnaclesoftware demanding payment of $179 for a subscription that they didn'trequest. I am now on the"Fourth Notice - Invoice past due"I know only one other SQL Server DBA and that person is also gettingthe mail each month. Information on the invoice says the paymentshould be made to Ragan Communication. Is there anyone else out therewho's getting these mails demanding money. I personally find itdisgusting that the company operates in this fashion.
View 1 Replies
View Related
Jan 23, 2007
We have SQL server 2005 Reporting Services SP1. In Report Manager I have a shared subscription that runs every hour between 8am and 6pm (I created the shared subscription in Report Manager, then modified it in Sql Server Management Studio to run the hours I wanted). I have a second shared schedule that runs Tuesday and Friday at 7am. For 2 separate report subscriptions - one uses the hourly schedule and the other uses the 2-day schedule, the first time they run during the day it sends 2 email - after that it sends only 1 email, as it should. I created a subscription to email a report when the report content is refreshed from a report snapshot. In Properties...Execution, I selected to render this report from a report execution snapshot, and selected my shared schedule. For the History property I have selected Allow report history to be created manually and Store all report execution snapshots in history; I am not using a schedule to add snapshots to report history in the History property.
When I look at the job in Sql Server Management Studio, it has only ran once each hour. When I look at the snapshot history, there is only one record for each hour. Why is the first scheduled instance sent twice instead of once?
View 8 Replies
View Related
Mar 30, 2007
I don't know much about broker service so I have question. We have a content management system the we developed locally, and what we have are catagories and subcatagries. When people choose a topic we send them an email about the topic they are interested in. We are now selling webinars and white papers and articles on demand. We would like to be able to send a reminder email to anyone who signed up for a webinar or special event. Is this possible with broker service?
View 4 Replies
View Related
Apr 23, 2007
Hi,
i am getting the following error when i try to schedule a report and Delivery it through an email
"The e-mail address of one or more recipients is not valid"
although, i have mentioned my pop3 mail account, i have included the smtp server details in the .config files as per the msdn help.
pl help me solve this
T & R,
Bharath V V
View 3 Replies
View Related
Aug 28, 2006
I presently have a Access macro that emails conditionals in a report to users in web format HTML. I would like to turn that into a stored procedure, but having some difficutly can anyone out there assit me please??
View 1 Replies
View Related
Oct 9, 2014
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?
jondoe1@gmail.com
jondoe2@gmail.com
jondoe3@gmail.com
jondoe4@gmail.com
jondoe5@gmail.com
View 2 Replies
View Related
Apr 25, 2008
Not sure if this is possible, I have a requirement, when email comes into our MS outlook Inbox, we need to identify the email based on the From email address and read the data which is in the body of the email. I do'nt know if we can do something like that using SQL 2005, any ideas?? Thanks!!
View 1 Replies
View Related
May 29, 2008
I have a stored proc I am running, and I would like to create a list of email addresses from a table and put that list into a variable. I did a basic while loop to work on syntax, but now I don't know how to actually get each address added on. Here's how I started it
declare @start int, @testEmail nvarchar(2000)
set @start = 1
set @testEmail = NULL
while @start <= (Select count(PADM_Email) from PADM_Emails)
Begin
--Print @start
set @testEmail = @testEmail + (Select distinct PADM_Email from PADM_Emails) + ';'
set @start = @start + 1
End
I know that the above is wrong, but I don't know how to get it right. Ideally, I want the @testEmail to look like this:
emailaddress1;emailaddress2;emailaddress3;
View 7 Replies
View Related
Mar 14, 2007
Hello,
I just joined the forum and consider myself a SQL beginner. I have installed and supported, run backups and similar, but haven't done much with queries and scripting.
I have a MS SQL 2000 server with a company database on it. Amongst other things, the database contains names, birthdays and e-mail addresses. How difficult would it be to setup an automated query to run every morning comparing birthdays to the current date and send automated e-mails out to matching people saying "Happy birthday ...."?
Thanks in advance,
MrNobody
"If I'm not there, I must be somewhere else"
View 8 Replies
View Related
Mar 15, 2007
I am setting up a data driven subscription to send out an email which includes a report. The Sql for the data driven subscription outputs the EmailTo, Subject, Comment(Body) and some report parameters. My issue is with the Comment (Body) field which I would like to format with CR/LF to make the email body look neat and readable. I've tried many different permutations of inserting the CR/LF, From concatenating Char(13) + Char(10) in which the email does recognize the Cr/LF to concatenating binary 0x0D + 0x0A which after the first concatenation of binary nothing else can be concatenated. Here is the code I've tried:
---~~~~~~~~
declare @iEndDate datetime
set @iEndDate = dbo.fn_today()
Declare @Account_group int
Set @Account_group = 999
/*
DECLARE @mybin1 binary(5), @mybin2 binary(5)
SET @mybin1 = 0x0D
SET @mybin2 = 0x0A
*/
Declare @CrLf VarChar(5)
set @CrLf = CHAR(13) + CHAR(10)
Declare @Str1 VarChar(255)
Declare @Str2 VarChar(255)
Declare @Str3 VarChar(255)
Declare @Str4 VarChar(255)
Declare @Str5 VarChar(255)
Declare @Str6 VarChar(255)
Declare @Str7 VarChar(255)
Declare @Str8 VarChar(255)
Declare @Str9 VarChar(255)
Set @Str1 = 'Good Morning,'
Set @Str2 = 'We are exposed by '
Set @Str3 = 'Attached please find the margin call detail.'
Set @Str4 = 'Please let us know if you have any questions.'
Set @Str5 = 'Thank you.'
Set @Str6 = 'Please respond to:'
Set @Str7 = ''
Set @Str8 = ''
Set @Str9 = ''
declare @t table
(
EmailTo VarChar(255),
ReplyTo VarChar(255),
Subject VarChar(255),
Comment Text,
Account_group Int,
Trading_Account_Id Int,
Broker_Code_Ky VarChar(255),
EndDate Datetime
)
insert into @t
select
EmailTo = '', -- isnull(bf.firm_email,''),
ReplyTo = '',
Subject = 'Margin Call Notice: - ' + Trading_account_name + ' - Exposure: ' + '$' + Convert(varchar(20), convert(Money, Mark_amount),1) ,
Comment = @Str1 + @CrLf + @CrLf + @Str2 + '$' + Convert(varchar(20), convert(Money, Mark_amount),1) + '.' + @CrLf + @CrLf + @Str3 + @CrLf + @CrLf + @Str4 + @CrLf + @CrLf + @Str5 + @CrLf + @CrLf + @Str6 + @CrLf + @Str7+ @CrLf + @Str8 + @CrLf + @Str9,
Account_group = @Account_group,
Trading_Account_Id = trading_account_Id,
Broker_Code_Ky = EGS.Broker_Code_Ky,
EndDate = @iEndDate
from fni_ExposureGovernmentSummary (@iEndDate) EGS
join broker_group bg
on EGS.broker_code_ky = bg.broker_code_ky
Join broker_firm bf
on bg.fbe_firm_id = bf.broker_firm_id
Order by
Broker_Group_Name,
Trading_account_Name
select top 2 * from @t
--~~~~~~~~~~~~~
The Email my company is using is Lotus Notes.
The Question is how do I code the the Comment field so that my email will recognize the Cr/LF.
Thanks
Elias
View 2 Replies
View Related
Nov 2, 2007
Hi everyone,
Is there somewhere that I can change the links that are included in subscription emails? The link that goes out is incorrect because the port number of the server is not included in the link, so I'd like to edit this. Does anyone know where this can be done? Thank you!
-Keith
View 1 Replies
View Related
Mar 30, 2012
I developed a mass mailing tools, but all mails received in junk and spam folder
I use an store procedure 'sp_send_dbmail' which is by default in sql server, when i send an testing mail using object explorer ->Database mail -> right click -> send testing mails, then this mail received in inbox, but when i call 'sp_send_dbmail' this procedure using job with 2 min intervals then mails received in junk or spam folder of recipients..
I configured correctly all information of smtp details ip address...
Why mails r received as junk or spam folder ??
View 2 Replies
View Related
Apr 18, 2012
i got stuck, while sending emails using store procedure sp_send_dbmail, when i m sending simple text format then mail received in inbox but whenever i m trying to send an html format emails with links of images and href tags mails received in spam.. i store procedure is given below..
DECLARE @tableHTML varchar ;
SET @tableHTML =
'<table border="1" >
<tr>
<td width="187"><a href="http://printbooth.in/product-list.php?cat_id=16&subcat_id=265" target="_blank"><img
[code]...
View 1 Replies
View Related
Jun 10, 2015
I am just trying to decipher this sql and the only thing I do not understand what is going on here is what the "input" key word is doing below. I believe its loading all of the select into the table ##OrderEmails, what is it doing?
Code:
SELECT
LEFT(OAORDT,4) + '-' + SUBSTRING(RTRIM(OAORDT),5,2) +'-' + RIGHT(OAORDT,2) "Date",
OACONO Company,
OADIVI Division,
COUNT(DISTINCT oaorno) "# Orders"
--/*OAORST + '/' +*/ OAORSL "Order Status"
INTO ##OrderEmails
[Code] ...
View 3 Replies
View Related
Dec 14, 2007
Hi, with some help today I was able to get my stored procedure runningand have the results emailed to me. However, this is how its showingup:Accounting_Year WK_IN_FYEAR LocationGL_AccountCol Data Difference--------------- ----------- ------------------------------------------------------------------ -------------------- --------------------2007 49 Test1500-001-2587872.0200 -2587872.0200 .00002007 49 Test2500-001-3344713.5000 -3344713.5000 .00002007 49 Test3500-001Is there anyway to line them up side by side properly? When i have twocolms selected the format comes out ok. Thanks for all the helpagain!Here is the sp:CREATE PROCEDURE [dbo].[spEmailVariance](@SubjectLine as varchar(500),@EmailRecipient VARCHAR(100))ASDECLARE @strBody varchar(5000)set @SubjectLine = 'Weekly Flash Update'SET @strBody ='Select statement'exec master.dbo.xp_sendmail@recipients= 'XX@XXXX.com',@subject= @SubjectLine,@query = @strbodyRETURNGO
View 2 Replies
View Related
Sep 21, 2007
Hi
I have report subscription to send mail to external emails.
It works fine for Internal email id's, external email id's doesn't work.
Any body knows what configuration changes we need to do.
Please let me know if any body knows.
Thx
vijji
View 4 Replies
View Related
Jul 16, 2007
Hi there,
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?
Thanks in Advance,
Liv
View 6 Replies
View Related
May 21, 2007
I'm hoping one of you will be able to help me because I haven't had very good luck finding any information on this.
I'm kind of new to SQL, but I'm learning as much as I can.
I created a web form that sends a new record to a SQL 2005 table I setup.
This all works exactly as it should, but I would like to have an email sent out every time a record is added to this table.
I have SQL Mail setup and I ran a test and it worked, but I can't seem to find any info on how to create a trigger that will send an email to me when a new record is added to the table.
My Database is called Engineering
The table is called ESSPartNumLog
And I have the following Columns that I would like to send in my email..
ESSSequence (PK,int, not null)
MaterialType (nvarchar(255, null)
ESSPrefix (nvarchar(255, null)
PartDescription (nvarchar(255, null)
Project (nvarchar(255, null)
PM (nvarchar(255, null)
Any ideas, or can you point me in the right direction?
View 1 Replies
View Related
Sep 8, 2015
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?
View 11 Replies
View Related
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.
What other options do we have?
Alex.
View 3 Replies
View Related
Sep 13, 2007
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?
View 4 Replies
View Related
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 .
Thanks
View 1 Replies
View Related
May 5, 2014
I am running RS 2012 installation and everything worked until few days ago. Now the subscriptions would not even run. When I configure new ones, after the time passes, there is not error message, not even a sign it ran.
View 2 Replies
View Related
