Tuesday, January 26, 2016
MS SQL Server - 'sa' user locked out
If you come across a scenario where 'sa' user is locked out and you do not have any other ways to reset or unlock the user. 

Use the below MSDN reference to get SQL server back and running.
Posted On Tuesday, January 26, 2016 6:33 PM | Comments (0)
Monday, January 25, 2016
setting up SMTP on windows server and getting it up and running
install smtp server features through server manager if its windows server machine or through add windows features from 'add/remove programs' dialog option

even if you are on iis 7 or anything higher, open up iis 6 manager and make sure smtp virtual server is  running (specially if you are getting error message saying 'Remote machine actively refused connection)

if you get 5.7.1 relay error - check the properties of smtp virtual server , general tab ..instead of all 'All UnAssigned' select the ip address of machine and on Access tab ..relay option add and server machine address to the list 
Posted On Monday, January 25, 2016 10:46 PM | Comments (0)
Thursday, November 5, 2015
Find out all the changes / alterations to SQL database objects
This script when run under user who has access to 'sys.traces' will be able to sql tables, stored procedures which has been modified.

DECLARE @filename VARCHAR(255) 
SELECT @FileName = SUBSTRING(path, 0, LEN(path)-CHARINDEX('\', REVERSE(path))+1) + '\Log.trc'  
FROM sys.traces   
WHERE is_default = 1;  
print @FileName
SELECT gt.HostName, 
       te.Name AS EventName,
FROM [fn_trace_gettable](@filename, DEFAULT) gt 
JOIN sys.trace_events te ON gt.EventClass = te.trace_event_id 
WHERE EventClass in (164) AND gt.EventSubClass = 0

Posted On Thursday, November 5, 2015 4:22 PM | Comments (0)
Friday, August 14, 2015
Remove all characters words after certain character word in SQL
If you want to get rid of all the data after certain word in SQL column, you can use the combination of LEFT and CHARINDEX function to achieve the desired result.

For example, if column consists of following data - 


tempdata1 tempdata2;tempdata3

and if you want to display only uptill the semicolon in result - then use the following query:

SELECT LEFT(Column1, CHARINDEX(';', Column1)-1)


tempdata1 tempdata2

Posted On Friday, August 14, 2015 12:24 PM | Comments (0)
Sunday, June 14, 2015
Angular JS - adding Options dynamically to Select drop down box
You can create options elements of <select> dynamically using Angular.

An example of it with various options like grouping options or disabling few are also available.

    <script data-require="angular.js@1.4.0" data-semver="1.4.0" src=""></script>
    <script type="text/javascript">
  angular.module("app", []).controller("myController", ["$scope", function($scope) {

    $scope.OptionArray = [{
      text: "Mumbai",
      id: 1,
      Country: "India"
    }, {
      text: "London",
      id: 1,
      Country: "UK"
    }, {
      text: "Perth",
      id: 1,
      Country: "Australia"
    }, ]

    $scope.optionSelectedOne = 2;

  <body ng-app="app" ng-controller="myController">

      <select ng-model="optionSelectedOne" ng-options=" as obj.text group by obj.Country disable when obj.Country == 'Australia' for obj in OptionArray">
      <option value="">Please Select Country</option>


The output of this would look like the following  - 

Posted On Sunday, June 14, 2015 3:31 PM | Comments (0)
AngularJS - Basics
I have recently been learning AngularJS by myself through online documentation. No Wonder it is so popular, its just WOW.  It's sop much easier to work and not to worry about two-way binding. Angualr JS takes care of it automatically for us. You don't need any framework or installations to get started working with AngualrJS. 

It makes life so much easier building SPA(Single Page Applications). 

All you need to do to get up and running on Angular JS is - 
  • Download AngularJS library from Google official site and reference it from your page using Script tag or you can directly reference to the CDN location instead of downloading it. At the time of writing this post, the latest version currently Live was 1.4 with alpha testing on for 2.0 version.
  • Put ng-app tag inside the area in HTML which you want to be handled and controlled by AngualarJS. All this does is bootstrap Angualr and tells it to take the control of designated area. All the directives, controllers and template are evaluated and controlled by Angular. 
  • Go to the Scripts and create an Angular module using javascript. A module is abstraction which acts as a container for a related functionality. So all related directives and controllers belong to the module. You can have multiple module inside the application grouped by functionality. But,a page can be assigned to only single module. 
Syntax for creating module.


The first parameter is the name of the module. The second parameter is the dependencies on which this module depends. This is the Dependency Injection pattern of Angular. You provide the dependencies as a parameter to the constructor. This is also called constructor injection.

Once created you use this module to add controllers, filters or custom directives. 

You will typically mark either <html> or <body> tag with ng-app and supply the created module name to it for Angular to take control of that section of page.

<script data-require="angular.js@1.4.0" data-semver="1.4.0" src=""></script>
<body  ng-app="moduleName">

the page when opened in browser would display 5. 

The expression inside the interpolation expression or template will get evaluated by Angular.

This is just very basic example of Angular and doesn't display all the capabilities of it. In coming days I'll cover few more posts about it.
Posted On Sunday, June 14, 2015 2:02 PM | Comments (1)
Monday, July 7, 2014
Create or Restore SQL Database with backup from different location
There comes a scenario when you want to restore database with another database backup but would like its data and transaction files to be located at different location.

Original Database - OldDB (database whose backup is with you)
New Database - NewDB (new database to be created by restoring backup)

Step 1- get the logical file name for OldDB backup using the following sql


This will give you logical file name of the Data and transaction log files of the backup.

Step 2- Use below sql to create new database using backup from different location

WITH MOVE 'OldDB Backup Data file Logical name' TO 'E:\Dev2008\Data\NewDB_Data.mdf',
MOVE 'OldDB Backup Log file Logical name' TO 'E:\Dev2008\Logs\NewDB_Log.ldf'

Posted On Monday, July 7, 2014 4:35 PM | Comments (0)
Tuesday, March 11, 2014
TFS - Unshelve shelvesets
If you ever come across a scenario where you want to unshelve a shelve set into a branch other than the one it's saved in, then it's an ideal case to use TFS Power tools.

use tfpt unshelve to achieve unshelving of a shelve set to a different branch


/>tfpt unshelve [shelvesetname[;username]] [/nobackup][/migrate /source:serverpath /target:serverpath]

  • shelvesetname          The name of the shelveset to unshelve 
  • /nobackup              Skip the creation of a backup shelveset 
  • /migrate               Rewrite the server paths of the shelved items                          (for example to unshelve into another branch) 
  • /source:serverpath     Source location for path rewrite (supply with /migrate) 
  • /target:serverpath     Target location for path rewrite (supply with /migrate) 
  • /undo                  Undo pending changes from an unshelved shelveset 
  • /batchsize:num         Set the batch size for server calls (default 500)
you saved a shelveset into Dev branch with the title 'Test Shelve set'. Now you wish to unshelve it into PROD branch. Use the below command -

/> tfpt unshelve “Test Shelve set” /nobackup /migrate /source:”$/MyTeamProject/DevBranch” /target:”$/MyTeamProject/ProdBranch”

Posted On Tuesday, March 11, 2014 3:53 PM | Comments (0)
Wednesday, July 24, 2013
Retrieve .Net Control ID in Javascript

If you need to retrieve a client ID of an asp:net control in a javascript function, then you can use the below function -

function $$(id, context) {
        var el = $("#" + id, context);
        if (el.length < 1)
            el = $("[id$=_" + id + "]", context);
        return el;
var tempDotNetControl = 'aspTextTemporary';
var ClientSideID = $$(aspTextTemporary);

Please bear in mind, this function is useful if you want to retrieve client ID of a different DotNet control based on some condition, otherwise if it’s always static then you can just use <%= aspTextTemporary.ClientID %>"

Posted On Wednesday, July 24, 2013 6:22 PM | Comments (0)
Thursday, June 6, 2013
LINQ example with GroupBy clause on a property in object
//group all above policies by broker to send an email about "broker re-assignment'
var brokerPolicies = from pol in policy.ListData.AsEnumerable()
                     group pol by pol.Field<int>("intUserID") into grp
                     select new
                             brokerID = grp.Key,
                             listOfPolicies =  from x in grp.AsEnumerable()
                                               select new 
                                                      list = String.Format("{0:00}-{1:000000}",x.Field<short>("intPolicySequence"), x.Field<int>("intPolicyNumber"))
Posted On Thursday, June 6, 2013 5:55 PM | Comments (1)
Tuesday, March 12, 2013
SQL Server–Find Nth Largest/smallest entry in a table

We could find the maximum and minimum value in a table by using the SQL inbuilt aggregate functions MAX() and MIN() respectively. But, many times there comes a scenario when we need to find the second or third largest/smallest entry in table. There is no built-in SQL function to get this information but we can use Row_Number() function to our advantage to find Nth Largest or smallest value in table.

For example – usually, to find maximum value we normally use the following

select MAX(column_name) as column_name from table_name 

This is equivalent to the following

SELECT ROW_NUMBER() OVER ( ORDER BY column_name DESC) ROW_NUM, column_name FROM table_name
)  T 
WHERE row_num = 1
However, this can then be used to get any largest value in the table, all you have to do is just change the row_num value in the where clause.
So, to get the second largest value set 
WHERE row_num = 2

Similarly to find the second smallest entry, just sort the column by ascending and set the where clause to 2.

Hope this helps!

Posted On Tuesday, March 12, 2013 3:45 PM | Comments (2)
Tuesday, March 5, 2013
Reseed Identity column in SQL Server
Use the below SQL command to reset the seed on the identity column -


The following example forces the current identity value in the AddressTypeID column in the AddressType table to a value of 10. Because the table has existing rows, the next row inserted will use 11 as the value, that is, the new current increment value defined for the column value plus 1.

DBCC CHECKIDENT ("Person.AddressType", RESEED, 10);

Posted On Tuesday, March 5, 2013 5:25 PM | Comments (2)
Monday, March 4, 2013
SQL Server - get last executed SQL statement
We all know the usage of sp_who2 command in SQL server, its life-saver when you need to know what's causing your query take longer time to execute or in other words who's blocking you r query.

But, SQL server has many in-built functions which lets you sneak a peek inside the sql server and we can use them to our advantage. One of them is 


Using this function, we can see the last executed sql statement in that process id.

There are several ways to find out what is the latest run query from system table sys.sysprocesses.

SELECT @sqltext sql_handle
FROM sys.sysprocesses
WHERE spid 61

Posted On Monday, March 4, 2013 3:32 PM | Comments (3)
SQL server - get list of active connections to each database
Many times, we want list of all users who are connected to the SQL server - to determine it  use the below query. It returns the Database, Number of open connections and logged-in user credentials.

SELECT DB_NAME(dbid) as Database, COUNT(dbid) as Number Of Open Connections,
loginame as LoginName
FROM sys.sysprocesses
WHERE dbid > 0
GROUP BY dbid, loginame

Hope this helps.
Posted On Monday, March 4, 2013 3:24 PM | Comments (4)
Friday, March 1, 2013
Find out last modification to the SQL table
If you need to find when was the table last updated for insert/delete/update .. use the below query to find it. It will show you the last updated datetime for the table

last_user_update, *

FROM sys.dm_db_index_usage_stats

WHERE database_id = DB_ID( 'DB_Name')
Posted On Friday, March 1, 2013 3:30 PM | Comments (1)
Thursday, February 28, 2013
SQL Server Management Studio crashed - recover unsaved sql query
If for some reasons SQL server instance dies or it crashes and if you had an unsaved query - you could still recover it from the backup files that sql server creates whenever it crashes unexpectedly.

The path for the backup .sql files is something like the following  -

C:\Users\USER_PROFI\Documents\SQL Server Management Studio\Backup Files\Solution1
Posted On Thursday, February 28, 2013 5:31 PM | Comments (7)
DMV to figure out unused and missing Indexes on table in SQL server
MS SQL Server 2005 onwards provides quite a lot of views and functions which can be used to keep a tab on the database engine and also gives you statistics which helps you in making informed decision about the database schemas. 

This are collectively called as DMV (Dynamic management views).

For example - the following DMV can help you show the state of indexes in the database.It shows you the table name, the number of indexes on that table, how many of those indexes have been unused since either the last restart or the index was (re)created, and how many indexes SQL Server thought it would like to have on the table. In a well tuned database, the unused and missing counts would both be 0. If they are, you have exactly the right number of indexes for the workloads hitting your tables.

        COALESCE(Unused.IdxCount0)    AS IDXCOUNT,
        COALESCE(Unused.UnusedCount0) AS UNUSEDCOUNT,
        COALESCE(CONVERT(DECIMAL(6,1),(CONVERT(DECIMAL(10,2),Unused.UnusedCount)/CONVERT(DECIMAL(10,2),Unused.IdxCount))100)0) AS UnusedPct

FROM    sys.objects so

    (   SELECT      s.OBJECT_ID,
                    COUNT(*)        AS idxcount,
                    SUM(CASE WHEN   s.user_seeks    = 0
                                AND s.user_scans    = 0
                                AND s.user_lookups  = 0
                            THEN    1
                            ELSE 0 END) AS UnusedCount

            FROM    sys.dm_db_index_usage_stats s
            JOIN    sys.indexes     i
            ON      s.OBJECT_ID     = i.OBJECT_ID
            AND     s.index_id      = i.index_id
            WHERE   s.database_id   = DB_ID()
            AND     OBJECTPROPERTY(s.OBJECT_ID,‘IsMsShipped’) = 0
            GROUP BY    s.OBJECT_ID
        ) Unused
ON      Unused.OBJECT_ID = so.OBJECT_ID
                    COUNT(*) AS MICOUNT
            FROM    sys.dm_db_missing_index_groups  g
            JOIN    sys.dm_db_missing_index_group_stats s
            ON      s.group_handle                  = g.index_group_handle
            JOIN    sys.dm_db_missing_index_details d
            ON      d.index_handle                  = g.index_handle
            WHERE   d.database_id = DB_ID()
            GROUP BY d.OBJECT_ID
ON      Missing.OBJECT_ID = so.OBJECT_ID
WHERE   so.type_desc = ‘USER_TABLE’
AND     (Missing.MICOUNT > 0
        OR Unused.UnusedCount > 0)


This returns the following in sample database = 


Table_1 1 1 0 100.0

Table_2 1 1 0 100.0

Table_3 1 0 4 0.0

Table_4 1 0 3 0.0

Table_5 1 0 1 0.0

Table_6 1 0 5 0.0

Posted On Thursday, February 28, 2013 3:49 PM | Comments (0)
Some times, you may want the database to interpret the date value as per the format you have provided rather than using the default database level format (which is mostly set to mdy as default US date format). SQL server SET DATEFORMAT syntax comes to rescue in this situations - 

you can set the dateformat to your convenience and then can run the insert or update with your format and SQL will not crib about it.  Please note, this is applicable only for the session and it does not set the date format at the server level. 
Also, the data in the database will get saved as per the format set for the server so there won't be any messing up of data.

A hypothetical scenario could be  - if you receive a CSV file which you need to import into database and all the dates in the CSV are in DD/MM/YYYY format whereas your database accepts date in YYYY/MM/DD format.

CREATE TABLE #temp (datesample smalldatetime)




VALUES ('09/28/2007')



VALUES ('2007/28/09')



VALUES ('2007/08/28')



VALUES ('28/12/2006')

SELECT DateSample

FROM #temp 


This would return the following output -


2007-09-28 00:00:00

2007-09-28 00:00:00

2007-08-28 00:00:00

2006-12-28 00:00:00

Hope this helps someone.


Posted On Thursday, February 28, 2013 3:40 PM | Comments (0)
Wednesday, March 28, 2012
App_Offline.htm, taking site down for maintenance

There is much simpler and graceful way to shut down a site while doing upgrade to avoid the problem of people accessing site in the middle of a content update.


Basically, if you place file with name 'app_offline.htm' with below contents in the root of a web application directory, ASP.NET will shut-down the application,  and stop processing any new incoming requests for that application. 

ASP.NET will also then respond to all requests for dynamic pages in the application by sending back the content of the app_offline.htm file (for example: you might want to have a “site under construction” or “down for maintenance” message).


Then after upgrade, just rename/delete app_offline.htm file…and the site would be back to normal.

Just remember that the size of the file should be greater than 512 bytes, doesn't matter even if you add some comments to it to push the byte size as long as it's of the size greater than 512 - it'll work fine.



<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "">
<html xmlns="" >
    <title>Maintenance Mode - Outage Message</title>
    <h1>Maintenance Mode</h1>

    <p>We're currently undergoing scheduled maintenance. We will come back very shortly.</p>
    <p>Sorry for the inconvenience!</p>

    Adding additional hidden content so that IE Friendly Errors don't prevent
    this message from displaying (note: it will show a "friendly" 404
    error if the content isn't of a certain size).
    <h2>Site under maintenance...</h2> 
    <h2>Site under maintenance...</h2> 
    <h2>Site under maintenance...</h2> 
    <h2>Site under maintenance...</h2> 
    <h2>Site under maintenance...</h2> 
    <h2>Site under maintenance...</h2> 
    <h2>Site under maintenance...</h2> 
    <h2>Site under maintenance...</h2> 




Posted On Wednesday, March 28, 2012 4:25 PM | Comments (3)
Thursday, March 22, 2012
Show line breaks in asp:label inside gridview

To show line breaks in asp:label element or for that matter inside Gridview, do the following  in case of Mandatory/ Nullable fields.

<%# ((string)Eval("Details")).Replace("\n", "<br/>")
<%# FormatString(Eval("Details"))


In code behind, add the following FormatString function -


string FormatString(string strHelpMessage)


string rtnString = string.Empty;

if (!string.IsNullOrEmpty(strHelpMessage))

rtnString = strHelpMessage.Replace(

Environment.NewLine, "<br/>");

return rtnString;


Posted On Thursday, March 22, 2012 4:02 PM | Comments (3)
Page_BlockSubmit - reset it to False, if there is a scenario when page doesn't postback on validation error

Recently, I was facing a problem where if there was a validation error, and if I changed the state of checkbox it won't postback on first attempt. But when I uncheck and check again , it postbacks on second attempt...this is some quirky behaviour in .ASP.Net platform.

The solution was to reset Page_BlockSubmit flag to false and it works fine.

The following explanation is from


Submit button on the page is a member of vgMain, so automatically it will only run the validation on that group. A solution is needed that will run validation on multiple groups and block the postback if needed.


Include the following function on the page:

function DoValidation()
   //validate the primary group
   var validated = Page_ClientValidate('vgPrimary ');
   //if it is valid
   if (validated)
      //valid the main group
      validated = Page_ClientValidate('vgMain');
   //remove the flag to block the submit if it was raised
   Page_BlockSubmit = false;
   //return the results
   return validated;

Call the above function from the submit button’s OnClientClick event.

<asp:Button runat="server" ID="btnSubmit" CausesValidation="true" 
   ValidationGroup="vgMain" Text="Next" OnClick="btnSubmit_Click"
   OnClientClick="return DoValidation();"  />

What is Page_BlockSubmit

When the user clicks on a button causing a full post back, after running Page_ClientValidate ASP.NET runs another built in function ValidatorCommonOnSubmit. Within Page_ClientValidate, Page_BlockSubmit is set based on the validation. The postback is then blocked in ValidatorCommonOnSubmit if Page_BlockSubmit is true. No matter what, at the end of the function Page_BlockSubmit is always reset back to false.

If a page does a partial postback without running any validation and Page_BlockSubmit has not been reset to false, the partial postback will be blocked. In essence the above function, RunValidation, acts similar to ValidatorCommonOnSubmit. It runs the validation and then returns false to block the postback if needed. Since the built in postback is never run, we need to reset Page_BlockSubmit manually before returning the validation result.

Posted On Thursday, March 22, 2012 3:59 PM | Comments (4)
Tuesday, March 13, 2012
Update Query in SQL with SELECT statement
Table.col1 = other_table.col1, 
Table.col2 = other_table.col2 
ON = 
Posted On Tuesday, March 13, 2012 4:16 PM | Comments (2)
Monday, March 5, 2012
ThrowIfMaxHttpCollectionKeysExceeded error message when doing postback

Microsoft recently (12-29-2011) released an update to address several serious security vulnerabilities in the .NET Framework. One of the fixes introduced by MS11-100 temporarily mitigates a potential DoS attack involving hash table collisions. It appears this fix breaks pages that contain a lot of POST data. In our case, on pages that have very large checkbox lists. Why would this be the case?

There are some information on this limit to be at if your webpage was working fine earlier and it all of a sudden started crashing with the following message -

"Operation is not valid due to the current state of the object." whenever a post back is done.

the stack trace is

at System.Web.HttpValueCollection.ThrowIfMaxHttpCollectionKeysExceeded() at System.Web.HttpValueCollection.FillFromEncodedBytes(Byte[] bytes, Encoding encoding) at System.Web.HttpRequest.FillInFormCollection()

Then  probably you have crossed the default limit for the postdata collection key.

To overcome this limit, add the following line of code in you <appsetting> section of web.config

<add key="aspnet:MaxHttpCollectionKeys" value="2001" />

Hope this helps someone..           

Posted On Monday, March 5, 2012 4:05 PM | Comments (18)
Thursday, June 9, 2011
Date/Time formats in SQL Server

In SQL server a column with datatype of DateTime stores the time to the millisecond, but many times you just like to show the date and time only upto the second.

Although there are many ways to achieve this, I'm just going to show the couple of possible ways -



This would yield the current date time in YYYY-MM-DD HH:MM:SS (ofcourse depends on your regional setting)

2.) You can get the millisecond part from the getdate() function and subtract it with the getdate() to leave you with time only upto second.

SELECT DATEADD(ms, -DATEPART(ms,GetDate()),GetDate())

As I mentioned earlier, these are just some of the possible ways to acheive the desired result.

The post has some very good collection to acheive various formats in date/time in SQL.

Hope this helps!


Posted On Thursday, June 9, 2011 12:21 PM | Comments (0)
Wednesday, June 8, 2011
Detecting browser 'Refresh' from Code behind in C#

Browser 'Refresh' is always a cause of concern for the developers. It becomes even more worse when the page interacts with the database. As each refresh, if not handled, would lead to the database action repeated.

This could lead to inconsistency in database or even break the application.

One way could be to detect 'refresh' using javascript and disable F5 or right click..but there are so many other ways end-user can initiate 'Refresh' action for e.g. by using CTRL+R on the keyboard...

The best way to stop 'Refresh' calling your program again is to detect it at the server side and handle it..

The following code snippet detects 'Refresh' in page_load function

bool IsPageRefresh = false;

//this section of code checks if the page postback is due to genuine submit by user or by pressing "refresh"

if (!IsPostBack)


ViewState["ViewStateId"] = System.Guid.NewGuid().ToString();

Session["SessionId"] = ViewState["ViewStateId"].ToString();



if (ViewState["ViewStateId"].ToString() != Session["SessionId"].ToString())


IsPageRefresh = true;


Session["SessionId"] = System.Guid.NewGuid().ToString();

ViewState["ViewStateId"] = Session["SessionId"].ToString();


You can then use the 'IsPageRefresh' boolean flag in the code-behind to determine if it's a postback due to genuine User submit action or by browser 'Refresh'.

Hope this helps!

Posted On Wednesday, June 8, 2011 4:34 PM | Comments (11)