2010-02-05
Troubleshooting Program Deployment By Using Verbose Logging
Troubleshooting Program Deployment By Using Verbose Logging
When a program that is deployed through Group Policy is not installed correctly on a client computer, a log file can be generated that records the steps of the Group Policy Application Deployment component leading up to the unsuccessful installation. This information, in conjunction with logging from the Windows Installer service, can be very helpful in determining the cause of the problem.
To enable verbose logging of Application Deployment operations, add the following registry value on the client computer. Delete this value after troubleshooting to avoid performance degradation:
Key: HKEY_LOCAL_MACHINE\Software\Microsoft\Windows NT\CurrentVersion\Diagnostics
Note that the you may need to create the Diagnostics key.
Value: AppMgmtDebugLevel
Type: REG_DWORD
Data: 4b
The logging file is named Appmgmt.log and is located in the %SystemRoot%\Debug\Usermode folder on the client computer.
http://support.microsoft.com/kb/246509/EN-US
When a program that is deployed through Group Policy is not installed correctly on a client computer, a log file can be generated that records the steps of the Group Policy Application Deployment component leading up to the unsuccessful installation. This information, in conjunction with logging from the Windows Installer service, can be very helpful in determining the cause of the problem.
To enable verbose logging of Application Deployment operations, add the following registry value on the client computer. Delete this value after troubleshooting to avoid performance degradation:
Key: HKEY_LOCAL_MACHINE\Software\Microsoft\Windows NT\CurrentVersion\Diagnostics
Note that the you may need to create the Diagnostics key.
Value: AppMgmtDebugLevel
Type: REG_DWORD
Data: 4b
The logging file is named Appmgmt.log and is located in the %SystemRoot%\Debug\Usermode folder on the client computer.
http://support.microsoft.com/kb/246509/EN-US
2010-02-04
Microsoft FTP site
Looking for those legacy tools for win98, win2000, IIS4, NT4? Try this site:
ftp://ftp.microsoft.com
ftp://ftp.microsoft.com
2010-02-03
How to delete and recreate the user profile on the local computer (Vista)
How to delete and recreate the user profile on the local computer (Vista)
1. restart the computer and logon with an administrator account.
2. Rename (or delete) the existing user profile folder: "c:\users\xxxxx"
3.Open Registry Editor (regedit.exe)
4. First, export to backup the registry key: HKLM\SOFTWARE\Microsoft\Windows NT\CurrentVersion\ProfileList
5. Then, expand the ProfileList and delete the key which the Data of ProfileImagePath is “C:\users\xxxxxx”
6. Log off and try to logon with the problematic account.
1. restart the computer and logon with an administrator account.
2. Rename (or delete) the existing user profile folder: "c:\users\xxxxx"
3.Open Registry Editor (regedit.exe)
4. First, export to backup the registry key: HKLM\SOFTWARE\Microsoft\Windows NT\CurrentVersion\ProfileList
5. Then, expand the ProfileList and delete the key which the Data of ProfileImagePath is “C:\users\xxxxxx”
6. Log off and try to logon with the problematic account.
2010-01-29
Failed to open EXE files in Vista
Symptoms:
• The EXE file opens in Notepad.
• EXE file opens in some other installed programs.
• Windows displays "Open With" dialog box to choose the program.
• Windows shows error message, "Windows cannot find .exe file. Make sure you typed the name correctly".
Solution:
1. Open a DOS prompt window:
Press CTRL+ALT+DEL keys and open "Task Manager". Hold down the "CTRL" key and click on "New Task" button and it'll open a Command Prompt window.
If your login account doesn’t have admin right, then you need to do this by opening a new DOS window with “runas” command from the DOS window:
runas /user:DomainXXXXX\AdminAccountYYYYY cmd
it will open another DOS window with your admin account.
From the new DOS window, use “regedit” to open the registry editor.
2. Go to “HKEY_CLASSES_ROOT\.exe”. In the right-side pane, change the value of “Default” key to "exefile"
3. Go to “HKEY_CLASSES_ROOT\exefile\shell\open\command”. In the right-side pane, change the value of “Default” key to: "%1" %*
4. Exit Registry Editor and reboot.
• The EXE file opens in Notepad.
• EXE file opens in some other installed programs.
• Windows displays "Open With" dialog box to choose the program.
• Windows shows error message, "Windows cannot find .exe file. Make sure you typed the name correctly".
Solution:
1. Open a DOS prompt window:
Press CTRL+ALT+DEL keys and open "Task Manager". Hold down the "CTRL" key and click on "New Task" button and it'll open a Command Prompt window.
If your login account doesn’t have admin right, then you need to do this by opening a new DOS window with “runas” command from the DOS window:
runas /user:DomainXXXXX\AdminAccountYYYYY cmd
it will open another DOS window with your admin account.
From the new DOS window, use “regedit” to open the registry editor.
2. Go to “HKEY_CLASSES_ROOT\.exe”. In the right-side pane, change the value of “Default” key to "exefile"
3. Go to “HKEY_CLASSES_ROOT\exefile\shell\open\command”. In the right-side pane, change the value of “Default” key to: "%1" %*
4. Exit Registry Editor and reboot.
2010-01-25
Acess denied error with psexec
You got Access Denied error message with below two scenarios.
Scenario one:
You ran the below command logged in with your Domain\UserName onto localmachineA
psexec \\remoteserverA -i c:\windows\system32\cmd /k dir \\remoteserverB\share
Scenario two:
Firstly you ran the below commdn to open a "cmd" command prompt from remote machine:
psexec \\remoteserverA CMD
After that, you ran "net view \\remoteserverB" and you got access denied.
The reason behind this is that by logging in implicitly you will loose the network resources on \\target that you would have if you had logged on explicitly.
What is "implicit login" and what is "explicit login"?
There is an important difference between using implicit versus explicit logon:
"psexec \\target some_command" uses implicit logon to \\target.
I.e. the user logged in locally will be used to logon to \\target. But logging in implicitly he will loose the network resources on \\target that he would have if he had logged on explicitly.
As -e was not specified his environment will be that of the ".default" user on \\target. Check e.g. the variable %userprofile%.
"psexec \\target -u user - p passw some_command" uses explicit logon to \\target.
psexec is told to explicitly identify as user "user" on \\target (even if he is the same user "user" logged in locally). So network resources assigned to user "user" on \\target may be available.
As -e was not specified his environment will be that of the ".default" user on \\target. Check e.g. the variable %userprofile%.
psexec \\target -u user -p passw -e some_command uses explicit logon to \\target and loads the environment of user "user".
Same as above plus:
As -e was specified the environment on \\target will be that of user "user", not the one of user ".default".
NOTE:
Starting with psexec v1.80 the default behaviour changed! The default now is to load the correct user environment. Specifying -e will keep psexec from loading the user's environment and use the .default environment instead.
To find out more details:
http://forum.sysinternals.com/forum_posts.asp?TID=5072&PN=1
Scenario one:
You ran the below command logged in with your Domain\UserName onto localmachineA
psexec \\remoteserverA -i c:\windows\system32\cmd /k dir \\remoteserverB\share
Scenario two:
Firstly you ran the below commdn to open a "cmd" command prompt from remote machine:
psexec \\remoteserverA CMD
After that, you ran "net view \\remoteserverB" and you got access denied.
The reason behind this is that by logging in implicitly you will loose the network resources on \\target that you would have if you had logged on explicitly.
What is "implicit login" and what is "explicit login"?
There is an important difference between using implicit versus explicit logon:
"psexec \\target some_command" uses implicit logon to \\target.
I.e. the user logged in locally will be used to logon to \\target. But logging in implicitly he will loose the network resources on \\target that he would have if he had logged on explicitly.
As -e was not specified his environment will be that of the ".default" user on \\target. Check e.g. the variable %userprofile%.
"psexec \\target -u user - p passw some_command" uses explicit logon to \\target.
psexec is told to explicitly identify as user "user" on \\target (even if he is the same user "user" logged in locally). So network resources assigned to user "user" on \\target may be available.
As -e was not specified his environment will be that of the ".default" user on \\target. Check e.g. the variable %userprofile%.
psexec \\target -u user -p passw -e some_command uses explicit logon to \\target and loads the environment of user "user".
Same as above plus:
As -e was specified the environment on \\target will be that of user "user", not the one of user ".default".
NOTE:
Starting with psexec v1.80 the default behaviour changed! The default now is to load the correct user environment. Specifying -e will keep psexec from loading the user's environment and use the .default environment instead.
To find out more details:
http://forum.sysinternals.com/forum_posts.asp?TID=5072&PN=1
Useful psexec command
Useful psexec command
PsExec is part of a growing kit of Sysinternals command-line tools that aid in the adminstration of local and remote systems named PsTools.
Utilities like Telnet and remote control programs like Symantec's PC Anywhere let you execute programs on remote systems, but they can be a pain to set up and require that you install client software on the remote systems that you wish to access. PsExec is a light-weight telnet-replacement that lets you execute processes on other systems, complete with full interactivity for console applications, without having to manually install client software. PsExec's most powerful uses include launching interactive command-prompts on remote systems and remote-enabling tools like IpConfig that otherwise do not have the ability to show information about remote systems.
1.Download psexec:
http://technet.microsoft.com/en-au/sysinternals/bb897553.aspx
2.Usage:
Usage: psexec [\\computer[,computer2[,...] | @file][-u user [-p psswd]][-n s][-l][-s|-e][-x][-i [session]][-c [-f|-v]][-w directory][-d][-][-a n,n,... ] cmd [arguments]
FAQ: Common PSTools Issues
http://forum.sysinternals.com/forum_posts.asp?TID=15920
3. Examples:
The following command launches an interactive command prompt on \\marklap:
psexec \\marklap cmd
This command executes IpConfig on the remote system with the /all switch, and displays the resulting output locally:
psexec \\marklap ipconfig /all
PsExec is part of a growing kit of Sysinternals command-line tools that aid in the adminstration of local and remote systems named PsTools.
Utilities like Telnet and remote control programs like Symantec's PC Anywhere let you execute programs on remote systems, but they can be a pain to set up and require that you install client software on the remote systems that you wish to access. PsExec is a light-weight telnet-replacement that lets you execute processes on other systems, complete with full interactivity for console applications, without having to manually install client software. PsExec's most powerful uses include launching interactive command-prompts on remote systems and remote-enabling tools like IpConfig that otherwise do not have the ability to show information about remote systems.
1.Download psexec:
http://technet.microsoft.com/en-au/sysinternals/bb897553.aspx
2.Usage:
Usage: psexec [\\computer[,computer2[,...] | @file][-u user [-p psswd]][-n s][-l][-s|-e][-x][-i [session]][-c [-f|-v]][-w directory][-d][-
FAQ: Common PSTools Issues
http://forum.sysinternals.com/forum_posts.asp?TID=15920
3. Examples:
The following command launches an interactive command prompt on \\marklap:
psexec \\marklap cmd
This command executes IpConfig on the remote system with the /all switch, and displays the resulting output locally:
psexec \\marklap ipconfig /all
2010-01-23
iPhone jailbreak steps
1.Preparation:
1.1.backup iphone:
http://support.apple.com/kb/HT1766
Right-click (or Control-click) the iPhone or iPod touch in iTunes under Devices and choose Back Up.
1.2.download iphone 3.1.2 firmware
http://appldnld.apple.com.edgesuite.net/content.info.apple.com/iPhone/061-7265.20091008.Xsd32/iPhone1,2_3.1.2_7D11_Restore.ipsw
1.3. download blackra1n
http://www.blackra1n.com/
2.start the break:
from your computer, double click the saved blackra1n
then clicke on "make it rain"
it will show “entering recovery mode...”
then you will see " your jailbreak is done once the device reboots. ....", click "ok" and reboot iphone.
I got below problem at this step:
frome itune you see below error message:
itune has detected an iphone in recovery mode, you must restore this iphone before it can be used with itunes
click on restore from itune, after a while iphone reboots.
(turn off blackrain window before doing so)
I got a white blank screen on iphone and itune says " preparing iphone for restore...."
then itune said: the iphone "iphone" could not be restored. an unkonw error occurred (13)
I figured out this was because a network issue in my home network, after fixed the network and tried it again, the restore worked fine.
BTW, the restore did ask for firmware package location and it extracted it by itself.
then itune shows " restoreing iphone firmware......."
After a while and a auto reboot, everything is ok.
Now I can get into iphone as usual, but I found everything is factory setting and all data lost.
I need to resotre from my data backup.Went to itune, click restore backup and choose the right
backup name, few minutes later everything done.
3. install cydia
Once blackra1n is installed properly that means jailbreak done.
click "balckra1n" from iphone, and then choose to install cydia. make sure you have network connected with your computer.
Note, my iphone was unlocked by the vendor before jailbreak.
references:
http://zone.it.sohu.com/forums/thread-3626476-1-1.html
http://zone.it.sohu.com/forums/thread-3626476-1-1.html
(both are very detailed and very good, but in Chinese only)
http://hi.baidu.com/%BA%D3%EB%E0%D3%E3/blog/item/88b4ba03d957f17c3812bb6f.html
English ones:
http://www.iphonedownloadblog.com/2009/10/11/jailbreak-iphone-blackra1n-tutorial/
http://macx.cn/a/a605i1180449.htm
Install tomtom map instruction:
Step 1 : Have an iPhone. Make sure you have OS 3.0
Step 2: Jailbreak iPhone - Follow exact instructions here:
http://www.iphonedownloadblog.com/2009/10/11/jailbreak-iphone-blackra1n-tutorial/
Step 3: Enter Cydia on the iPhone, go to manage, go to sources, click edit (top right), click add (top left) and type : http://cydia.hackulo.us/ (see screenshot) . Ignore Cydia’s silly warnings
Step 4: click search (bottom right), type in ‘installous’. The only result you should get is ‘Installous’(see screenshot). Click this and press ‘install’ (top right)
Step 5: click search again, type in ‘appsync’. The only result you should get is ‘AppSync’(see screenshot). Click this and press install
Step 6: Exit Cydia, and click the new installous icon. Click settings (bottom right). Change ‘Install Method’ to dirty. Toggle ‘iTunes Sync’ to On
Step 7: Download the tomtom app here:
http://thepiratebay.org/torrent/5061144/TomTom_Australia_for_iPhone
Step 8: Drag the app you just downloaded into iTunes
Step 9: Plug your iphone into your computer, it should now sync the app. this can take up to 40 minutes
Step 10: Unplug iPhone and run the tomtom app!
1.1.backup iphone:
http://support.apple.com/kb/HT1766
Right-click (or Control-click) the iPhone or iPod touch in iTunes under Devices and choose Back Up.
1.2.download iphone 3.1.2 firmware
http://appldnld.apple.com.edgesuite.net/content.info.apple.com/iPhone/061-7265.20091008.Xsd32/iPhone1,2_3.1.2_7D11_Restore.ipsw
1.3. download blackra1n
http://www.blackra1n.com/
2.start the break:
from your computer, double click the saved blackra1n
then clicke on "make it rain"
it will show “entering recovery mode...”
then you will see " your jailbreak is done once the device reboots. ....", click "ok" and reboot iphone.
I got below problem at this step:
frome itune you see below error message:
itune has detected an iphone in recovery mode, you must restore this iphone before it can be used with itunes
click on restore from itune, after a while iphone reboots.
(turn off blackrain window before doing so)
I got a white blank screen on iphone and itune says " preparing iphone for restore...."
then itune said: the iphone "iphone" could not be restored. an unkonw error occurred (13)
I figured out this was because a network issue in my home network, after fixed the network and tried it again, the restore worked fine.
BTW, the restore did ask for firmware package location and it extracted it by itself.
then itune shows " restoreing iphone firmware......."
After a while and a auto reboot, everything is ok.
Now I can get into iphone as usual, but I found everything is factory setting and all data lost.
I need to resotre from my data backup.Went to itune, click restore backup and choose the right
backup name, few minutes later everything done.
3. install cydia
Once blackra1n is installed properly that means jailbreak done.
click "balckra1n" from iphone, and then choose to install cydia. make sure you have network connected with your computer.
Note, my iphone was unlocked by the vendor before jailbreak.
references:
http://zone.it.sohu.com/forums/thread-3626476-1-1.html
http://zone.it.sohu.com/forums/thread-3626476-1-1.html
(both are very detailed and very good, but in Chinese only)
http://hi.baidu.com/%BA%D3%EB%E0%D3%E3/blog/item/88b4ba03d957f17c3812bb6f.html
English ones:
http://www.iphonedownloadblog.com/2009/10/11/jailbreak-iphone-blackra1n-tutorial/
http://macx.cn/a/a605i1180449.htm
Install tomtom map instruction:
Step 1 : Have an iPhone. Make sure you have OS 3.0
Step 2: Jailbreak iPhone - Follow exact instructions here:
http://www.iphonedownloadblog.com/2009/10/11/jailbreak-iphone-blackra1n-tutorial/
Step 3: Enter Cydia on the iPhone, go to manage, go to sources, click edit (top right), click add (top left) and type : http://cydia.hackulo.us/ (see screenshot) . Ignore Cydia’s silly warnings
Step 4: click search (bottom right), type in ‘installous’. The only result you should get is ‘Installous’(see screenshot). Click this and press ‘install’ (top right)
Step 5: click search again, type in ‘appsync’. The only result you should get is ‘AppSync’(see screenshot). Click this and press install
Step 6: Exit Cydia, and click the new installous icon. Click settings (bottom right). Change ‘Install Method’ to dirty. Toggle ‘iTunes Sync’ to On
Step 7: Download the tomtom app here:
http://thepiratebay.org/torrent/5061144/TomTom_Australia_for_iPhone
Step 8: Drag the app you just downloaded into iTunes
Step 9: Plug your iphone into your computer, it should now sync the app. this can take up to 40 minutes
Step 10: Unplug iPhone and run the tomtom app!
How to Use Skype over 3G on iPhone
http://blog.tmcnet.com/blog/tom-keating/skype/how-to-use-skype-over-3g-on-iphone.asp
Skype over 3G is disable by most of the carriers hence Skype by default requires WiFi in order to make calls.
this article shows you how to fix that so you can have some nice Skype over 3G action!
How to Use Skype Over 3G on your iPhone
Requirements: Jailbreaking, VoIPover3G, OpenSSH or DiskAid, and Mobile Substrate
Note: You do not need to install Intelliscreen to ensure your modified VoIPover3G.plist file works. The latest MobileSubstrate makes sure that VoIPover3G is enabled and working properly. Previously, you needed to install Intelliscreen as a workaround, but no more.
1. Jailbreak
2. Open Cydia. Search for and install VoIPover3G. This enables you to truck apps that "require" WiFi to also work over your 3G data connection
3. While in Cydia install OpenSSH which will allow you to transfer files from/to the iPhone.
4. Install the latest Mobile Substrate.
5. After all these apps are installed, you should reboot your iPhone.
6. Next, connect your iPhone to your home WiFi network and get the IP address the iPhone was assigned. Click on Settings>WiFi>circuluar blue arrow
7. Pick either DiskAir (option a) or OpenSSH (option b) to modify the iPhone file. (on your pc)
a) DiskAid instructions: (a tool for PCs and Macs which enables to use your iPhone or iPod Touch as external Disk.)
1) Download & install DiskAid
2) Connect iPhone to PC or Mac using USB cable.
3) Drag the /Library/MobileSubstrate/DynamicLibraries/VoIPover3G.plist file to your PC or Mac
4) goto Step 8)
b) OpenSSH (more advanced, but techies love SSH/SCP.
1) Download a SSH/SCP program. For PC users I recommend WinSCP.
2) In WinSCP, make sure to select "SFTP" and enter in this info:
- Server: your iPhone's IP address
- Username: root
- Password: alpine
- Port: 22
3) Click on "Connect", which might take a few seconds to connect to your iPhone.
4) Navigate to: /Library/MobileSubstrate/DynamicLibraries
5) Continue to next step (8.)
8. From your PC, double-click VoIPover3G.plist which will open the file.
9. You will likely see the following text within "VoIPover3G.plist"or something very similar:
Filter = {Bundles = ("com.Fringland.Fring", "com.apple.AppStore", "com.audiofile.Interstate", "com.apple.MobileStore");};
Append "com.skype.skype" to the text (with a comma) which will then look like this:
Filter = {Bundles = ("com.Fringland.Fring", "com.apple.AppStore", "com.audiofile.Interstate", "com.apple.MobileStore", "com.skype.skype");};
10. Now save the modified file. If using WinSCP, it will automatically upload back to your iPhone. If using DiskAid, just drag-and-drop the file back onto the iPhone overwriting the old one.
11. Uninstall the Skype app & reboot your iPhone.
12. Reinstall the Skype app from the Apple App Store
And now you will be able to make Skype over 3G calls. Enjoy!
From your iphone, make a call test to ensure skype over 3g is working. Previously you will get a message saying call over 3g is not available and you need to use wifi to make call. Now the message disappeared!
I should point out that MobileSubstrate along with VoIPover3G.plist enables you to install appstore apps larger than 10mb over the 3g and you can use other Voice over IP apps such as Fring over 3G. It even allows ustream to work over 3G - another app restricted to WiFi only. Good stuff!
Skype over 3G is disable by most of the carriers hence Skype by default requires WiFi in order to make calls.
this article shows you how to fix that so you can have some nice Skype over 3G action!
How to Use Skype Over 3G on your iPhone
Requirements: Jailbreaking, VoIPover3G, OpenSSH or DiskAid, and Mobile Substrate
Note: You do not need to install Intelliscreen to ensure your modified VoIPover3G.plist file works. The latest MobileSubstrate makes sure that VoIPover3G is enabled and working properly. Previously, you needed to install Intelliscreen as a workaround, but no more.
1. Jailbreak
2. Open Cydia. Search for and install VoIPover3G. This enables you to truck apps that "require" WiFi to also work over your 3G data connection
3. While in Cydia install OpenSSH which will allow you to transfer files from/to the iPhone.
4. Install the latest Mobile Substrate.
5. After all these apps are installed, you should reboot your iPhone.
6. Next, connect your iPhone to your home WiFi network and get the IP address the iPhone was assigned. Click on Settings>WiFi>circuluar blue arrow
7. Pick either DiskAir (option a) or OpenSSH (option b) to modify the iPhone file. (on your pc)
a) DiskAid instructions: (a tool for PCs and Macs which enables to use your iPhone or iPod Touch as external Disk.)
1) Download & install DiskAid
2) Connect iPhone to PC or Mac using USB cable.
3) Drag the /Library/MobileSubstrate/DynamicLibraries/VoIPover3G.plist file to your PC or Mac
4) goto Step 8)
b) OpenSSH (more advanced, but techies love SSH/SCP.
1) Download a SSH/SCP program. For PC users I recommend WinSCP.
2) In WinSCP, make sure to select "SFTP" and enter in this info:
- Server: your iPhone's IP address
- Username: root
- Password: alpine
- Port: 22
3) Click on "Connect", which might take a few seconds to connect to your iPhone.
4) Navigate to: /Library/MobileSubstrate/DynamicLibraries
5) Continue to next step (8.)
8. From your PC, double-click VoIPover3G.plist which will open the file.
9. You will likely see the following text within "VoIPover3G.plist"or something very similar:
Filter = {Bundles = ("com.Fringland.Fring", "com.apple.AppStore", "com.audiofile.Interstate", "com.apple.MobileStore");};
Append "com.skype.skype" to the text (with a comma) which will then look like this:
Filter = {Bundles = ("com.Fringland.Fring", "com.apple.AppStore", "com.audiofile.Interstate", "com.apple.MobileStore", "com.skype.skype");};
10. Now save the modified file. If using WinSCP, it will automatically upload back to your iPhone. If using DiskAid, just drag-and-drop the file back onto the iPhone overwriting the old one.
11. Uninstall the Skype app & reboot your iPhone.
12. Reinstall the Skype app from the Apple App Store
And now you will be able to make Skype over 3G calls. Enjoy!
From your iphone, make a call test to ensure skype over 3g is working. Previously you will get a message saying call over 3g is not available and you need to use wifi to make call. Now the message disappeared!
I should point out that MobileSubstrate along with VoIPover3G.plist enables you to install appstore apps larger than 10mb over the 3g and you can use other Voice over IP apps such as Fring over 3G. It even allows ustream to work over 3G - another app restricted to WiFi only. Good stuff!
2010-01-18
Find and/or Delete Duplicate Rows from SQL server
How do I identify duplicate rows?
Group the rows by CustName, Pincode. Rows having similar CustName and Pincode will have more than one rows in the grouping. So locate them using HAVING COUNT(*) > 1. If duplicate values are encountered, return the maximum ID for each duplicate row. Using the outer query, delete any ID returned by subquery.
1.SQL Server 2005 Solution:
-- Find Duplicate Rows
SELECT MAX(ID) as ID, CustName, Pincode FROM #Customers
GROUP BY CustName, Pincode
HAVING COUNT(*) > 1
-- Delete Duplicate Rows
DELETE FROM #Customers
WHERE ID IN
( SELECT MAX(ID) FROM #Customers
GROUP BY CustName, Pincode
HAVING COUNT(*) > 1)
2.-- Find Duplicate Rows
SELECT MAX(ID) as ID, CustName, Pincode FROM #Customers
GROUP BY CustName, Pincode
HAVING COUNT(*) > 1
-- Delete Duplicate Rows
DELETE FROM #Customers
WHERE ID IN
( SELECT MAX(ID) FROM #Customers
GROUP BY CustName, Pincode
HAVING COUNT(*) > 1)
Check out the full article:
http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=DuplicateRows
Group the rows by CustName, Pincode. Rows having similar CustName and Pincode will have more than one rows in the grouping. So locate them using HAVING COUNT(*) > 1. If duplicate values are encountered, return the maximum ID for each duplicate row. Using the outer query, delete any ID returned by subquery.
1.SQL Server 2005 Solution:
-- Find Duplicate Rows
SELECT MAX(ID) as ID, CustName, Pincode FROM #Customers
GROUP BY CustName, Pincode
HAVING COUNT(*) > 1
-- Delete Duplicate Rows
DELETE FROM #Customers
WHERE ID IN
( SELECT MAX(ID) FROM #Customers
GROUP BY CustName, Pincode
HAVING COUNT(*) > 1)
2.-- Find Duplicate Rows
SELECT MAX(ID) as ID, CustName, Pincode FROM #Customers
GROUP BY CustName, Pincode
HAVING COUNT(*) > 1
-- Delete Duplicate Rows
DELETE FROM #Customers
WHERE ID IN
( SELECT MAX(ID) FROM #Customers
GROUP BY CustName, Pincode
HAVING COUNT(*) > 1)
Check out the full article:
http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=DuplicateRows
Subscribe to:
Posts (Atom)
Posts
