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) ,Emails varchar(100) ,ISSend bit )   insert EmailRecipient values ('nehaa@devtechie.com',1) insert EmailRecipient values ('anoop@devtechie.com',1) insert EmailRecipient values ('ankit@devtechie.com',0) Step 2. Variables: 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          

Row Constructor is new feature to SQL Server 2008 that allows insertion of multiple rows of data at once. Say we create a table row_constructor create table row_construct (ID int identity(1,1) not null primary key , type varchar(20) not null default 'N/A' , name varchar(100) not null default 'N/A' )   insert row_construct (type,name) values ('A', 'Garments') insert row_construct (type,name) values ('B', 'Sports Equipments') insert row_construct (type,name) values ('C', 'Cosmetics') insert row_construct (type,name) values ('A', 'Swim Wears') insert row_construct (type,name) values ('A', 'Sports Garments')   --but with the help of row constructors we can insert the same data using single insert statement as below   insert row_construct (type,name) values ('A', 'Garments'),('B', 'Sports Equipments'),('C', 'Cosmetics'),('A', 'Swim Wears'),('A', 'Sports Garments'); --we can also write the same as insert row_construct select type, name from (values ('A', 'Garments'),('B', 'Sports Equipments'),('C', 'Cosmetics'),('A', 'Swim Wears'),('A', 'Sports Garments')) as rowtables (type, name);   --Limitations: --We cannot select from other table in values clause as below it will only give "Incorrect syntax near the keyword 'select'."   insert row_construct select type, name from ( values (select 'A', 'Garments'),('B', 'Sports Equipments'),('C', 'Cosmetics'),('A', 'Swim Wears'),('A', 'Sports Garments') ) as rowtables (type, name);   insert row_construct (type,name) values (select 'A', 'Garments'),('B', 'Sports Equipments'),('C', 'Cosmetics'),('A', 'Swim Wears'),('A', 'Sports Garments');   --We can insert from multiple tables only by using UNION/UNION ALL clause as below   insert row_construct select 'A', 'Garments' union select 'B', 'Sports Equipments' union select 'C', 'Cosmetics'

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]

In this article we will see as how we can query the result from relational table when the input parameter is sending data in the form of string separated by a delimiter. [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]

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]

Suppose we want to keep handy a function that will calculate and give date going backward or forward any number of days from current date. So say, we want to get a date for 100 days prior to current date we can keep this function handy. [More]