Solutions with Practical SQL DBA : Implimentation

Showing posts with label Implimentation. Show all posts

Thursday, 4 October 2012

SQL Server: Sending Email in HTML Table Format Using TSQL

As part of our DBA life , we have to configure many alert or statistical mails which gives an idea about the state of the database server. Let us discuss how we can send an email in HTML table format using TSQL. I am just going to give a sample script and it is self explanatory.

USE MSDB
GO
DECLARE @Reportdate DATE
SET @Reportdate =CONVERT(VARCHAR(10),GETDATE(),121)


/**************************************************************

full backup Header
***************************************************************/


DECLARE @FullBackupHeader VARCHAR(MAX)

SET @FullBackupHeader='<font color=black bold=true size= 5>'
SET @FullBackupHeader=@FullBackupHeader+'<BR /> Full Backup Report<BR />'
SET @FullBackupHeader=@FullBackupHeader+'</font>'
/**************************************************************
full backup report Section
***************************************************************/
DECLARE @FullBackupTable VARCHAR(MAX)
SET @FullBackupTable= CAST( (
SELECT td = name + '</td><td>' + BackupType + '</td><td>'+  FileName + '</td><td>'  +
Startdate + '</td><td>'  + FinishDate+ '</td><td>' + Duration + '</td><td>'  +BackupSize+
'</td><td>'  +CompressionRatio
FROM (

      
       SELECT

sd.name,
ISNULL(db.[Backup Type],'0') AS [BackupType],
ISNULL(DB.Physical_device_name,'No Backup') AS 'FileName',
CAST(ISNULL(DB.backup_start_date,'1900-01-01') AS VARCHAR(24))  AS Startdate ,
CAST(ISNULL(DB.backup_finish_date,'1900-01-01') AS VARCHAR(24)) AS FinishDate,
CAST(ISNULL(DB.Duration,'0') AS VARCHAR(24)) AS Duration,
LEFT(CAST(ISNULL(Backupsize,0)AS VARCHAR(100)),4)+' GB' AS BackupSize,
LEFT(CAST(ISNULL(ratio,0)AS VARCHAR(100)),5)+'%' AS CompressionRatio FROM
SYS.SYSDATABASES sd LEFT JOIN
(
SELECT
bm.media_Set_id,
'FullBackup' AS 'Backup Type',
bm.Physical_device_name ,
backup_start_date,
backup_finish_date,
Duration =
CONVERT(VARCHAR(5),DATEDIFF(second,backup_start_date,backup_finish_date)/60) + ':' +
RIGHT('00' + CONVERT(VARCHAR(5),DATEDIFF(second,backup_start_date,backup_finish_date)%60),2),
database_name,
ROUND((compressed_backup_size)/1024/1024/1024,2) AS Backupsize ,
100-(compressed_backup_size*100/backup_size) AS ratio
FROM msdb..backupmediafamily BM
INNER JOIN msdb..backupset bs ON bm.media_Set_id = bs.media_Set_id
WHERE [type]='D' AND backup_start_date>=DATEADD(dd,-1,@Reportdate) AND
backup_start_date<=@Reportdate
) db ON sd.name=db.database_name
  ) AS d ORDER BY BackupType
  FOR XML PATH( 'tr' ), TYPE ) AS VARCHAR(MAX) )


  
  SET @FullBackupTable= '<table cellpadding="0" cellspacing="0" border="1">'

  + '<tr><th width="10">Database Name</th>
<th  width="20">Backup Type</th>
<th width="80">File Name</th>
<th width="100">Start Date</th>
<th width="40">Finish Date</th>
<th width="40">Duration</th>
<th width="10">Backup Size</th>
<th width="40">Compression Ratio</th></tr>'
  + REPLACE( REPLACE( @FullBackupTable, '<', '<' ), '>', '>' )
+ '</table>'
/**************************************************************
differential backup Header
***************************************************************/
DECLARE @DiffBackupHeader VARCHAR(MAX)
SET @DiffBackupHeader ='<font color=black bold=true size= 5>'
SET @DiffBackupHeader =@DiffBackupHeader +'<BR /> Differential Backup Report<BR />'
SET @DiffBackupHeader =@DiffBackupHeader +'</font>'
/**************************************************************
Differential backup Section
***************************************************************/
DECLARE @DiffBackupTable VARCHAR(MAX)
SET @DiffBackupTable= CAST( (
SELECT td = name + '</td><td>' + BackupType + '</td><td>'+  FileName + '</td><td>'  +
Startdate + '</td><td>'  + FinishDate+ '</td><td>' + Duration + '</td><td>'  +BackupSize+
'</td><td>'  +CompressionRatio
FROM (
SELECT
sd.name,
ISNULL(db.[Backup Type],'0') AS [BackupType],
ISNULL(DB.Physical_device_name,'NO BACKUP') AS 'FileName'  ,
CAST(ISNULL(DBB.backup_start_date,'1900-01-01') AS VARCHAR(24))AS Startdate ,
CAST(ISNULL(DB.backup_finish_date,'1900-01-01') AS VARCHAR(24)) AS FinishDate,
CAST(ISNULL(DB.Duration,'0') AS VARCHAR(24)) AS Duration,
LEFT(CAST(ISNULL(Backupsize,0) AS VARCHAR(100)),6)+' MB' AS BackupSize,
LEFT(CAST(ISNULL(ratio,0)AS VARCHAR(100)),5)+'%' AS CompressionRatio
FROM SYS.SYSDATABASES sd LEFT JOIN
(
SELECT
bm.media_Set_id,
'Differential Backup' AS 'Backup Type',
bm.Physical_device_name ,
backup_start_date,
backup_finish_date,
Duration =
CONVERT(VARCHAR(5),DATEDIFF(second,backup_start_date,backup_finish_date)/60) + ':' +
RIGHT('00' + CONVERT(VARCHAR(5),DATEDIFF(second,backup_start_date,backup_finish_date)%60),2),
database_name,
ROUND((compressed_backup_size)/1024/1024,2) AS Backupsize ,
100-(compressed_backup_size*100/backup_size) AS ratio
FROM msdb..backupmediafamily BM INNER JOIN msdb..backupset bs ON bm.media_Set_id =
bs.media_Set_id
WHERE TYPE='I'  AND backup_start_date>=DATEADD(dd,-1,@Reportdate) AND
backup_start_date<=@Reportdate
) db ON sd.name=db.database_name
) AS d ORDER BY BackupType
  FOR XML PATH( 'tr' ), TYPE ) AS VARCHAR(MAX) )

  
    SET @DiffBackupTable= '<table cellpadding="0" cellspacing="0" border="1">'


/**************************************************************

Empty Section for giving space between table and headings
***************************************************************/
DECLARE @emptybody2 VARCHAR(MAX)
SET @emptybody2=''
SET @emptybody2 = '<table cellpadding="5" cellspacing="5" border="0">'
  +
  '<tr>
<th width="500">               </th>
</tr>'
  + REPLACE( REPLACE( ISNULL(@emptybody2,''), '<', '<' ), '>', '>' )
+ '</table>'
/**************************************************************
Sending Email
***************************************************************/
  DECLARE @subject AS VARCHAR(500)
DECLARE @importance AS VARCHAR(6)
DECLARE @EmailBody VARCHAR(MAX)
SET @importance ='High'
DECLARE @recipientsList VARCHAR(8000)
SELECT @recipientsList = 'Dba@PracticalSqlDba.com;nelsonaloor@PracticalSqlDba.com'


 SET @subject = 'Backup Report of MYSql Instance'

SELECT @EmailBody
=@FullBackupHeader+@emptybody2+@FullBackupTable+@emptybody2+@DiffBackupHeader
+@emptybody2+@DiffBackupTable
EXEC msdb.dbo.sp_send_dbmail
@profile_name ='MyMailProfile',
@recipients=@recipientsList,
@subject = @subject ,
@body = @EmailBody ,
@body_format = 'HTML' ,
@importance=@importance

You can download the formatted script from here. I have implemented this method to send various statistical/alert mail. Hope this will help you.

If you liked this post, do like my page on FaceBook

Thursday, 13 September 2012

SQL Server: String Pattern Matching

It is common scenario that, we might need to extract the data from the SQL server based on some pattern. For example extract all customers information who has a valid PAN card number (XXXXX0000X). SQL server is not very powerful in pattern matching.We can easily implement simple pattern matching but for complicated one we might need to used Regular Expression using CLR integration. In this post let us discuss about the possibilities of pattern matching using SQL server syntax.

To fetch all customers who has valid PAN card number (5 Alphabet 4 numeric 1 Alphabet), we can use the following query.

SELECT * FROM customers
WHERE Pancard LIKE '[A-Z][A-Z][A-Z][A-Z][A-Z][0-9][0-9][0-9][0-9][A-Z]'

To fetch all customer, whose postal code does not have any alphabet.

SELECT * FROM customers
WHERE PostalCode NOT LIKE '%[A-Z]%'

To fetch all customer, whose postal code is alpha numeric

SELECT * FROM customers
WHERE PostalCode LIKE '%[A-Z]%' AND PostalCode LIKE   '%[0-9]%'

To fetch all customer, whose first character of postal code is not vowels

SELECT * FROM customers
WHERE PostalCode LIKE '[^aeiou]%'

To fetch all customer, whose has postal code does not contain special characters  @ ,# ,$ and %

SELECT * FROM customers
WHERE PostalCode NOT LIKE '%[@#$%]%'

To fetch all customers, whose postal code contain the character '%'. We have to use escape character as '%' used for wild card search.

SELECT * FROM customers
WHERE PostalCode LIKE '%@%%' ESCAPE  '@'

To fetch all customers, whose postal code starts with anything but second character is A to D

SELECT * FROM customers
WHERE PostalCode LIKE '_[A-D]%'

These are the common pattern search option available in SQL server . We can mix and match this to make more complicated pattern search.

If you liked this post, do like my page on FaceBook

Monday, 10 September 2012

SQL Server : Usage of OVER Clause

Over clause can be used in association with aggregate function and ranking function. The over clause determine the partitioning and ordering of the records before associating with aggregate or ranking function. Over by clause along with aggregate function can help us to resolve many issues in simpler way. Below is a sample of Over clause along with the aggregate function.

SELECT
SalesOrderID
,p.Name AS ProductName
,OrderQty
,SUM(OrderQty) OVER(PARTITION BY SalesOrderID) AS TotalOrderQty

,AVG(OrderQty) OVER(PARTITION BY SalesOrderID) AS "Avg Qty of Item"
    ,COUNT(OrderQty)OVER(PARTITION BY SalesOrderID) AS "Total Number of Item"

,MIN(OrderQty) OVER(PARTITION BY SalesOrderID) AS "Min order Qty"

,MAX(OrderQty) OVER(PARTITION BY SalesOrderID) AS "Max Order Qty"

FROM Sales.SalesOrderDetail SOD INNER JOIN Production.Product p ON SOD.ProductID=p.ProductID WHERE SalesOrderID IN(43659,43664)

The Partition clause tell the aggregate function that the result should be based on the salesorderid. The output will looks like as given below

TotalOrderQty: is the total quantity of product ordered in the the sales order.
Avg Qty of Item : is the average of order quantity for a salesorder. In our case Totalorderqty for the salesorderid 43659 is 26 and we have twelve order line . So the average quantity per order line = 26/12
Total Number of Item : is the number of product ordered in a salesorder.
Min Order Qty : is the minimum quantity ordered in a salesorder.

Max Order Qty: is the maximum quantity ordered in a salesorder.

The difference between group by and this method is , in group by we will get only the summery part. In our case if we use group by,will get only two records in the output. To get the result as above using group by, we need to write the query as given below:

SELECT

p.name,GRPRESULT.*
FROM sales.SalesOrderDetail SOD INNER JOIN Production.Product p ON SOD.ProductID=p.ProductIDINNER JOIN
(

     SELECT 
     SalesOrderID 
    ,SUM(OrderQty) AS TotalOrderQty
    ,AVG(OrderQty) AS "Avg Qty of Item"

,COUNT(OrderQty)AS "Total Number of Item"
,MIN(OrderQty) AS "Min order Qty"

   ,MAX(OrderQty) AS "Max Order Qty"
FROM Sales.SalesOrderDetail WHERE SalesOrderID               IN(43659,43664)GROUP BY SalesOrderID

) 
GRPRESULT 
ON 
GRPRESULT .SalesOrderID =sod.SalesOrderID

Another interesting part is we can use the over clause with out partition clause which will do an aggregation on entire result set . Let us assume that we have requirement to list all sales order for the year 2008 with sales order number, total amount and Percentage of 2008 sales. It can be achieved easily as given below.

USE AdventureWorks2008
GO

SELECT
SalesOrderNumber,

TotalDue,
(TotalDue*100.)/  SUM(TotalDue) OVER()  AS [%2008Sales]

FROM Sales.SalesOrderHeader WHERE YEAR(OrderDate)=2008

In SQL server 2012 there are more options along with over clause to display cumulative total .

ROW_NUMBER, RANK, DENSE_RANK and NTILE are the ranking function which can be used along with Over clause. For ranking function along with Partition by clause, we can use Order by clause also.To explain the rank function let us have a small table

USE mydb
GO
CREATE TABLE Student
(
Name VARCHAR(10)
)
INSERT INTO Student VALUES ('aa'),('bb'),('cc'),('dd'),('ee')
INSERT INTO Student VALUES ('aa'),('bb'),('cc')
INSERT INTO Student VALUES ('aa'),('bb'),('cc')
INSERT INTO Student VALUES ('dd'),('ee')
INSERT INTO Student VALUES ('dd'),('ee')
INSERT INTO Student VALUES ('ff'),('gg'),('hh')

Row_Number() can be used in many scenarios like to filter the records, remove the duplicated records , implementing paging etc. Let us assume that we need to generate serial number while listing the entries from the student table.
SELECT ROW_NUMBER() OVER (ORDER BY NAME) AS [Si No],* FROM Student

To remove the duplicate entries from the above table
WITH cte_s
AS (
SELECT ROW_NUMBER() OVER (PARTITION BY name ORDER BY NAME) AS [SiNo],* FROM Student
)
DELETE FROM cte_s WHERE [SiNo]<>1
GO
SELECT * FROM Student

Let us assume that we have to divide the student into four group for a game. The NTILE will help us

SELECT NTILE(4) OVER (ORDER BY NAME) AS [Grpno],* FROM Student

As the total number of records 18 is not divisible by 4, it has created two groups with 5 students and other two groups with 4 students.

Let us have slightly different table structure to understand RANK and DENSE_RANK function.

CREATE TABLE StudentMark

(
   Name VARCHAR(10),
   Mark INT

)
INSERT INTO StudentMark VALUES
('aa',10),('bb',14),('cc',16),
('dd',22),('ee',25),('ff',25),

('gg',11),('hh',21),('ii',16)

To assign a rank to student based on their mark we can use the below query

SELECT RANK() OVER (ORDER BY mark DESC) AS 'Rank' ,* FROM StudentMark

The output will looks like as given below:

We can see that rank is assigned based on the position .We have two student with same marks and the student who has next highest marks came in the third position. This listing will be suitable for scenario like an entrance examination result for a total seat of 50. Student who has rank above 50 will not get the admission.

But some scenario we might need to display the actual rank with out any gap.The student who has the second highest mark should have the second rank irrespective of number of student have highest mark. The below query will helps us to do that.

SELECT DENSE_RANK() OVER (ORDER BY mark DESC) AS 'Rank' ,* FROM StudentMark

The output will looks like as given below:

If you liked this post, do like my page on FaceBook

Sunday, 9 September 2012

SQL Server:Output Clause

Couple of days back, one of my colleague came to me asking for help. He is inserting multiple record from a XML to a table which has identity column . He need those newly generated identity values to insert into one more supporting table. This is a very common scenario and it can be achieved by using the OUTPUT clause available in SQL Server 2005 onward.

Till SQL server 2005, the logical/magical tables Updated and Deleted can be accessed only through the triggers.From SQL server 2005 onward it can be accessed as part of INSERT,UPDATE and DELETE statement using the OUTPUT clause. Let us discuss the usage of output clause in this post.

Updated and Deleted are two logical/magical table exists in the SQL server as part of the DML operation.While inserting a new record into a table , the same record along with identity value and default values will be available in the Updated table. During the update operation, the Updated table holds the new data and Deleted table holds a old copy of records. While deleting the records,Deleted table holds a copy of the deleted records. It will be more clear by looking into the below example.

USE MYDB
GO
CREATE TABLE Employee
(

   EMP_Id          INT IDENTITY(1,1)    NOT NULL,
   Emp_Name        VARCHAR(100)        NOT NULL,
   Emp_LastName    VARCHAR(100)        ,
   Emp_DOB         DATE,
   emp_DOJ         DATETIME            DEFAULT GETDATE()
)

GO
INSERT INTO Employee(Emp_Name,Emp_LastName,Emp_DOB) OUTPUT inserted.*VALUES ('William','George','1986-04-12')
GO
UPDATE Employee SET Emp_LastName='John' OUTPUT deleted.*,inserted.*WHERE Emp_id=1
GO
DELETE FROM Employee OUTPUT deleted.* WHERE Emp_id=1

The output of the above statements shows capturing the values of identity column/ column which has default values is much easier using the OUTPUT clause. The output of the OUTPUT clause can be put it in a table or a table variable. Let us see a sample below:

--Inserting the output 
of output clause into TableVariable

DECLARE 
@Employee TABLE (Emp_id INT,Emp_name VARCHAR(100),Emp_DOJ DATETIME)

INSERT INTO Employee(Emp_Name,Emp_LastName,Emp_DOF)

OUTPUT inserted.emp_id,inserted.emp_name,inserted.emp_DOJ 
INTO @Employee VALUES 
('William','George','1986-04-12')

SELECT * FROM @Employee GO
--Inserting the 
output of output clause into Table for maintainging the history

CREATE TABLE Employee_History
(
   History_id          INT 
IDENTITY(1,1)   NOT NULL PRIMARY 
KEY,
   EMP_Id              INT                 NOT 
NULL ,
   Emp_Name            VARCHAR(100)        NOT 
NULL,
   Emp_LastName        VARCHAR(100)        ,
   
Emp_DOB             
DATE                ,
   emp_DOJ             DATETIME            ,
   
InsertdDate         DATETIME            )GO
UPDATE   Employee 

SET Emp_LastName='John'

OUTPUT 
deleted.*,GETDATE() INTO  Employee_History(emp_id,Emp_Name,Emp_LastName,Emp_DOB,Emp_DOJ,InsertdDate)WHERE Emp_id=1