Sending Mail using SQL Server Express Edition

In SQL server standard and enterprise edition,A DataBase mail functionality in built to sent mail.But in SQL Server express edition,You need either use CLR integration or configure SQL Mail using MSDB system database.

Here I am discussing sending mail using MSDB system database.By default the MSDB database installed when you install SQL Server.The below tables used to confiure sysmail account.

To configure SQL mail we need to follow below steps.

1. Create Sysmail Account

Use sysmail_add_account_sp stored procedure of MSDB database to configure sysmail   account.

EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = 'MailTest',
@description = 'Sent Mail using MSDB',
@email_address = 'umashankar@queryingsql.com',
@display_name = 'umashankar',
@username='umashankar@queryingsql.com',
@password='password',
@mailserver_name = 'mail.queryingsql.com'

      

2. Creating Database Profile
   
   

Use sysmail_add_profile_sp stored procedure of MSDB database to configure Database Profile.

EXECUTE msdb.dbo.sysmail_add_profile_sp @profile_name = 'MailTest',
@description = 'Profile used to send mail'

3. Add database Mail account to profile

Use sysmail_add_profileaccount_sp stored procedure of MSDB database to map database mail account to Profile.

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'MailTest',
@account_name = 'MailTest',
@sequence_number = 1

4. Grants permission for a database user or role to use a Database Mail profile.

To Grants permission for a database user or role to use a Database Mail profile use                     sysmail_add_principalprofile_sp.

EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = 'MailTest',
@principal_name = 'public',
@is_default = 1 ;

--A principal_name of 'public' makes this profile a public profile, granting access to all principals in the database.

5. You can query to test data
   
   
   

   SELECT *FROM msdb.dbo.sysmail_account
   SELECT *FROM msdb.dbo.sysmail_configuration
   SELECT *FROM msdb.dbo.sysmail_principalprofile
   SELECT *FROM msdb.dbo.sysmail_profile
   SELECT *FROM msdb.dbo.sysmail_profileaccount
   SELECT *FROM msdb.dbo.sysmail_profileaccount
   

   
   

6. Send Mail using Created Profile
   
   
   
   

exec msdb.dbo.sp_send_dbmail @profile_name = 'MailTest', @recipients = 'receiver@queryingsql.com', @subject = 'Mail Test', @body = 'Mail Sent Successfully', @body_format = 'text'

If all things are going right,Mail sent successfully.But when I tried I found this error.

Msg 15281, Level 16, State 1, Procedure sp_send_dbmail, Line 0

SQL Server blocked access to procedure 'dbo.sp_send_dbmail' of component 'Database Mail XPs' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Database Mail XPs' by using sp_configure. For more information about enabling 'Database Mail XPs', see "Surface Area Configuration" in SQL Server Books Online.

7. This error occured due to 'Database Mail XPs' disabled.To enable this use this code

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Database Mail XPs', 1;
GO
RECONFIGURE
GO

8. Try to send mail again.I hope it works successfully.But If you tried this using Microsoft Exchange Server Mail ID,It will not work properly.Because maybe This is turned out to be an issue with a rule on the exchange server.

Where to use SQL Server Cursor

Where to Use Cursor

Cursor can be used when you need to manipulate data in a set on a row-by-row basis, however you can also use T-SQL WHILE loop, CASE expression and some system defined stored procedure like sp_MSforeachdb, sp_MSforeachdb etc.

IN SQL Server the cursor can be implemented by 6 step process as:

1. Declare cursor

DECLARE ins_cursor CURSOR

FOR

    Select statement…………….

2. Open cursor

OPEN ins_cursor

3. Fetch row from the cursor

FETCH NEXT FROM ins_cursor 

INTO @fileno,……….variable list

4. Process fetched row

WHILE @@FETCH_STATUS = 0

BEGIN

….

…..

…

FETCH NEXT FROM ins_cursor 

INTO @fileno@fileno,……….variable list

END

5. Close cursor

CLOSE ins_cursor

6. Deallocate cursor

DEALLOCATE ins_cursor;

I have used cursor first time when I need to insert data from one table of one database into tables of another database. So I am explaining same example here

--declare variables to use in logic

DECLARE @RID INT

DECLARE @FILENO VARCHAR(50),@ISSUEDATE DATETIME,@REQDATE DATETIME,@RCN VARCHAR(50),@FILEREMARKS VARCHAR(200)

--decalare cusrsor

DECLARE INS_CURSOR CURSOR

    FOR

    --select statement

    SELECT FILENO,REQCONTROLNO,CONVERT(DATETIME,REPLACE(REQDATE,'-','/'),103),CONVERT(DATETIME,REPLACE(ISSUEDATE,'-','/'),103) ,REMARKS FROM ISSUE WHERE RECEIVEDDATE IS NULL

    --open cursor

OPEN INS_CURSOR

--Fetch row from the cursor

FETCH NEXT FROM INS_CURSOR

INTO @FILENO,@RCN,@REQDATE,@ISSUEDATE,@FILEREMARKS

--process fetched row

WHILE @@FETCH_STATUS = 0

BEGIN

--insert into first table

INSERT INTO FISS1.DBO.FILEREQUEST(REQDATE,REQSTATUS,RCN,CORDSTATUS,FILEREMARKS)

VALUES (@REQDATE,1,@RCN,1,@FILEREMARKS)

----assigning primary key(identity) value

SELECT @RID=@@IDENTITY

--insert into 2^nd table with foreign key @rid

INSERT INTO FISS1.DBO.ISSUE(REQID,FILENO,PRIORITY,PURPOSE,ISSUEDATE,STAUS)

VALUES(@RID,@FILENO,1,'GENERAL',@ISSUEDATE,1)

FETCH NEXT FROM INS_CURSOR INTO @FILENO,@RCN,@REQDATE,@ISSUEDATE,@FILEREMARKS

END

--close cursor

CLOSE INS_CURSOR;

DEALLOCATE INS_CURSOR;

Cursor Recommendations:

·         It is better to avoid using cursor because they consume memory for execution, so performance is less.

·         If you using cursor, then you should always close cursor after using it.

Further Reading

http://msdn.microsoft.com/en-us/library/ms181441.aspx

Prev---> Handling Null Values in SQL Server

Frequently asked SQL Server Queries

Data to test:

CREATE TABLE [dbo].[emp](

      [emp_id] [nchar](10) NULL,

      [emp_name] [nchar](10) NULL,

      [salary] [int] NULL

) ON [PRIMARY]

 insert emp ( emp_id,emp_name,salary )  select '1','ABC',-20000

 insert emp ( emp_id,emp_name,salary )  select '2','XYZ',-5000

 insert emp ( emp_id,emp_name,salary )  select '3','PQR',-7800

 insert emp ( emp_id,emp_name,salary )  select '4','AK ',10000

 insert emp ( emp_id,emp_name,salary )  select '5','MP ',19000

 insert emp ( emp_id,emp_name,salary )  select '6','KHJ',11000

 insert emp ( emp_id,emp_name,salary )  select '7','LTR',13000

 insert emp ( emp_id,emp_name,salary )  select '8','MAN',14000

select *from emp.

emp_id                emp_name        salary

1              ABC                     -20000

2              XYZ                         -5000

3              PQR                       -7800

4              AK                          10000

5              MP                         19000

6              KHJ                         11000

7              LTR                         13000

8              MAN                     14000

·        How to get nth Highest Salary from Emp Table

SELECT * FROM  emp a WHERE 3 = (SELECT COUNT(*) FROM  emp b WHERE a.salary <= b.salary) ;

emp_id      emp_name    salary

7           LTR         13000

for nth salary

SELECT * FROM  emp a WHERE nth= (SELECT COUNT(*) FROM  emp b WHERE a.salary <= b.salary) ;

·        How to delete duplicate rows from a table

--insert auto increment id if not in table

ALTER TABLE emp ADD autoid INT IDENTITY(1,1)

--then query  returns autoid for unique rows

SELECT MAX(autoid)

FROM emp

GROUP BY emp_id, emp_name, salary

--delete duplicate rows

DELETE

FROM emp

WHERE autoid NOT IN

(

SELECT MAX(autoid)

FROM emp

GROUP BY emp_id, emp_name, salary)

·        Select alternate rows from Emp table

--Using CTE

WITH CTE AS

(

SELECT ROW_NUMBER()OVER (ORDER BY emp_id)AS ROW,* FROM emp

)

SELECT * FROM CTE WHERE ROW%2=0

--Using SubQuery

select a.* from (SELECT ROW_NUMBER()OVER (ORDER BY emp_id)AS ROW,* FROM emp) a where ROW%2=0

Output:

ROW   emp_id      emp_name    salary      autoid

2     2           XYZ         -5000       10

4     4           AK          10000       4

6     6           KHJ         11000       6

8     8           MAN         14000       8

·        Select nth record from Emp table

select a.* from (SELECT ROW_NUMBER()OVER (ORDER BY emp_id)AS ROW,* FROM emp) a where ROW=3

Output:

ROW      emp_id                emp_name        salary         autoid

3              3              PQR                                       -7800                     3

·        Suppose a column has some -Ve values and +Ve  values.Find the sum of -Ve numbers and the sum of the +Ve numbers.

data:

SELECT

SUM(CASE WHEN salary < 0 THEN salary ELSE 0 END) Sum_of_negative_amount,

SUM(CASE WHEN salary > 0 THEN salary ELSE 0 END) Sum_of_positive_amount

FROM emp;

Sum_of_negative_amount  Sum_of_positive_amount

-32800                  6000

• Suppose You have a table

   create table tbltests(id int ,id1 int)

  insert into tbltests(id,id1) values

  (1,2),(2,1),(3,null),(4,5),(5,4)

  
  

  Table Data:

   1  2

   2  1

   3  NULL

   4  5

   5  4

   Fetch data like

  id            id1
  
  1              2
  
  3              NULL
  
  4              5
  
  Query:
  
  
  
  SELECT t1.id,t1.id1
  FROM tbltests t1
  WHERE NOT EXISTS(SELECT * FROM tbltests t2
                   WHERE t2.id = t1.id1
                     AND t2.id1 = t1.id
                     AND t2.id < t2.id1)
  
  

Frequently asked Scripts during Interviews

1. Select no of Months between two dates:

SELECT

    DATEDIFF(MONTH, '4/1/2011', '01/23/2013') +

    CASE

        WHEN DAY('4/1/2011') < DAY('01/23/2013')

        THEN 1

        ELSE 0

    END

Output:

----------------

22

2. What will be the output of following script

CREATE TABLE dbo.t1

(

 id INT NOT NULL IDENTITY (1, 1),

 name VARCHAR(100)

);

INSERT  INTO dbo.t1

        (name)

VALUES  ('sonu');

DBCC CHECKIDENT ('dbo.t1', RESEED, 24);

INSERT  INTO dbo.t1

        (name)

VALUES  ('queryingsql');

SELECT  id

FROM    dbo.t1;

Output:

---------------------------

1

25

3. Create a query that display the Emp name and indicate the amounts of their annual salaries with asterisks. Each asterisk signifies a thousand rupees. Sort the data in descending order of salary. As for example if Emp name sonu have salary 11000. Then output should be Sonu***********.

select FirstName+SUBSTRING('****************************

********************************************************

********************************************************

********************************************************

*************************************************',1,ROUND(salary/1000,0))

    as Employee_and_salary ,salary from Employee order by salary desc;

Output:

---------------------------------------------------------------

Employee_and_salary    salary

sonu***********                         11000

mohit**********                         10000

mohit**********                         10000

monu*********                            9000

More script coming soon...........