How to insert records on SQL Server identity column?

Scenario:

Insert records in SQL Server table with pre-defined primary key on an identity column. For instance, the the data ContactId to insert is from 11-50. How to insert the ContactId as my identity column value is 150 now? The assumption here is existing ContactId  in the range could be deleted for the new INSERT.

 Concepts:

Identity column on a table increment the value automatically for each record INSERT into the table. For example, the table "Contact" has an identity column defined on ContactID column. The column is defined to increase the max identity value by 1. This is done by specifying the increment value to 1.

The seed value lets you specify the identity column starting value. For instance, I would like to start all data from ContactId = 100.

The T-SQL table create statement looks like below:

Solution:

Turn the IDENTITY_INSERT ON to tell the server that you want to insert specific ID value. Perform the record insert. Enable the identity columns auto value by setting the IDENTITY_INSERT OFF.

Syntax:
SET IDENTITY_INSERT [ database. [ owner. ] ] { table } { ON | OFF }

Example:

Further references:

MSDN Table Identity Column
MSDN IDENTITY_INSERT

How to configure Windows 7 Remote Desktop to use different port number

Scenario

Imagine you would like to disable the remote desktop session for all users but not yourself.  You can definitely restrict remote desktop users using the Local Security Policy. Then, what if the domain users is part of Administrator group who can remote desktop and you do not wish to change that? 

One way to achieve that is to change the RDP default port 3389. For example use port 3391 for RDP.

How to configure specific port number

   Figure 1.0 RDP port number registry key

1. Edit the RDP port number (in windows registry) from 3389 to 3391. Run regedit tool and navigate the registry key [HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Terminal Server\WinStations\RDP-Tcp]. 
2. Double click on the registry value, select Decimal and type in 3391.
3. Logoff the session
4. Remote Desktop and specify the computer with port number. If your computer name is LUCKY, then specify it to Lucky:3391 and click connect (as shown below).
5. Voila.

 

Figure 2.0 RDP connect with port number

Possible Issue1 - Unable to RDP using Lucky:3391

Details 

Error might goes like "Remote Desktop Can't Connect to the remote computer for one of these reason ....".

Possible cause

The computer that is used to remote desktop is blocking port 3391. Windows Firewall is blocking port 3391.

Diagnose: How to check the port is blocked by Windows Firewall

1. Check Window Firewall is ON. If not, check for any 3rd party firewall.
2. Check whether port 3391 is blocked by running telnet command from command prompt.
3. Command text => telnet LUCKY 3391 (Assume Computer name = LUCKY and port = 3391)
4.  If the port is blocked, you should see the screen below

Figure 3.0 Diagnose with Telnet

Solution: Add firewall port number exception (for Windows Firewall)

Steps

1. Add the port 3391 to your firewall exception rule. This will allow your computer to connect to port 3391.
2. Open Windows Firewall settings. This could be done by typing firewall.cpl into the command line. Click on Advance Settings.
3. Create an Outbound Rule on TCP port 3391. Name the rule and close the window

    

Figure 4.0 Firewall Exception

Further reference

How to connect to specific port using Remote Desktop

Buy Wireless USB Adapter for Windows 7

Scenario

My Netgear 54Mbps 802.11b/g usb wifi dongle has joined the broken list recently. I am aware that  Wireless PCI network card is a clear winner for desktop computer (compare to usb dongle). It uses less processor cycle and faster.

However, I opted for the Usb dongle instead. The reason is I have 2 desktop computers. I can swap it to another computer. Imagine unplugging the wireless PCI adapter card..... 

How do you know which Usb dongle works with Windows 7? Does it support 32 bits or 64?

Find out the answer from Microsoft official Windows 7 Certified Usb Wireless adapter Compatibility page.

The logo indicates:

"We focused on robust testing requirements to ensure optimal Windows 7 experience. Products that receive the Logo are checked for common issues to minimize the number of crashes, hangs, and reboots experienced by the user."

I end up buying the following from Amazon. Apparently many good reviews.

Edimax EW-7811Un Network adapter

What to consider when buying a new wireless USB adpater?

1. Security/Encryption

Avoid WEP as it's less secure wireless protocol (exists in the older version of wifi adapter). Look for WPA, WPA2/AES.

2. Network compatability (802.11a/b/g/n)

Does the dongle need to be backward compatible with specific 802.11b router? Or do you intend to use the dongle to connect to other 802.11x router? If you only have one 802.11n router, just get the dongle that support 802.11n.

3. Draft version
Watch out for 802.11n or  draft 802.11n. Avoid draft version by checking manufacture specification.

4. USB compatibility. USB1.0, 1.1, 2.0 or 3.0. 

Which version does your desktop computer supports?

5. Software set up. 

Does the dongle software allows you to save configuration profile?

6. Range. 

New usb dongle has MIMO technology that increase range, realiability and speed. Dearer indeed.

7. Issues. Check online forum whether it has overheating or battery draining issue (if use on old laptop).

I hope you find this post useful. :)

USB Pen Drive does not power off in Windows 7

Symptoms:

Isn't it annoying the USB Pen Drive power still on after you ejected the usb device successfully in Windows 7? This doesn't happen on Windows XP or Windows 2003. The USB drives are still drawing power from the laptop or desktop.

Cause:

This is work as design according to Microsoft Engineer known as SoftRemoval in Windows 7. The USB device is marked for removal but NOT disabled in Windows 7. When a USB hub is disabled, no further traffic is sent to the device.

Windows XP disabled the USB device when it's ejected. This powers down the USB port hub and the device.

Resolution (Windows 7):

1) Modify window registry (using regedit) to disabled all USB devices on eject
2) Modify windows registry to disabled usb hub port per device basis.

Instructions could be found on Microsoft KB 2401954.

Easy Steps:

I have followed the instructions above and power off all USB devices globally on my Windows 7. As the task includes using registry editor (regdit), it is only recommended for Advance user or if you know what you are doing.

 Figure 1.0 Added registry entry to shut down USB globally upon successful device eject

I have made things easier by creating the registry script below. So just run the registry file and off you go.

1) Download the registry script to a folder.

2) Rename the file from GlobalUsbDisabled.reg1 to GlobalUsbDisabled.reg. The reason is some antivirus might block file with extension reg.

3) Double click on the file and click ok to proceed. You should see the effect after next restart.

SQL Server Table Fragmentation

How do you find out the % fragmentation of your table? 

SQL Server stores your table data rows on data page that is normally 8Kb/Page. These 8k pages form a 64Kb container call Extent.

use dbcc showcontig ('YOUR_TABLE_NAME').


If it's fragmented, then run DBCC REINDEX. DBCC REINDEX reorganises your database table and index like defraging your hard drive.


This table describes the information in the result set. Microsoft owns the following tables. More info here.

Statistic	Description
Pages Scanned	Number of pages in the table or index.
Extents Scanned	Number of extents in the table or index.
Extent Switches	Number of times the DBCC statement moved from one extent to another while it traversed the pages of the table or index.
Avg. Pages per Extent	Number of pages per extent in the page chain.
Scan Density [Best Count: Actual Count]	Best count is the ideal number of extent changes if everything is contiguously linked. Actual count is the actual number of extent changes. The number in scan density is 100 if everything is contiguous; if it is less than 100, some fragmentation exists. Scan density is a percentage.
Logical Scan Fragmentation	Percentage of out-of-order pages returned from scanning the leaf pages of an index. This number is not relevant to heaps and text indexes. An out of order page is one for which the next page indicated in an IAM is a different page than the page pointed to by the next page pointer in the leaf page.
Extent Scan Fragmentation	Percentage of out-of-order extents in scanning the leaf pages of an index. This number is not relevant to heaps. An out-of-order extent is one for which the extent containing the current page for an index is not physically the next extent after the extent containing the previous page for an index.
Avg. Bytes free per page	Average number of free bytes on the pages scanned. The higher the number, the less full the pages are. Lower numbers are better. This number is also affected by row size; a large row size can result in a higher number.
Avg. Page density (full)	Average page density (as a percentage). This value takes into account row size, so it is a more accurate indication of how full your pages are. The higher the percentage, the better.

WatiN Login automation

How could you automate web application screen testing?

- Write unit test in Selenium or WatiN (pronounce what-in).

Selenium has action replay feature and Firefox plugin to record a series of steps then generating the script for you. However, my example would be using WatiN as it's lightweight and the project dependency is only 1 library (WatiN.Core). Simple.

The following code demonstrates a unit test script to login to Google account (via Internet Explorer).

1. Download WatiN from here. Extract the WatiN.Core and add this lib to your project reference.
2. Add a test method as below. Fill in a valid Email and Passwd (replace me.gmail.com and xxx).


[TestMethod]
public void LoginGoogle()
{

using(IE _ie = new IE("https://www.google.com/accounts/ServiceLogin?service=mail&passive=true&rm=false&continue=http%3A%2F%2Fmail.google.com%2Fmail%2F%3Fui%3Dhtml%26zy%3Dl&bsv=1eic6yu9oa4y3&scc=1&ltmpl=default&ltmplcache=2"))
{
_ie.TextField(tf => tf.Name.Equals("Email")).TypeText("me@gmail.com");
_ie.TextField(tf => tf.Name.Equals("Passwd")).TypeText("xxx");

_ie.Button(Find.ByValue("Sign in")).Click();

_ie.Link(Find.ByText("Sign out")).Click();
_ie.Close();

}

}

3. Right click on the TestMethod and click Run Tests. Result as follow (click on picture to enlarge) :

4. What you might ask. How do I find out the textbox's name for the test page?
You could use Firefox Firebug or IE7 Developer Tool to find that out.

Ninject 2 MVC Extension binary download

MVC Unit Testing

I have been checking out how to unit test MVC2 web app. Found Ninject. 

Ninject is a dependency injection framework like RhinoMock, Moq. It enables you to unit test your code by injecting mock object at run-time. Nice feature as it is light-weight and no xml dependencies writing.

Git-ting Ninject is a pain

Navigated to Ninject MVC Extension source file on github using browser window. You should see a bunch of source code folders. Navigate into mvc2. 

You have to use the git client to download, compile  the web.mvc library. Not good. Me no like git-ting.

I gave up on the git-ting task after sometimes. Then I found out binaries could be downloaded from github TeamCity build server. Hurray!! No build!! :)

Where to download the binaries?

Click on this link Ninject web mvc2  and login as guest. Click on the artifact link and you should see the binaries. 

Unzip and reference the Ninject.dll and Ninject.Web.Mvc.dll in visual studio. Voila! You are ready to Ninject.