Microsoft Technologies blogs(.net, Sql Server)

Download Blocked Gmail Attachments

clock December 26, 2015 18:47 by author Arvind

Today I faced one real time issue while using mailing service. When I was tried to download attachments of email before more than 1 year which contain .rar and .dll file so now gmail are now allowing such type of file in email attachments and saying that Blocked file and I am not able to download that file as in image you can see.


I found some solution for download such attachments like android app or python way but there one very simple way.

Just click on top right “More” button and select “Show Original”, so that will open attachments as text you just copy that text file and paste in notepad and save as *.eml extention.


After that just open .eml file that will be open in outlook and you can save attachment from outlook mail simply.

Delete Duplicate Records from sql server table

clock February 24, 2015 08:16 by author Arvind

This is very common question in interview how can delete duplicate records from database table in sql server and there is lots of way to achieve this goal. But some time much developer confused with some scenario. So here I am discuss solution based on scenarios. First of all there are two main thing one delete duplicate when the identity column are present and 2nd when the identity column are not in table.

When identity column exists in table than you just check this example (just copy and run you will see the result.). Yes if the identity column exists than this is simple task.



Create table tmpTable(tmpId int identity(1,1), Fullname varchar(100), Comments varchar(500))

insert into tmpTable values ( 'Amit', 'comment here')

insert into tmpTable values ( 'Amit', 'comment here')

insert into tmpTable values ( 'Suresh', 'comment here two')

insert into tmpTable values ( 'Jai', 'comment here three')


Select * from tmpTable

--Here is single statement which delete duplicate record


FROM tmpTable




FROM tmpTable

GROUP BY Fullname, Comments)

--Here end

Select * from tmpTable


Drop Table tmpTable


2nd option when identity column are doesn’t exists in the table then



Create table tmpTable( Fullname varchar(100), Comments varchar(500))

insert into tmpTable values ( 'Amit', 'comment here')

insert into tmpTable values ( 'Amit', 'comment here')

insert into tmpTable values ( 'Suresh', 'comment here two')

insert into tmpTable values ( 'Jai', 'comment here three')


Select * from tmpTable

--Here is delete duplicate record

--In such case the simple way you just copy distinct result into another temp table and truncate original table

--and copy back distinct result from temp to original table and finally drop temp table

Select distinct * into #temp from tmpTable truncate table tmpTable insert tmpTable select * from #temp drop table #temp


--In such case this is also good 2nd query you can just use ROW_NUMBER and CTE(Common Table Expressions) and delete duplicate




FROM tmpTable






Select * from tmpTable


Drop Table tmpTable


Note:- Also some other option you can try yourself.

a)  You can add Identity Column and perform the operation based on identity and finally drop Identity Column.

b) You can use loop or cursor to do removing duplicate records.

c) You can use while loop as well do the same as cursor.





DefaultView of DataTable count showing 0 even if the datatable having rows

clock February 3, 2015 11:50 by author Arvind

Today I got little bit strange problem the DefaultView of DataTable (eg dt.DefaultView) is showing row count as 0 (eg dt.DefaultView.Count) where as datatable dt having rows and dt.Rows.Count showing greater than 0. So when I found such issue just thought as simple thing may be there are set RowFilterproperty which having zero result and most of the time this happened while coding. But interesting part this time when I checked RowFilter = "" set as empty and DefaultView count showing 0 where as DataTable rosw count showing greater than 0. My code as follows

//Here dt rosw count > 0 , checked RowFilter = "";

if(dt.Rows.Count > 0 )


DataView dv = dt.DefaultView;

      // here dv rows is showing 0



            //doing other stuff here



After some R&D we found the solution of this issue and change DefaultView as new DataView(dt); than it will work correctly.

//Here dt rosw count > 0

if(dt.Rows.Count > 0 )


      DataView dv = new DataView(dt);

      // Now here dv rows is showing > 0

      //dv.RowFilter = "";



            //doing other stuff here




So when we create new object the memory is allocated for that dataview and in that case such issue never comes again.

Calculated Running total in sql query

clock May 1, 2014 09:39 by author Arvind

There are lots of time in financial calculation the requeirment is get calculate the running total for each row. So I am writing the complete query with visible example.

Declare @TempTable Table


SNo int identity,

Total int





 INSERT INTO @TempTable(Total) Values(50)

 INSERT INTO @TempTable(Total) Values(60)

 INSERT INTO @TempTable(Total) Values(60)

 INSERT INTO @TempTable(Total) Values(10)

 INSERT INTO @TempTable(Total) Values(40)


 SELECT * FROM @TempTable


Select SNo, Temp.Total ,(select sum(Total) From @TempTable Where SNo <= Temp.SNo) 'Running Total'

From @TempTable Temp


Disabled button after click while processing in using javascript

clock April 7, 2014 11:59 by author Arvind

There is lots of way to prevent double clicking while postback process but this is the simple way to do that using window’s onbeforeunload eg. 

On aspx page your button control

<asp:Button ID="btnUpdate" runat="server" Text="Update" OnClick="btnUpdate_Clicked" />

Javascript code for that

<script type = "text/javascript">

function DisableButton()


document.getElementById("btnUpdate").disabled = true;


window.onbeforeunload = DisableButton;




Create database using SMO instead of DMO in Sql server in

clock December 31, 2013 10:04 by author Arvind

Microsoft introduces SMO as new feature in sql server 2005 onward version. This is the lots of advantages over old DMO for manage sql server using program. SQL Server Management Objects (SMO) is objects designed for programmatic management of Microsoft SQL Server. One of my old projects using the DMO to create database and update all script related to sp/view and master data records as well as jobs. Once running server upgraded the sql server 2000 to sql server2012 after that this feature is stop working because of DMO no longer supported in sql server 2012. So now I made change code using SMO instead of old DMO and I love new feature of SMO.

I create one web services for the creating database and run script/jobs etc. by simply calling that method into existing application.

First of all just add reference of SMO dll in to web service project and use these on top


using Microsoft.SqlServer.Management.Smo;

using Microsoft.SqlServer.Management.Common;

using Microsoft.SqlServer.Management.Smo.Agent;


After that wrtie web method to create database and runt the sql script file for all stored proc/view/table etc.



        public bool CreateDataBase(string strSQLServer, string strDBName, string SqlUserName, string Sqlpwd,string strSchemaScriptPath, string strDataScriptPath)


            bool flag = false;

            //here first of all create connection object

            ServerConnection objSc = new ServerConnection(strSQLServer, SqlUserName, Sqlpwd);

            //get server object based on connection

            Server objSrv = new Server(objSc);

            //create database object to create desired database using code.

            Database objDB = new Database(objSrv, strDBName);




                //After creation of database create schema script

                FileInfo fileSchema = new FileInfo(strSchemaScriptPath);

                string scriptSchema = fileSchema.OpenText().ReadToEnd();


                //After createion os schema need to inser datascript

                FileInfo fileData = new FileInfo(strDataScriptPath);

                string scriptData = fileData.OpenText().ReadToEnd();


                flag = true;


            catch (Exception ex)


                string str = ex.Message;

                flag = false;




                objDB = null;


            return flag;



In above web method only you need to pass the sql server instance name, database name, sql server uid/pwd and script for schema and data script if any.

Paging in T-Sql using OFFSET and FETCH NEXT in Sql server 2012

clock December 5, 2013 06:19 by author Arvind

Microsoft introduced lots of good feature in Sql Server 2012. One of them is pagination (paging with OFFSET and FETCH NEXT). Pagination in Sql server improves performance mainly when retrieving and displaying large set of records from Database.

The OFFSET and FETCH NEXT clause is basically an extension of existing ORDER BY clause in T-SQL. This feature is very helpful for developer to get only desired number of records which they would like to display in ASP.NET Data Grid or other control instead of getting the all records. So using this feature developer can implement the paging on page with faster and only get desired records. It can be used only with the ORDER BY clause.

Actually by using OFFSET the pointer set to that record to skip those and fetching the record after that using NEXT command.


Syntax and example:-

[ORDER BY { order_by_expression [ ASC | DESC ] } [ ,...n][<offset_fetch>] ]


<offset_fetch> ::= {OFFSET { integer_constant | offset_row_count_expression } { ROW | ROWS }    [FETCH { FIRST | NEXT } {integer_constant | fetch_row_count_expression } { ROW | ROWS } ONLY]}



We can use this feature in two different cases

Case 1: Skip first 100 rows from the result set and return the remaining rows

SELECT FirstName + ' ' + LastName FROM Employees ORDER BY FirstName OFFSET 100 ROWS


Case 2: Skip first 100 rows from the resultset and return next 50 rows.

SELECT FirstName + ' ' + LastName FROM Employees ORDER BY FirstName OFFSET 100 ROWS FETCH NEXT 50 ROWS ONLY



Limitations of OFFSET-FETCH clause:-

1. ORDER BY is mandatory to use OFFSET and FETCH clause.

2. OFFSET clause is mandatory with FETCH. You can never use, ORDER BY … FETCH.

3. TOP cannot be combined with OFFSET and FETCH in the same query expression.

4. The OFFSET/FETCH rowcount expression can be any arithmetic, constant, or parameter expression that will return an integer value. The rowcount expression does not support scalar sub-queries


I love this feature and hope you all enjoy this too………….


I also sharing one good query regarding the interview get nth highest salary of the employee with the help of OFFSET FETCH clause.

Eg- Suppose you have to write query for getting 51the highest salary of the employee you can write this simple query as follows

Select Salary From EmpSalDetails Order By Salary Desc OFFSET 50 ROWS FETCH NEXT 1 ROWS ONLY


It will return the 51st highest salary of the employee.


Create data script in sql server 2008/2012

clock December 2, 2013 12:48 by author Arvind

In new version of Sql server having good feature to generate data script automatically by selecting the option, this feature is very helpful when you are migrating data or updating production from test server. This is the simple step to generate data script of a table or whole database.


Step 1: Right click on database name and select “Tarsks” option in menu as in figure.

Step2: After selecting “Tasks” menu there are submenu comes up, in that submenu you can select “Generate script…” option.

Step3: Open “Generate and Publish Scripts” dialog box in that box click “Next” button to proceed.

Step4: After clicking “Next” button you can select the script option either one table or all table in that database or other database object eg views, stored procedures etc and click “Next” button.

Step 5: After selecting table you can click “Advanced” button to choose data script option.

Step 6: In “Advanced Scripting Options” you can select the “Type of data to script” either “Data only”, “Schema and data” and “Schema only” option as shown in figured.

Step 7 : If you selected “Data only” and click “Ok” and than “Next” and “Finish” button you will get all data script of the selected table.

Password for E-Aadhar card

clock November 13, 2013 04:54 by author Arvind

InIndianow a day everyone appling for Unique Identification Authority of India (UIDAI) and provide one option to get E-Aadhar card by downloading from there official website. But there are one problem facing lots of user after download the E-version of Aadhar Card that are not opening due to password protected. So I am writing this post to avoid the difficulty facing in opening the pdf e-aadhar card file.


Just open the E-Aadhar card pdf and the asking for password, your password for that pdf will be your postal address pin code/zip code(Which you given at the time of filling Aadhar card form).


Get two Database tables record within one stored procedure

clock August 6, 2013 11:44 by author Arvind

Sometime we need to get record from two different database tables record. So in such type of requirement if both databases are on same sql server than we can easily get two tables from different database within a stored procedure, that stored procedure may put in any one database where you want.

eg. Let consider two database db1 (table1) and db2 (table2) are on same sql server having two different table table1 and table2 respectively.

Now in db1 we write one stored procedure that need to fetch records from both tables as follows.

In db1, we can get data from table2 of db2 by using (double ..) as follows

Select * from db2..table2

If you want to get record from both tables using join condition than you store procedure should be looks like

Create Proc getDataFromTwoTable



      Select * from db2..table2 T1 Inner Join table1 T2 ON T1.Id = T2.Id




Comment RSS

Sign In