Sending Email Using Stored Procedures in Sql Server

Remember we will be using pre defined Stored procedure to send the mails. First of all we need to set up an account with the credentials required by the server to send the mails. Usually the mail is sent through SMTP, Simple Mail Transfer Protocol. The settings would depend on the server your aplication demands. Remember the configuration needs to be valid.
Create a Database Account:-

EXEC msdb.dbo.sysmail_add_account_sp
    @account_name = 'SendEmailSqlDemoAccount'
  , @description = 'Sending SMTP mails to users'
  , @email_address = 'suraj.0241@gmail.com'
  , @display_name = 'Suraj Sahoo'
  , @replyto_address = 'suraj.0241@gmail.com'
  , @mailserver_name = 'smtp.gmail.com'
  , @port = 587
  , @username = 'XXXXXX'
  , @password = 'XXXXXX'
Go

Please use proper credentials and server settings in order to successfully deliver the mails, else they will fail and be queued.
Nextstep is to create a profile which would be used to tconfigure the database mail. The sp would look like below:

EXEC msdb.dbo.sysmail_add_profile_sp
    @profile_name = 'SendEmailSqlDemoProfile'
  , @description = 'Mail Profile description'
Go

This profile would be used in order to set the mail configuration and the emails and sent.
Next step is to map the account to the profile. This will let the profile know, which account credentials it need to work for sending successfully.
That would look like:

-- Add the account to the profile
EXEC msdb.dbo.sysmail_add_profileaccount_sp
    @profile_name = 'SendEmailSqlDemo'
  , @account_name = 'SendEmailSql'
  , @sequence_number = 1
GO

Thus, we are all set to send the successly emails. The mail sending look up snippet would look like below:

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

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'SendEmailSqlDemo2'
  , @recipients = 'suraj.0241@gmail.com'
  , @subject = 'Automated Test Results (Successful)'
  , @body = 'The stored procedure finished successfully.'
  , @importance ='HIGH' 
GO

The stored procedured being used are sometimes vulnerable to not getting executed. So Try catch block and Begin and End Transaction are mandatory in few Stored Procedures.
Lets take an example here,
Suppose we have a SELECT INSERT query using Stored Procedure, so what happens is we are selecting and inserting from 4 tables, lets say
Users | UserLogin | UserEmployment | Departments
For each new screen creation we are manipulating and selecting the users based on their PK and inserting again into the same tables with a different FK, representing the particular screen. The query would look like below:-

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

BEGIN TRY
  BEGIN TRAN
 INSERT INTO
   dbo.[User]
 SELECT
    us.UserName,
 us.UserAddress,
 us.UserPhone,
    @fkScreenID
 FROM
   dbo.[User] as us
 WHERE
   UserID= @userID
 COMMIT TRAN
    END TRY
   BEGIN CATCH
  ROLLBACK TRAN
  END
  END CATCH  //Similarly for other tables as well we continue. Its is better to add the Try Catch to whole SP Executing Block

Here, when the transaction in case fails, it would move into the Catch block and there we can have the email sending procedure so as to get a notification regarding the success or failure and reason and where it failed. This would be so helpful for any developer.

Troubleshooting Mails

There are also stored procedure to let us know if the mails are successful, failed or remained in the queue. This is fascinating feature. .
To check for the mails which were successfully sent and delivered, we run the below query:

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

select * from msdb.dbo.sysmail_sentitems

Some of the columns it returns are


In the second image you can see we have the sent_status as sent, which states the mail has been successfully sent.
To check for the unsent mails which could not be sent, we run the below query:

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

select * from msdb.dbo.sysmail_unsentitems

TO check for the failed mails, which will not even be retried to be sent from the queue, we run the below query:-

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

select * from msdb.dbo.sysmail_faileditems

For more details on the failure along with the reason, the trouble shoot query would look like:

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

SELECT items.subject,
    items.last_mod_date
    ,l.description FROM msdb.dbo.sysmail_faileditems as items
INNER JOIN msdb.dbo.sysmail_event_log AS l
    ON items.mailitem_id = l.mailitem_id
GO

The results look like below:

The error description above is like “No Such Host” Error. This error usually comes when we have some smtp server connection settings wrong. We need to troubleshoot that on our own and recheck the settings credentials and then try. If then it does not seem to work, we need to look for the DNS server settings and retry with the configuration again. Nothing to worry for this though

SQL INDEX1

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

 Use while 

CREATE TABLE Sales(
 ID INT IDENTITY(1,1)
,ProductCode VARCHAR(20)
,Price FLOAT(53)
,DateTransaction DATETIME);

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

CREATE PROCEDURE InsertIntoSales
AS 
SET NOCOUNT ON
BEGIN
DECLARE @PC VARCHAR(20)='A12CB'
DECLARE @Price INT = 50
DECLARE @COUNT INT = 0
      WHILE @COUNT<200000
      BEGIN
      SET @PC=@PC+CAST(@COUNT AS VARCHAR(20))
      SET @Price=@Price+@COUNT
      INSERT INTO Sales VALUES (@PC,@Price,GETDATE())
      SET @PC='A12CB'
      SET @Price=50
      SET @COUNT+=1
      END
END
--------------------------------------
EXEC InsertIntoSales 

--------------STUFF-----------------------------------

SELECT STUFF('abcdef', 2, 3, 'ijklmn');
GO 

----------------------CHOOSE -------------------------------

CHOOSE ( index, val_1, val_2 [, val_n ] ) 

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

SELECT CHOOSE ( 3, 'Manager', 'Director', 'Developer', 'Tester' ) AS Result;

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

USE AdventureWorks2012;
GO
SELECT ProductCategoryID, CHOOSE (ProductCategoryID, 'A','B','C','D','E') AS Expression1
FROM Production.ProductCategory; 

-----------------------Random--------------------------------------

DECLARE @counter smallint;
SET @counter = 1;
WHILE @counter <= 5
   BEGIN
      SELECT RAND() Random_Number
      SET @counter = @counter + 1
   END;
GO 

----------------------IIF-----------------------------------------------

IIF ( boolean_expression, true_value, false_value )

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

DECLARE @a int = 45, @b int = 40;
SELECT IIF ( @a > @b, 'TRUE', 'FALSE' ) AS Result;

--------------SIN-------------------------------------- 

SIN ( float_expression ) 

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

DECLARE @angle float;
SET @angle = 45.175643;
SELECT 'The SIN of the angle is: ' + CONVERT(varchar,SIN(@angle));
GO

-------------UNICODE --------------------------

UNICODE ( 'ncharacter_expression' ) 

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

DECLARE @nstring nchar(12);
SET @nstring = N'Åkergatan 24';
SELECT UNICODE(@nstring), NCHAR(UNICODE(@nstring));

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

What are the difference between DDL, DML and DCL commands?

 

DDL

Data Definition Language (DDL) statements are used to define the database structure or schema. Some examples:

• CREATE - to create objects in the database
• ALTER - alters the structure of the database
• DROP - delete objects from the database
• TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed
• COMMENT - add comments to the data dictionary
• RENAME - rename an object

DML

Data Manipulation Language (DML) statements are used for managing data within schema objects. Some examples:

• SELECT - retrieve data from the a database
• INSERT - insert data into a table
• UPDATE - updates existing data within a table
• DELETE - deletes all records from a table, the space for the records remain
• MERGE - UPSERT operation (insert or update)
• CALL - call a PL/SQL or Java subprogram
• EXPLAIN PLAN - explain access path to data
• LOCK TABLE - control concurrency

DCL

Data Control Language (DCL) statements. Some examples:

• GRANT - gives user's access privileges to database
• REVOKE - withdraw access privileges given with the GRANT command

TCL

Transaction Control (TCL) statements are used to manage the changes made by DML statements. It allows statements to be grouped together into logical transactions.

• COMMIT - save work done
• SAVEPOINT - identify a point in a transaction to which you can later roll back
• ROLLBACK - restore database to original since the last COMMIT
• SET TRANSACTION - Change transaction options like isolation level and what rollback segment to use