Cumulative SUM in SQL Server

Sometimes we need to find sum of first and next row in cumulative way.

Create table and insert data:

CREATE TABLE [dbo].[testsum](
      [name] [varchar](10) NULL,
      [val] [int] NULL,
      [ID] [int] NULL
) ON [PRIMARY]

insert into [testsum] (id,name,val)
values(1,'A',10),
(2,'B',20),
(3,'C',30)

Required Output:

ID    name  val   cumSum

1     A     10    10

2     B     20    30

3     C     30    60

To find cumulative sum first you need to self join on condition >=

select t1.*,t2.* from testsum t1 inner join testsum t2 on t1.ID>=t2.ID

output after join.

t1			t2
ID	name	val	ID	name	val
1	A	10	1	A	10
2	B	20	1	A	10
3	C	30	1	A	10
2	B	20	2	B	20
3	C	30	2	B	20
3	C	30	3	C	30

Group by ID and SUM.

select t1.id, t1.val, SUM(t2.val) as cumSum
from testsum t1
inner join testsum t2 on t1.id >= t2.id
group by t1.id, t1.val
order by t1.id

t1			t2
ID	name	val	ID	name	val
1	A	10	1	A	10
2	B	20	1	A	10
3	C	30	1	A	10
2	B	20	2	B	20
3	C	30	2	B	20
3	C	30	3	C	30

We reach to output:

id	val	cumSum
1	10	10
2	20	30
3	30	60

How to Split a string by delimited char in SQL Server..............

However I posted this one in one of my post.
Querying Microsoft SQL Server : Functions in SQL Server: Functions in SQL Server In SQL Server functions are subrotienes that encapsulate a group of T-SQL statements for reuse.SQL Server pro...

Here I separate ,Split function from that post.

CREATE FUNCTION [dbo].[fnSplitString] 
( 
    @string NVARCHAR(MAX), 
    @delimiter CHAR(1) 
) 
RETURNS @output TABLE(splitdata NVARCHAR(MAX) 
) 
BEGIN 
    DECLARE @start INT, @end INT 
    SELECT @start = 1, @end = CHARINDEX(@delimiter, @string) 
    WHILE @start < LEN(@string) + 1 BEGIN 
        IF @end = 0  
            SET @end = LEN(@string) + 1
       
        INSERT INTO @output (splitdata)  
        VALUES(SUBSTRING(@string, @start, @end - @start)) 
        SET @start = @end + 1 
        SET @end = CHARINDEX(@delimiter, @string, @start)
        
    END 
    RETURN 
END

Exceute this T-sql statements to create function and use as

select *from dbo.fnSplitString('Querying SQL Server','')

Output:

Connection Pool limit exceeds Error

Sometimes below error occurs on our web applications hosted on IIS connected with SQL Server.

Timeout expired.  The timeout period elapsed prior to obtaining a connection from the pool.  This may have occurred because all pooled connections were in use and max pool size was reached.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.InvalidOperationException: Timeout expired.  The timeout period elapsed prior to obtaining a connection from the pool.  This may have occurred because all pooled connections were in use and max pool size was reached.

Basically, This error occurs whenever connection pool limit exceeds.This can be resolved by clearing connection pool.However this can be resolved using SQL Server by number of ways.

You can use

SqlConnection.ClearAllPools(); of .Net to clear pool.

SqlConnection.ClearAllPools() method empties the connection pool.If there are connections in use at the time of the call, they are marked appropriately and will be discarded (instead of being returned to the pool) when Close method  is called on them.

Sometimes you need to clear pool without modifying your code.So I create a utility for cleaning connection pool.A simple window form that make a connection to your database and empty database connection pool.

Use Code:

private void button1_Click(object sender, EventArgs e)
     {
         try
         {
             //Creating Connection
             SqlConnection con = new SqlConnection();
             //Connection Sring
con.ConnectionString = string.Format("Data Source={0};Initial Catalog={1};Persist Security Info=True;User ID={2};Password={3}", txtData.Text, txtDatabaseName.Text, txtUserID.Text, txtpassword.Text);
             if (button1.Text == "Connect")
                {
                 //Open Connection
                 con.Open();
                 if (con.State == ConnectionState.Executing)
                 {
                     lblStatus.Text = "Connecting..........";
                 }
                 else if (con.State == ConnectionState.Open)
                 {
                     lblStatus.Text = "Connected";
                     button1.Text = "Disconnect";
                     btnClear.Enabled = true;
                 }
             }
             else
             {
                 con.Close();
                 lblStatus.Text = "Disconnected";
                 button1.Text = "Connect";
                 btnClear.Enabled = false;
             }
         }
         catch (Exception ex)
         {
             lblStatus.Text = ex.ToString();
         }
     }
 
     private void btnClear_Click(object sender, EventArgs e)
     {
         //Clear all Pools.
         SqlConnection.ClearAllPools();
         lblstatuspool.Text = "Pool Claered";
     }
 

 

Download Utility

Sourceforge:

https://sourceforge.net/projects/connectionpoolcleaner/files/latest/download?source=files

Direct Link:

https://downloads.sourceforge.net/project/connectionpoolcleaner/ClearConnectioPool.rar?r=&ts=1379070899&use_mirror=master

Insert data from Excel to SQL Server

Here I am discussing about data insertion from MS Excel into SQL Server table. SSMS provides  Export Import wizard by which you can achieve same easily. Follow below steps

Suppose I have a Excel file on server as c:/fis.xlsx.

1.       Go to database

2.       Right Click on database select tasks

3.       Select Import Data

                  

4. A SQL Server Export Import wizard appears.Click Next.
   
   
5. Choose DataSource Microsoft Excel from the List and Browse the Excel File
   
   
   

6. Click Next.
   
   
7. Choose destination.
   

8. Click Next
   
   
   
   
   
   
   
   
   
9. Next
   
   

10. Click Next->Next and Finish.
    
    
    
    
    
    
    
11. Finally a new table named Sheet1$ created.
    
    
12. Now if you want to insert these records in existing table,you can use simple insert statement and drop table.
    
    

--Sample

insert into dbo.existingtable(col1,col2,col3) values
(select col1,col2,col3 from dbo.Sheet1$)

Example:

insert into dbo.FileRecord(FileNo,ProjectName,Customer_Name,PropertyCode,Status,IDate,InsertedBy,UpdateDate,UpdatedBy,remarks,location)
select top 2 [FileNo,ProjectName,Customer_Name,PropertyCode,Status,IDate,InsertedBy,UpdateDate,UpdatedBy,remarks,location from dbo.Sheet1$

13. Your data now successfully inserted into SQL Server table from Excel.
    
    
14. You can drop the table.
    
    
15. drop table dbo.Sheet1$

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.