Problem: We need to send email to multiple recipients that are not static and will be changing.
Solution: Below is how our control flow will look
Step1. First we will create a table and populate the table with Emails
create table EmailRecipient
ID int identity(1,1)
We will create two variables as shown below
Step 3. We will start configuring components in the control flow starting with Execute SQL Task. Create an OLE DB connection manager to be included here.
Resultset: Full result set
Step 3. Configure Foreach Loop Container as shown below
Step 4: Configure Script Task
Below will be the one line code inside Script task
public void Main()
// TODO: Add your code here
Dts.Variables["ToLine"].Value = Dts.Variables["ToLine"].Value + "; " + Dts.Variables["TempString"].Value;
Dts.TaskResult = (int)ScriptResults.Success;
After including SMTP Connection Manager to the project, configure Send Mail Task
Since the recepients to whom email will be sent to are configured in Script Task, ToLine will be mapped to the variable
Say we have a stored procedure that we want to copy across multiple databases. One way would be to create the script and execute in all the databases. But if Databases are many it can be time consuming and efforts to change Database name every time before executing the script will be more. One better way is writing a script that can create the Database object in selected Databases or all Databases at once. [More]
In this article we will see the way execution order works in SQL server, mainly when TOP, Union and order by clause are utilized in a query.
Say we have an Employee table
create table Employees
(EmployeeID int identity(1,1) NOT NULL
,EmployeeName varchar(50) NOT NULL default 'N/A'
,Department varchar(50) NOT NULL default 'N/A'
,Salary float NOT NULL default 0) [More]
Performance is something every Developer struggles with at some point or other. In order to do optimization we can see query plan. This article will demonstrate as how we can create the function that will show us the query plan and how we can use it to see plans of objects that are highly used [More]
Certain Business requirements may involve sending database mail that incorporates company's logo, and other formatting things like thick border, particular font etc...Below article describes how this can be done using Database Mail. [More]
TRY...CATCH block is very efficient mechanism for error handling. In the previous versions of SQL Server @@Error was used, that required checking for error after every SQL statement. [More]
In this article we will be learning how to copy image file or any file form one folder to another and to save the filename to database.
Now there are situation when we need to copy a filename to different directory or a specified directory from your user application e.g. Suppose there is an application which copies you photos and picture file to a specific directory and access those files from that directory. In situations like this your application need to save the files to the specific intended directory. [More]
Table Valued Parameters are new to SQL Server 2008. They allow us to pass table variable to stored procedure. The same function can be accomplished by temporary tables, but table valued provide better performance and complete dataset in a table can be passed to stored procedure. [More]
While using msdb.dbo.sp_send_dbmail for sending email, we realize that @receipients parameter takes up only one email address, unless that email address is not pinting to a distribution list all receipient cannot get email. In case we want to accomplish sending email to multiple receipient we can do following: [More]
Custom Paging is one of the most efficient ways of improving performance in any application. When we use the paging that comes with in controls like Gridview, DataList, DetailsView, ListView etc... is not very efficient as the complete data gets loaded in the memory and paging takes place there. Custom Paging will help bring only request number of rows from our relational database. [More]