Today, I will show you how to upload excel file, read Excel file data,  save Excel file data & import into SQL Server using SQLBULK in ASP.NET.

First step, you must create the excel file. And then create a SQL table in database like the following picture:

Next step, add the code in "Default.aspx"

<asp:FileUpload ID="fupUpload" runat="server" />
<asp:Button ID="btnImport" Font-Bold="true" ForeColor="White"
BackColor="#136671" Height="23px" runat="server" Text="Import Excel Data"
onclick="btnImport_Click" />

Now, write the following code in "Default.aspx.cs"
Add these NameSpace
using System.IO;
using System.Data.OleDb;
using System.Data;

Write the code in Click Event of Import Button:
protected void btnImport_Click(object sender, EventArgs e)
{
 string strFilepPath;
 DataSet ds = new DataSet();
 string strConnection = ConfigurationManager.ConnectionStrings
                          ["connectionString"].ConnectionString;
 if (fupUpload.HasFile)
 {
  try
  {
    FileInfo fi = new FileInfo(fupUpload.PostedFile.FileName);
    string ext = fi.Extension;
    if (ext == ".xls" || ext == ".xlsx")
    {
     string filename = Path.GetFullPath(fupUpload.PostedFile.FileName);
     string DirectoryPath = Server.MapPath("~/UploadExcelFile//");
     strFilepPath = DirectoryPath + fupUpload.FileName;     
     Directory.CreateDirectory(DirectoryPath);
     fupUpload.SaveAs(strFilepPath);  
     string strConn = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source="
                      + strFilepPath + ";Extended Properties=\"Excel 12.0
                      Xml;HDR=YES;IMEX=1\"";
     OleDbConnection conn = new OleDbConnection(strConn);
     conn.Open();    
     OleDbCommand cmd = new OleDbCommand("SELECT * FROM [Sheet1$]", conn);
     OleDbDataAdapter da = new OleDbDataAdapter(cmd);
     da.Fill(ds);
     DeleteExcelFile(fupUpload.FileName); // Delete File Log
     SqlBulkCopy sqlBulk = new SqlBulkCopy(strConnection,
                                   SqlBulkCopyOptions.KeepIdentity);
     sqlBulk.DestinationTableName = "Table_1";
     sqlBulk.WriteToServer(ds.Tables[0]);
     conn.Close();
     sqlBulk.Close();     
     ScriptManager.RegisterStartupScript(Page, GetType(), "script1", 
        "alert('Excel file successfully imported into DB');", true);
     return;
    }    
    else
    {
      ScriptManager.RegisterStartupScript(Page, GetType(), "script1",  
                    "alert('Please upload excel file only');", true);
     return;
    }
  }  
  catch (Exception ex)
   {
    DeleteExcelFile(fupUpload.FileName);      
    ScriptManager.RegisterStartupScript(Page, GetType(), "script1",  
      "alert('error occured: " + ex.Message.ToString() + "');", true);
    return;
   }
  } 
 else
  {
    ScriptManager.RegisterStartupScript(Page, GetType(), "script1",  
                        "alert('Please upload excel file');", true);
   return;
  }
}
protected void DeleteExcelFile(string Name)
{             
 if (Directory.Exists(Request.PhysicalApplicationPath +  
                                           "UploadExcelFile\\"))
   {     
    string[] logList = Directory.GetFiles(Request.PhysicalApplicationPath
                       + "UploadExcelFile\\", "*.xls");
     foreach (string log in logList)
      {        
        FileInfo logInfo = new FileInfo(log);
        string logInfoName = logInfo.Name.Substring(0, 
                             logInfo.Name.LastIndexOf('.'));
        if (logInfoName.Length >= Name.Length)
         {           
          if (Name.Equals(logInfoName.Substring(0, Name.Length)))
           {
             logInfo.Delete();
           }
         }
      }
   }
}

I hope it works for you! Good luck.

HostForLIFE.eu SQL Server 2014 with Free ASP.NET Hosting

Try our SQL Server 2014 with Free ASP.NET Hosting today and your account will be setup soon! You can also take advantage of our Windows & ASP.NET Hosting support with Unlimited Domain, Unlimited Bandwidth, Unlimited Disk Space, etc.

You know that RAND() function in SQL Server generates a random float value 0 through 1 (excluding 0 and 1). In this article, I will show you how to generate different random number for each group using RAND() Function.

First, let's us create the following dataset:

CREATE TABLE #random(no INT)
INSERT INTO #random(no)
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 3

If you want to generate a random value, you can use the following code:

SELECT no, RAND() AS random_number FROM #random

This code will result to:
no random_number
 1 0.370366365964781
 1 0.370366365964781
 1 0.370366365964781
 2 0.370366365964781
 3 0.370366365964781
 3 0.370366365964781

Please note that when you execute it , you may get different random number than what I got for column 2 but all will be same. What if you want to generate random number in such a way that it is reset to each column value (in this case the column is no)? Did you know that RAND() accepts a seed value as well?

If you execute the following code:

SELECT no, RAND() AS random_number,RAND(no) AS random_number_reset FROM #random

Then, it will comes to the result:
no random_number random_number_reset
 1 0.58334760467751 0.713591993212924
 1 0.58334760467751 0.713591993212924
 1 0.58334760467751 0.713591993212924
 2 0.58334760467751 0.713610626184182
 3 0.58334760467751 0.71362925915544
 3 0.58334760467751 0.71362925915544

Please note that when you execute it , you may get different random number than what I got for column2 2 and 3. If you notice the result the values of second column is same for all rows, whereas the values of third column is same withing a group (column no), and different across groups. So if you want to generate random number differently for each group, you can make use of this method.

HostForLIFE.eu SQL Server 2014 with Free ASP.NET Hosting
Try our SQL Server 2014 with Free ASP.NET Hosting today and your account will be setup soon! You can also take advantage of our Windows & ASP.NET Hosting support with Unlimited Domain, Unlimited Bandwidth, Unlimited Disk Space, etc.

When you found that your TempDB log file is filled up, you will come across following errors in log file:
Source: MSSQLSERVER
Event ID: 17052
Description: The LOG FILE FOR DATABASE 'tempdb' IS FULL.
Back up the TRANSACTION LOG FOR the DATABASE TO free
up SOME LOG SPACE

So, you have to move the TempDB to different drive. Moving the TempDB to another drive will help the growth of the file. Sometimes user also moves to different drive due to performance reasons as keeping TempDB on a different drive from your main database helps. By this article, I'm gonna show you how to move TempDB from one drive to another drive.

There are major two reasons why TempDB needs to move from one drive to other drive:
1) TempDB grows big and the existing drive does not have enough space.
2) Moving TempDB to another file group which is on different physical drive helps to improve database disk read, as they can be read simultaneously.

You can follow the direction below exactly to move database and log from one drive (c:) to another drive (d:) and (e:)

Make sure that TempDB is set to autogrow and do not set a maximum size for TempDB. If the current drive is too full to allow autogrow events, then arrange a bigger drive, or add files to TempDB on another device (using ALTER DATABASE as described below and allow those files to autogrow).

1. Open Query Analyzer and connect to your server. Run this script to get the names of the files used for TempDB:

USE TempDB
GO
EXEC sp_helpfile
GO

Results will be something like:
name fileid filename filegroup size
——- —— ————————————————————– ———- ——-
tempdev 1 C:Program FilesMicrosoft SQL ServerMSSQLdatatempdb.mdf PRIMARY 16000 KB
templog 2 C:Program FilesMicrosoft SQL ServerMSSQLdatatemplog.ldf NULL 1024 KB

2. Along with other information related to the database, the names of the files are usually tempdev and demplog by default. These names will be used in next statement. Run following code, to move mdf and ldf files:

USE master
GO
ALTER DATABASE TempDB MODIFY FILE
(NAME = tempdev, FILENAME = 'd:datatempdb.mdf')
GO
ALTER DATABASE TempDB MODIFY FILE
(NAME = templog, FILENAME = 'e:datatemplog.ldf')
GO

The definition of the TempDB is changed. However, no changes are made to TempDB till SQL Server restarts. Please stop and restart SQL Server and it will create TempDB files in new locations.

HostForLIFE.eu SQL Server 2014 with Free ASP.NET Hosting
Try our SQL Server 2014 with Free ASP.NET Hosting today and your account will be setup soon! You can also take advantage of our Windows & ASP.NET Hosting support with Unlimited Domain, Unlimited Bandwidth, Unlimited Disk Space, etc.

In this post, I would like to show you how to change compatibility of database to SQL Server 2014. Maybe you have installed SQL Server 2014 and attached a database file from previous version of SQL Server. Right after attaching database, you were not able to work with the latest features of Cardinality Estimation. This problem is caused by the database compatibility was still set of the earlier version of SQL Server. To use most of the latest features of SQL Server 2014, you have to change the compatibility level of the database to the latest version.

Here are two different ways how we can change the compatibility of database to SQL Server 2014’s version:

1. Using Management Studio

For this method first to go database and right click over it. Now select properties.

On this below screen user can change the compatibility level to 120:

2. Using T-SQL Script

You can execute following script and change the compatibility settings to 120:
USE [master]
GO
ALTER DATABASE [AdventureWorks2012] SET COMPATIBILITY_LEVEL = 120
GO

Congratulations, you're done!

HostForLIFE.eu SQL Server 2014 with Free ASP.NET Hosting
Try our SQL Server 2014 with Free ASP.NET Hosting today and your account will be setup soon! You can also take advantage of our Windows & ASP.NET Hosting support with Unlimited Domain, Unlimited Bandwidth, Unlimited Disk Space, etc. You will not be charged a cent for trying our service. Once your trial period is complete, you decide whether you'd like to continue.

In this tutorial, I will show you how to  to encrypt/decrypt string in SQL Server. First step, you must write the following DB Script:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        <Author,,Name>
-- Create date: <Create Date,,>
-- Description:   <Description,,>
-- =============================================
ALTER PROCEDURE Enc_Dec
    @username varchar(20),
      @password varchar(10)
AS
BEGIN
      SET NOCOUNT ON;
      DECLARE @encrypt_val VARBINARY(200)
    SELECT @encrypt_val = EncryptByPassPhrase('xxx', @password)
    SELECT @encrypt_val
    INSERT INTO reg_users (username,r_pwd)VALUES(@username ,CONVERT(VARCHAR(200), @encrypt_val, 1))
    SELECT CONVERT(VARCHAR(200),DecryptByPassPhrase('xxx', @encrypt_val ))
END
GO

The output of the above code as shown in the following picture:

HostForLIFE.eu SQL Server 2014 with Free ASP.NET Hosting

Try our SQL Server 2014 with Free ASP.NET Hosting today and your account will be setup soon! You can also take advantage of our Windows & ASP.NET Hosting support with Unlimited Domain, Unlimited Bandwidth, Unlimited Disk Space, etc. You will not be charged a cent for trying our service. Once your trial period is complete, you decide whether you'd like to continue.

In this tutorial, I will show you how to handling NULL Character \x00 when Exporting to File Using BCP in SQL Server.

This article talks about the conduct of the BCP utility when removing information from SQL Server to records and all the more particularly to CSV documents. Imagine that you have the underneath table:

As you can see on the above picture, records 2 and 3 contain null values. Now, you need to export the table contents to CSV using BPC: bcp "SELECT [code],[description],[price] FROM [TestDB1].[dbo].[product]" queryout "c:\tmp\testExtract.csv" -c -C 1253 -t ; -S ".\SQL2K14" -T

And here is the output from the code:

As should be obvious on the above screenshot, the output appears to be correct. The records' NULL value have been supplanted with unfilled strings. Now, consider that the first table, rather than NULL qualities has unfilled strings:

Let's try again to export the new table contents to CSV using BPC:
bcp "SELECT [code],[description],[price] FROM [TestDB1].[dbo].[product]" queryout "c:\tmp\testExtract.csv" -c -C 1253 -t ; -S ".\SQL2K14" –T

And here is the output:

As should be obvious, the unfilled strings have been replaced by NULLs which relate to the hexadecimal character \x00. It appears from the above sample, that the queryout keyword has the same behavior regarding null and empty strings.

Presently, if you are going to nourish the CSV document to another application for parsing and handling it, if the application does not expressly handle conceivable events of the NULL character then most probably an error will be occurred. To this end, dependably have as a top priority the above conduct when extracting data to files using BCP.

HostForLIFE.eu SQL Server 2014 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes. We have customers from around the globe, spread across every continent. We serve the hosting needs of the business and professional, government and nonprofit, entertainment and personal use market segments.

In this post, I will tell you about rename column name in In SQL Server. Now, I’ve got a table “tblRename” in which there are the two columns Id and Name.

But by mistake I misspelled Name to “Names” and now I want to change the column name to the correct name -> “Name”. So, how is it possible to rename the column name without dropping the table?

We can use a System Stored Procedure sp_rename.
EXEC sp_rename 'tblRename.Names', 'Name','column'

sp_rename is the Stored Procedure.The first value is the tblRename that is the table in which the column is to be renamed, as in "Names" -> tblRename.Names. The second value is the new segment name that we need to change it to, in other words Name. The third value is the sort and here the sort is segment.
Execute the previous query.

Finally, the column name is now changed to Name. But when we execute our query we get an error message:

Along these lines, if there are any Stored Procedures and scripts indicating this tblRename table in which you have determined the "Names" segment then that Stored Procedure or script won't come being used any longer in light of the fact that now there is no section present with a name "Names". In this way, in the event that you need to change the segment name then do it before making any important Stored Procedures or scripts.

HostForLIFE.eu SQL Server 2014 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes. We have customers from around the globe, spread across every continent. We serve the hosting needs of the business and professional, government and nonprofit, entertainment and personal use market segments.

Here's a convenient script that is a piece of my tool stash all over I go. Scripts out and identifies basic database level security objects, and creates a TSQL proclamation to reproduce the items. Note that this script just chips away at SQL 2005 or above. This is a long way from an authority script.

I invite to all input on these scripts. I likewise have a server-level security script here.
--Run the below on each database for database-level security. 
SELECT DB_NAME() as Database_Name 
--Database Level Roles
SELECT DISTINCT
     QUOTENAME(r.name) as database_role_name, r.type_desc, QUOTENAME(d.name) as principal_name, d.type_desc
,    TSQL = 'EXEC sp_addrolemember @membername = N''' + d.name COLLATE DATABASE_DEFAULT + ''', @rolename = N''' + r.name + ''''
FROM sys.database_role_members AS rm
inner join sys.database_principals r on rm.role_principal_id = r.principal_id
inner join sys.database_principals d on rm.member_principal_id = d.principal_id
where d.name not in ('dbo', 'sa', 'public') 
--Database Level Security
SELECT     rm.state_desc
     ,   rm.permission_name
    ,   QUOTENAME(u.name) COLLATE database_default
    ,   u.TYPE_DESC
     ,   TSQL = rm.state_desc + N' ' + rm.permission_name + N' TO ' + cast(QUOTENAME(u.name COLLATE DATABASE_DEFAULT) as nvarchar(256))  
FROM sys.database_permissions AS rm
     INNER JOIN
     sys.database_principals AS u
     ON rm.grantee_principal_id = u.principal_id
WHERE rm.major_id = 0
and u.name not like '##%'
and u.name not in ('dbo', 'sa', 'public')
ORDER BY rm.permission_name ASC, rm.state_desc ASC
 --Database Level Explicit Permissions
SELECT     perm.state_desc
    , perm.permission_name
    ,   QUOTENAME(USER_NAME(obj.schema_id)) + '.' + QUOTENAME(obj.name)
       + CASE WHEN cl.column_id IS NULL THEN SPACE(0) ELSE '(' + QUOTENAME(cl.name COLLATE DATABASE_DEFAULT) + ')' END AS [Object]
     , QUOTENAME(u.name COLLATE database_default) as Usr_Name
    ,   u.type_Desc
    , obj.type_desc
   ,  TSQL = perm.state_desc + N' ' + perm.permission_name
           + N' ON ' + QUOTENAME(USER_NAME(obj.schema_id)) + '.' + QUOTENAME(obj.name)
           + N' TO ' + QUOTENAME(u.name COLLATE database_default)
FROM sys.database_permissions AS perm
     INNER JOIN
     sys.objects AS obj
     ON perm.major_id = obj.[object_id]
     INNER JOIN
     sys.database_principals AS u
     ON perm.grantee_principal_id = u.principal_id
     LEFT JOIN
    sys.columns AS cl
     ON cl.column_id = perm.minor_id AND cl.[object_id] = perm.major_id
where
     obj.name not like 'dt%'
and obj.is_ms_shipped = 0
and u.name not in ('dbo', 'sa', 'public')
ORDER BY perm.permission_name ASC, perm.state_desc ASC

Alternately, wrap the entire thing in a msforeachdb:
exec sp_msforeachdb 'use [?];
SELECT DB_NAME() as Database_Name
--Database Level Roles
SELECT DISTINCT
 QUOTENAME(r.name) as database_role_name, r.type_desc, QUOTENAME(d.name) as principal_name, d.type_desc
, TSQL = ''EXEC sp_addrolemember @membername = N'''''' + d.name COLLATE DATABASE_DEFAULT + '''''', @rolename = N'''''' + r.name + ''''''''
FROM sys.database_role_members AS rm
inner join sys.database_principals r on rm.role_principal_id = r.principal_id
inner join sys.database_principals d on rm.member_principal_id = d.principal_id
where d.name not in (''dbo'', ''sa'', ''public'')
--Database Level Security
SELECT  rm.state_desc
 ,   rm.permission_name
    ,   QUOTENAME(u.name) COLLATE database_default
    ,   u.TYPE_DESC
 ,   TSQL = rm.state_desc + N'' '' + rm.permission_name + N'' TO '' + cast(QUOTENAME(u.name COLLATE DATABASE_DEFAULT) as nvarchar(256))  
FROM sys.database_permissions AS rm
 INNER JOIN
 sys.database_principals AS u
 ON rm.grantee_principal_id = u.principal_id
WHERE rm.major_id = 0
and u.name not like ''##%''
and u.name not in (''dbo'', ''sa'', ''public'')
ORDER BY rm.permission_name ASC, rm.state_desc ASC
 --Database Level Explicit Permissions
SELECT perm.state_desc
    , perm.permission_name
    ,   QUOTENAME(USER_NAME(obj.schema_id)) + ''.'' + QUOTENAME(obj.name)
       + CASE WHEN cl.column_id IS NULL THEN SPACE(0) ELSE ''('' + QUOTENAME(cl.name COLLATE DATABASE_DEFAULT) + '')'' END AS [Object]
 , QUOTENAME(u.name COLLATE database_default) as Usr_Name
    ,   u.type_Desc
    , obj.type_desc
    ,  TSQL = perm.state_desc + N'' '' + perm.permission_name
  + N'' ON '' + QUOTENAME(USER_NAME(obj.schema_id)) + ''.'' + QUOTENAME(obj.name)
  + N'' TO '' + QUOTENAME(u.name COLLATE database_default)
FROM sys.database_permissions AS perm
 INNER JOIN
 sys.objects AS obj
 ON perm.major_id = obj.[object_id]
 INNER JOIN
 sys.database_principals AS u
 ON perm.grantee_principal_id = u.principal_id
 LEFT JOIN
 sys.columns AS cl
 ON cl.column_id = perm.minor_id AND cl.[object_id] = perm.major_id
where
 obj.name not like ''dt%''
and obj.is_ms_shipped = 0
and u.name not in (''dbo'', ''sa'', ''public'')
ORDER BY perm.permission_name ASC, perm.state_desc ASC
';

HostForLIFE.eu SQL Server 2014 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes. We have customers from around the globe, spread across every continent. We serve the hosting needs of the business and professional, government and nonprofit, entertainment and personal use market segments.

One of the benefits and uses of Filtered Indexes in SQL Server is to produce filtered uniqueness. clearly this has some implications, thus please perceive what you are making an attempt to accomplish. In the below example, we've a compound natural key of the 2 fields key1 and key2. the field bit1 isn't a member of the natural key, however will inform us as to some table usage. maybe bit1 indicates that this record is Active (1) or Inactive (0), or whether or not the info is Confirmed (1) or unconfirmed  (0), or Deleted (0) or Not Deleted (1).

In any case, we have a tendency to solely wish to enforce uniqueness for when bit1 = 1, that indicates:
This value is often filtered to be used wherever bit1 = 1
We do not care whether or not there are duplicate records for once bit1 = 0.
In this means, you'll "deactivate" (in business terms) a record by setting bit1 = 0, while not violating your natural key's uniqueness on (key1, key2).
drop table dbo.tabletestdups
go
create table dbo.tabletestdups
( key1 int not null
, key2 int not null
, bit1 bit not null
)
go
create unique nonclustered index idx_nc_u_f_tabletestdups_key1_key2_bit1 on dbo.tabletestdups (key1, key2, bit1)
go
insert into dbo.tabletestdups (key1, key2, bit1) values (1,1,1) –succeed
insert into dbo.tabletestdups (key1, key2, bit1) values (2,2,1) –succeed
insert into dbo.tabletestdups (key1, key2, bit1) values (3,3,1) –succeed
insert into dbo.tabletestdups (key1, key2, bit1) values (1,1,0) –succeed
insert into dbo.tabletestdups (key1, key2, bit1) values (2,2,0) –succeed
insert into dbo.tabletestdups (key1, key2, bit1) values (3,3,0) –succeed
go
insert into dbo.tabletestdups (key1, key2, bit1) values (1,1,1) –fails
go
insert into dbo.tabletestdups (key1, key2, bit1) values (1,1,0) –fails
go
drop index idx_nc_u_f_tabletestdups_key1_key2_bit1 on dbo.tabletestdups
go
create unique nonclustered index idx_nc_u_f_tabletestdups_key1_key2_bit1 on dbo.tabletestdups (key1, key2, bit1) WHERE bit1 = 1 --Note the important WHERE clause here at the end of the index.
go
insert into dbo.tabletestdups (key1, key2, bit1) values (1,1,1) –fails
go
insert into dbo.tabletestdups (key1, key2, bit1) values (1,1,0) --succeeds because the unique constraint only enforces bit1 = 1.
go
select * from dbo.tabletestdups

And here is the output:

Note that rows four and seven have allowed duplicate combination of key1 =1, key2= 1 and bit1 = 0, but that previous attempts to insert a duplicate combination of key1 =1, key2= 1 and bit1 = 1 unsuccessful.

HostForLIFE.eu SQL Server 2014 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes. We have customers from around the globe, spread across every continent. We serve the hosting needs of the business and professional, government and nonprofit, entertainment and personal use market segments.

There are some condition &amp; times once SQL Server CPU usage exceeds threshold value (ex 80th or more) and keep constant even until 100 percent for a undefined duration. A DBA don't get notified mechanically concerning performance degrade till its according by application team or service desk.

This typically happens once all in explosive unannounced application work goes to SQL Server for processing; but at smart range of times, it’s as a result of some T-SQL Queries are taking longer than expected or as a result of bad execution plan or block. There is also a lot of reason to add, but ultimately high processor usage on production systems goes to harm performance.

Given below could be a script that you simply will convert into stored procedure and call from SQL Server agent job, regular for each five minutes or additional to ascertain CPU usage on a vital info server. This script uses sys.dm_os_ring_buffers to spot CPU usage from last five minutes and if average CPU usage goes beyond 80th then send an alert email together with top 50 CPU consuming SQL sessions and details. And now change the 3rd code line to: SELECT @TimeNow = cpu_ticks / (cpu_ticks/ms_ticks) from sys.dm_os_sys_info

SCRIPT
SET NOCOUNT ON
DECLARE @TimeNow bigint
SELECT @TimeNow = cpu_ticks / convert(float, ms_ticks) from sys.dm_os_sys_info
-- Collect Data from DMV
Select record_id, dateadd(ms, -1 * (@TimeNow - [timestamp]),
GetDate())EventTime, SQLSvcUtilization, SystemIdle,
(100 - SystemIdle - SQLSvcUtilization) AS OtherOSProcessUtilization into #tempCPURecords
from ( select record.value('(./Record/@id)[1]', 'int')record_id, record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]','int')SystemIdle, record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]','int')SQLSvcUtilization,
timestamp
from ( select timestamp, convert(xml, record)record
from sys.dm_os_ring_buffers
where ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
and record like '%<SystemHealth>%')x )y  order by record_id desc
-- To send detailed sql server session reports consuming high cpu
-- For a dedicated SQL Server you can monitor 'SQLProcessUtilization'
-- if (select avg(SQLSvcUtilization) from #temp where EventTime>dateadd(mm,-5,getdate()))>=80
-- For a Shared SQL Server you can monitor 'SQLProcessUtilization'+'OtherOSProcessUtilization'
if (select avg(SQLSvcUtilization+OtherOSProcessUtilization)
from #tempCPURecords where EventTime>dateadd(mm,-5,getdate()))>=80
begin
print 'CPU Alert Condition Ture, Sending Email..'
DECLARE @tableHTML  NVARCHAR(MAX) ;
SET @tableHTML =
    N'<H1>High CPU Utilization Reported</H1>' +
    N'<H2>SQL Server Session Details</H2>' +
    N'<table border="1">' +
    N'<tr><th>SPID</th><th>Status</th><th>Login</th><th>Host</th><th>BlkBy</th>'+
   N'<th>DatabaseID</th><th>CommandType</th><th>SQLStatement</th><th>ElapsedMS</th>'+
   N'<th>CPUTime</th><th>IOReads</th><th>IOWrites</th><th>LastWaitType</th>'+
N'<th>StartTime</th><th>Protocol</th><th>ConnectionWrites</th>'+
N'<th>ConnectionReads</th><th>ClientAddress</th><th>Authentication</th></tr>'+
CAST ( ( SELECT  TOP 50 -- or all by using *
td= er.session_id,'',
td= ses.status,'',
td= ses.login_name,'', 
td= ses.host_name,'',  
td= er.blocking_session_id,'', 
td= er.database_id,'', 
td= er.command,'', 
td= st.text,'', 
td= er.total_elapsed_time,'', 
td= er.cpu_time,'', 
td= er.reads,'', 
td= er.writes,'', 
td= er.last_wait_type,'', 
td= er.start_time,'', 
td= con.net_transport,'', 
td= con.num_writes,'', 
td= con.num_reads,'', 
td= con.client_net_address,'', 
td= con.auth_scheme,'' 
FROM sys.dm_exec_requests er 
OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) st 
LEFT JOIN sys.dm_exec_sessions ses 
ON ses.session_id =er.session_id  LEFT JOIN sys.dm_exec_connections con 
ON con.session_id = ses.session_id 
WHERE er.session_id > 50 
ORDER BY er.cpu_time DESC ,er.blocking_session_id
FOR XML PATH('tr'), TYPE
)AS NVARCHAR(MAX))+
N'</table>'
-- Change SQL Server Email notification code here
EXEC msdb.dbo.sp_send_dbmail
@recipients='dk@sqlknowledge',
@profile_name = 'SQLProfileName',   
@subject = 'ServerName:Last 5 Minutes Avg CPU Utilization Over 80%',
@body = @tableHTML,
@body_format = 'HTML';
END
-- Drop the Temporary Table
DROP Table #tempCPURecords

HostForLIFE.eu SQL Server 2014 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes. We have customers from around the globe, spread across every continent. We serve the hosting needs of the business and professional, government and nonprofit, entertainment and personal use market segments.