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

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.

Select distinct rows and sum columns in DataTable

How to filter distinct row from a DataTable column?

Use the [DataView].ToTable (bool, string[]) method. The following code returns a distinct category (no repeated rows in same category).

Example:

DataTable newTable = view.ToTable(true, "Category");

How to sum column total from a DataTable?

Use the DataTable Compute () function. This method performs aggregate function on the DataTable based on the filter criteria.

Example:

newTable.Compute("Sum(Sales)","Category='Pasta'");

How to open network connections window

To open "Network Connections" window via command prompt

Type npca.cpl in Start->Run textbox (works with WinXP and Windows 7).

To open "Add Remove program" window via command prompt

Type appwiz.cpl in Start->Run textbox (works with WinXP and Windows 7).

How to terminate remote desktop session

List the session id on the host server
qwinsta /server:[host]


Terminate the specific rdp session on the host
rwinsta [sessionID] /server:[host]

How to find referenced table in MS SQL 2005?

SELECT so.name
FROM sysobjects so INNER JOIN
sysreferences sr ON so.id = sr.rkeyid
WHERE (so.type = 'U') AND (sr.fkeyid = OBJECT_ID('Customer'))

Result:
name
CustomerGroup
CustomerCountry
Rating
CustomerType

How to edit a pdf file?

What do you normally use to edit a pdf file? Adobe Acrobat? Or create/edit the document in Microsoft Word and save it as pdf document?

Why not check out Foxit PdfEdit? A light weight pdf editor.

More information from this site.

Try 6 months free license by clicking on the link below.

How to hide vista users at Welcome screen

1. Type regedit to run Registry Editor
2. Navigate to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Winlogon\SpecialAccounts\UserList.
3. Create folder for SpecialAccounts or UserList in the registry order above if it's not found. Right click on the Winlogon folder in registry editor and select new folder to create the folder.
4. Create a new DWORD(32 bits for 32bits vista os) key with value 0 (as default) under UserList registry folder. Name the key as the vista login you would like to hide.
5. For example, if your vista login name is Belacan. Then the new key should be belacan (case in-sensitive).
   
6. The DWORD value 0 indicates hiding. You can set the value to 1 if you would like to show it in the future.
   

Disable Vista 'Need your permission to continue'

Whenever a user performs what window think it potentially risky operation, the following message is displayed to warn and asked for confirmation.



How to disable it?

1. Open control panel, type user account in the search box.
2. Click on 'Turn User Account Control (UAC) on or off.
3. Uncheck the the tickbox and restart window.
4. Voila. No more annoying pop-up.
   

How to find out current logon username and domain (via dos command)

1. Open command prompt window (by typing in cmd in Start-> Run textbox).
2. Type echo %userdomain%\%username% and press enter.
   

Teamviewer - Free Remote Desktop apps

TeamViewer is the fast, simple and friendly solution for remote access over the Internet. It can even bypass firewall as it uses http tunneling. It enable remote access, remote presentation, traning, file transfer, chat and more.

Cost: Free for non-commercial use.

For more information, go to TeamViewer website.

Scenario:
Bob tries to remotely view/control Susan's machine for virus troubleshooting.

What to do?
For Susan

• Download the TeamViewerQS and run it (Click on the link to download).
• Tell Bob the session id and password (depicted as the screen below).
• 

For Bob

• Download TeamViewer (full version) and run it.
• Connect using the session id and password provided by Susan.
  

How to add/remove program via Run command

Click Start->Run, type in appwiz.cpl.

Netmeeting to share desktop

There is an easier way to share the screen with another computer user. VNC is one of the choice but sometimes required port 5900 set up correctly through the firewall/router.

Why not try netmeeting?

1) How to set up
Type 'conf' (without the quotation mark) in the Start -> Run dialog. Go through the default configuration until the netmeeting application appears.

The remote user does the same. Type 'conf' in the run menu.

2) How to connect?

Click on netmeeting menu Help-> About window netmeeting
. This will show your ip address.

The remote user types the ip address (destination machine ip) in the and press the call button.