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?
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.
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?
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.
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?
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 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.
Can anyone outline some best practices for sending the same message out to multiple servers? What is the easiest way to do this?
Currently, I have a message sent from Server A to Server B, which is fired by a table insert, update, delete trigger.
The goal is to keep this table synchronized on many remote servers. It sounds like a tedious exercise to deploy the SSB messages, queues, services, etc to 20+ more sites! I am hoping there is a shortcut of some kind.
We're looking to send messages to an SSB Queue (our 'Notification' queue) that contains multiple values.. like Message #1: 20,1 to indicate Record #20, send Notification #1.. the reason for this is we have lots of different types of records in our database, and based on certain conditions, we would like to send different notices regarding these various record types.
What is the best format to send mesages like this? We thought about sending TABLE vars.. what other options are there?
Any ideas on how I can send multiple values from a listbox to a stored procedure? right now I have a ListBox Control called lbCategory, and I want to pass multiple selected items to a stored procedure. <asp:SqlDataSource ID="dsFS" runat="server" ConnectionString="someConnectionString" SelectCommand="usp_FS" SelectCommandType="StoredProcedure"> <SelectParameters> <asp:ControlParameter ControlID="lbCategory" DefaultValue="%" Name="category" PropertyName="SelectedValue" type="String" /> </SelectParameters> </asp:SqlDataSource>
I am facing a problem in writing the stored procedure for multiple search criteria.
I am trying to write the query in the Procedure as follows
Select * from Car where Price=@Price1 or Price=@price2 or Price=@price=3 and where Manufacture=@Manufacture1 or Manufacture=@Manufacture2 or Manufacture=@Manufacture3 and where Model=@Model1 or Model=@Model2 or Model=@Model3 and where City=@City1 or City=@City2 or City=@City3
I am Not sure of the query but am trying to get the list of cars that are to be filtered based on the user input.
If I send multiple sql's with ado.net in one statement (one executeSql separated by semilcolons), and the second one fails, will the first one be rolled back? or do I need to put it all in a transaction?
fetch next from emailCursor into @Name,@info, @Recipients,@MyMessage
end close emailCursor deallocate emailCursor
Currently I have two users in my table that I want to send email to, but when the stored procedure runs I got two emails for the last user in the table instead one for each user.
Writing this code using a CTE or any other method which do not use a table variable or a temp table as the users do not permissions to run this code from the application because of their roles which are required for creating and dropping the temp tables. I replaced table variable in place of Temp tables however the query never completes, probably because of the number of rows.
SELECT A.[Entry No_], A.[G_L Account No_],A.[Gen_ Prod_ Posting Group],A.[Document No_],A.[Posting Date] INTO temp1 FROM [G_L Entry] as A ,[G_L Account] as B where A.[G_L Account No_]= B.[No_] SELECT VE.[Entry No_],VE.[Document No_],VE.[Posting Date],VE.[Gen_ Prod_ Posting Group] , GLILER.[G_L Entry No_] INTO temp2
I want the top 1, having the highest counter from each ID. This is a highly simplified version of that I am pulling which also is between a date range, but same principle.
IE: SELECT * FROM Table WHERE ID in (SELECT DISTINCT ID FROM Table WHERE Date BETWEEN <date> AND <date>
I'd rather keep it in one statement if possible, but if I have to do it in multiple passes then so be it.
I need to update a empty column in our SQL database with the login ID for employees of our company.The table is called SY01200 and were I need to put the login ID is column INET5, and the login ID is just me stripping off the company's email address(removing the @company.com), and I need to update the INET5 column only where Master_Type = 'EMP'
And here is the Query that I am using to strip the email select LEFT(convert(varchar(40),EmailToAddress),LEN(convert(varchar(40),EmailToAddress))-14) As LoginName from sy01200 where Master_Type = 'emp'And here is what I thought the Query would be to update however I got and error saying more than 1 arguement returned
UPDATE sy01200 SET INET5 = (select LEFT(convert(varchar(40),EmailToAddress),LEN(convert(varchar(40),EmailToAddress))-14) As LoginName from sy01200 where Master_Type = 'emp') WHERE Master_Type = 'EMP'
tblPerson - holds basic person data. tblPersonHistorical - holds a dated snapshot of the fkPersonId, fkInstitutionId, and fkDepartmentId tblWebUsers - holds login data specific to a web account, but not every person will have a web account
I want to allow my admins to search for users (persons) with web accounts. They need to be able to search by tblPerson.FirstName, tblPerson.LastName, tblInstitutions.Institution, and tblDepartments.Department. The only way a Person record is joined an Institution or Department record is through many -> many junction table tblPersonHistorical.
People place orders and make decisions in our system. Because people can change institutions and departments, we need an historical snapshot of where they worked at the time they placed an order or made a decision. Of course that means some folks will have multiple historical records. That all works fine.
So when an admin user wants to search for webusers, I only want to return data, if possible, from he most recent/current historical records. This is where I am getting bogged down. When I search for a specific webuser I simply do a TOP 1 and ORDER BY DateCreated DESC. That returns only the current historical record for that person/webuser.
But what if I want to return many different webusers, and only want the TOP 1 historical for each returned?
Straight TOP by itself won't do it. GROUP BY by itself won't do it.
I am working with some old code that we are trying to clean up and perform some performance enhancements. The performance is now, so Very much better. From over 3 minutes to under 2 seconds.
But I am still trying to get the multiple rows into a single row. I would like to place this into a CTE to get the multiples into a single row. I just cannot get my head around how is the best, most efficient way to write the query.
This is a small example of what the rows look like in the resultset, and what I want to single to be.
DECLARE @BillingCorrect TABLE ( ContractNumber char(10) , pc1 int , pb int , om int , vp int
[Code] ....
I am not sure how to write the query to have all the data in a single row.
Can you update data from multiple tables in the same UPDATE statement, by joining those tables in a CTE ?
For example, this fails:
DECLARE @UPDCATE_COUNT AS int = 100000; WITH COMBINED_TABLES AS ( SELECT TOP (@UPDATE_COUNT) T.UpdateID, T.IS_UPDATED, U.[Description] FROM dbo.Table1 AS U INNER JOIN dbo.Table2 AS T
I’ve never written a query with multiple APPLY joins before and I’m running into some troubles with my first one. The below SQL statement runs within 10 seconds if I comment out either one of the APPLY joins and its corresponding field columns. However, when I try to execute with both APPLY joins, the query runs indefinitely. The longest I’ve waited before cancelling it is 90 minutes.
Now, I know there are probably other ways I could write this query to get me the results I’m looking for. I’m posting this on the board because I’m curious about finding out why multiple APPLY joins could cause SQL Server to run away. I’m hoping to gain some insight so that I can better understand how APPLY joins work so that in case I have a big need to do this again in the future (without suitable workarounds) I can code it correctly.
Here are some things I’ve tried so far…
1.Changed the States table into a subquery that only returns a single state 2.Change all the references inside the APPLY subqueries so that they had different aliases (just in case they were conflicting with each other). 3.Changed the CROSS applies to OUTER applies. States has 50 records and only 32 have matching permit data so the 18 extra iterations using OUTER APPLY don’t impact performance any when an APPLY is used by itself.
SELECT s.state_name , COUNT(DISTINCT DUPS.PermitNumber) AS NumOfDupPermits , SUM(DistinctPermits) AS DistinctPermits FROM States S CROSS APPLY (SELECT w.StateID, COUNT(*) as DistinctPermits
I'm working on a project where I need to retrieve employees data and then combine the data into single row per employee.
Sample Data:
WITH SampleData (PERSON, [DATA], [FIELD]) AS ( SELECT 1234,'04/02/2014','Date' UNION ALL SELECT 1234,'123','Department' UNION ALL SELECT 1234,80.0,'Rate' ) SELECT * FROM SampleData;
The results from the above are as follows:
PERSONDATA FIELD 123404/02/2014Date 1234123 Department 123480.0 Rate
The desired results would be:
PERSONDate Department Rate 123404/02/2014 123 80.0
I have a case where if the Id field is a specific value, I don't want to allow null in another field, but if the Id value <> a specific value, null is ok.
In the example below, inserting the first record should succeed, the second should succeed, and the 3rd should fail. Right now the 2nd two fail. I gotta be missing something easy, but I can't figure it out.
USE tempdb GO IF OBJECT_ID('tempdb.dbo.CheckConstraintTest') IS NOT NULL DROP TABLE tempdb.dbo.CheckConstraintTest; CREATE TABLE CheckConstraintTest
I want to display records from @table1 only when combination of col2,col3 and col4 are present in @table2.In Below case I want output as: below two records only.
'test1', 'need this record', 25, {d '1901-01-01'} 'test3', 'some longer value', 23, {d '1900-01-01'} declare @table1 table ( col1 varchar(10) not null, col2 varchar(200) null, col3 int not null,
I have a wrapper stored procedure ProcMain_wrapper that executes in one main dataabase and it calls child stored procedure in multiple databases. Does the rollback occur in all the databases if the stored procedure fails in any one of the multiple databases?
create procedure dbo.db_Main.ProcMain_wrapper ( declare @curClientCode as cursor ,@db varchar(10),@dbName varchar(20) BEGIN TRANSACTION TRAN1 BEGIN TRY SET @curClientCode = CURSOR FOR SELECT [name] from sys.databases where name like 'dbclient_%'