Nouman Zakir

Using SELECT statement to UPDATE records in SQL

Nouman Zakir — Tue, 04 Aug 2009 11:28:10 GMT

You can use SELECT statement to update records through UPDATE statement.
In the SET Clause use the SELECT statement along with desired filters to
update the records.

UPDATE tblDestination
SET tblDestination.col=value
WHERE EXISTS (
SELECT col2.value
FROM tblSource
WHERE tblSource.join_col=tblDestination.join_col
AND tblSource.constraint=value)

Using SELECT statement to INSERT records in SQL

Nouman Zakir — Tue, 04 Aug 2009 09:57:24 GMT

In INSERT statement, by replacing the VALUES clause with a SELECT statement
you can get a set of records for INSERT.

Suppose I have a table called table_A and I want to populate it with the ID, Name and
Address of table_B. The statement would look something like this:

INSERT table_A (col_id, col_name, col_address)
SELECT col_id, col_name, col_address
FROM table_B
WHERE col_city = 'karachi'

This will take the records with col_city='karachi' and load them into the
table table_A. I can use any type of SELECT statement here.
It just has to return a record set that matches the columns in the
INSERT statement. The number of columns and their data types must match
(or be implicitly convertible). I can also execute a stored procedure
that returns a record set using the EXEC command in place of the SELECT
statement.

Optional parameters in SQL Stored Procedures

Nouman Zakir — Sat, 28 Feb 2009 11:48:24 GMT

You can add optional parameters in stored procedures by setting a default value for each parameter that you want to make optional. The default value is typically NULL, but it's not necessary.

Example:

CREATE PROCEDURE dbo.mySP
@firstParam VARCHAR(32) = NULL,
@secondParam INT = NULL
AS

BEGIN

SET NOCOUNT ON
SELECT *
FROM [TableName]
WHERE
Param1 = @firstParam
AND Param2 = @secondParam

END
GO

--
EXEC dbo.mySP @firstParam ='bar', @secondParam =4
EXEC dbo.mySP @firstParam ='bar'
EXEC dbo.mySP @secondParam =4
EXEC dbo.mySP 'bar',4
EXEC dbo.mySP 'bar'
EXEC dbo.mySP
GO

DROP PROCEDURE dbo.mySP
GO

Check access to databse in SQL Server

Nouman Zakir — Sat, 28 Feb 2009 11:32:21 GMT

HAS_DBACCESS returns information about whether the user has access to the specified database.

Example:
SELECT HAS_DBACCESS('Northwind');
returns
1 if the user has access to the database
0 if the user does not have access to the database
NULL if the database does not exist

Find all databases that the current user has access to
SELECT [Name] as DatabaseName
FROM master.dbo.sysdatabases
WHERE
ISNULL(HAS_DBACCESS ([Name]),0)=1
ORDER BY [Name]

Microsoft Help and Support

Nouman Zakir — Thu, 19 Feb 2009 08:24:35 GMT

I got this joke in an email from my friend and I couldn't stop laughing.

A plain computer illiterate guy rings tech support to report that his computer is faulty.

Tech: What's the problem?
User: There is smoke coming out of the power supply.

Tech: You'll need a new power supply.
User: No, I don't! I just need to change the start up files.

Tech: Sir, the power supply is faulty. You'll need to replace it.
User: No way! Someone told me that I just needed to change the start up and it will fix the problem! All I need is for you to tell me the command.

Tech support::
10 minutes later, the User is still adamant that he is right. The tech is frustrated and fed up.

Tech: Sorry, Sir. We don't normally tell our customers this, but there is an undocumented DOS command that will fix the problem.
User: I knew it!

Tech : Just add the line
LOAD NOSMOKE -U
at the end of the CONFIG.SYS. Let me know how it goes.

10 minutes later.

User : It didn't work. The power supply is still smoking.
Tech : Well, what version of DOS are you using?

User : MS-DOS 6.22 .
Tech : That's your problem there. That version of DOS didn't come with NOSMOKE. Contact Microsoft and ask them for a patch that will give you the file. Let me know how it goes.

1 hour later.

User : I need a new power supply.
Tech support : How did you come to that conclusion?

User : Well, I rang Microsoft and told him about what you said, and he started asking questions about the make of power supply.
Tech: Then what did he say?

User: He told me that my power supply isn't compatible with NOSMOKE.

Reset table identity (reseed) in SQL Server with DBCC RESEED

Nouman Zakir — Wed, 11 Feb 2009 09:59:34 GMT

You may use DBCC CHECKIDENT to reseed (reset) the identity column of any table in SQL Server.

Example:

A table has 10 rows with 10 as last identity. The next record will automatically genrate 11, to start the new identity use the follwing T SQL query in query analyzer.

DBCC CHECKIDENT (tableName, reseed, 15)

To set the seed to zero use the following T SQL query in query analyzer.

DBCC CHECKIDENT (tableName, reseed, 0)

Avoid using a value lower than the current value, doing this will violate the uniqueness contrant in case of new entries.

Very useful windows run commands

Nouman Zakir — Wed, 11 Feb 2009 09:48:35 GMT

Different Run Commands useful for time saving while using your computer. To invoke the Run box, hold down the Windows key and hit R (or from the Start Menu, choose "Run.") From there type any one of the keywords listed to launch an app: like calc for Calculator or clpbrd for the Clipboard Viewer or freecell for the card game.

Program	Run Command
Accessibility Controls	access.cpl
Accessibility Wizard	accwiz
Add Hardware Wizard	hdwwiz.cpl
Add/Remove Programs	appwiz.cpl
Administrative Tools	control admintools
Adobe Acrobat ( if installed )	acrobat
Adobe Distiller ( if installed )	acrodist
Adobe ImageReady ( if installed )	imageready
Adobe Photoshop ( if installed )	photoshop
Automatic Updates	wuaucpl.cpl
Basic Media Player	mplay32
Bluetooth Transfer Wizard	fsquirt
Calculator	calc
Ccleaner ( if installed )	ccleaner
C: Drive	c:
Certificate Manager	cdrtmgr.msc
Character Map	charmap
Check Disk Utility	chkdsk
Clipboard Viewer	clipbrd
Command Prompt	cmd
Command Prompt	command
Component Services	dcomcnfg
Computer Management	compmgmt.msc
Compare Files	comp
Control Panel	control
Create a shared folder Wizard	shrpubw
Date and Time Properties	timedate.cpl
DDE Shares	ddeshare
Device Manager	devmgmt.msc
Direct X Control Panel ( if installed )	directx.cpl
Direct X Troubleshooter	dxdiag
Disk Cleanup Utility	cleanmgr
Disk Defragment	dfrg.msc
Disk Partition Manager	diskmgmt.msc
Display Properties	control desktop
Display Properties	desk.cpl
Display Properties (w/Appearance Tab Preselected )	control color
Dr. Watson System Troubleshooting Utility	drwtsn32
Driver Verifier Utility	verifier
Ethereal ( if installed )	ethereal
Event Viewer	eventvwr.msc
Files and Settings Transfer Tool	migwiz
File Signature Verification Tool	sigverif
Findfast	findfast.cpl
Firefox	firefox
Folders Properties	control folders
Fonts	fonts
Fonts Folder	fonts
Free Cell Card Game	freecell
Game Controllers	joy.cpl
Group Policy Editor ( xp pro )	gpedit.msc
Hearts Card Game	mshearts
Help and Support	helpctr
Hyperterminal	hypertrm
Hotline Client	hotlineclient
Iexpress Wizard	iexpress
Indexing Service	ciadv.msc
Internet Connection Wizard	icwonn1
Internet Properties	inetcpl.cpl
Internet Setup Wizard	inetwiz
IP Configuration (Display Connection Configuration)	ipconfig /all
IP Configuration (Display DNS Cache Contents)	ipconfig /displaydns
IP Configuration (Delete DNS Cache Contents)	ipconfig /flushdns
IP Configuration (Release All Connections)	ipconfig /release
IP Configuration (Renew All Connections)	ipconfig /renew
IP Configuration (Refreshes DHCP & Re-Registers DNS)	ipconfig /registerdns
IP Configuration (Display DHCP Class ID)	ipconfig /showclassid
IP Configuration (Modifies DHCP Class ID)	ipconfig /setclassid
Java Control Panel ( if installed )	jpicpl32.cpl
Java Control Panel ( if installed )	javaws
Keyboard Properties	control keyboard
Local Security Settings	secpol.msc
Local Users and Groups	lusrmgr.msc
Logs You Out of Windows	logoff
Malicious Software Removal Tool	mrt
Microsoft Access ( if installed )	access.cpl
Microsoft Chat	winchat
Microsoft Excel ( if installed )	excel
Microsoft Diskpart	diskpart
Microsoft Frontpage ( if installed )	frontpg
Microsoft Movie Maker	moviemk
Microsoft Management Console	mmc
Microsoft Narrator	narrator
Microsoft Paint	mspaint
Microsoft Powerpoint	powerpnt
Microsoft Word ( if installed )	winword
Microsoft Syncronization Tool	mobsync
Minesweeper Game	winmine
Mouse Properties	control mouse
Mouse Properties	main.cpl
MS-Dos Editor	edit
MS-Dos FTP	ftp
Nero ( if installed )	nero
Netmeeting	conf
Network Connections	control netconnections
Network Connections	ncpa.cpl
Network Setup Wizard	netsetup.cpl
Notepad	notepad
Nview Desktop Manager ( if installed )	nvtuicpl.cpl
Object Packager	packager
ODBC Data Source Administrator	odbccp32
ODBC Data Source Administrator	odbccp32.cpl
On Screen Keyboard	osk
Opens AC3 Filter ( if installed )	ac3filter.cpl
Outlook Express	msimn
Paint	pbrush
Password Properties	password.cpl
Performance Monitor	perfmon.msc
Performance Monitor	perfmon
Phone and Modem Options	telephon.cpl
Phone Dialer	dialer
Pinball Game	pinball
Power Configuration	powercfg.cpl
Printers and Faxes	control printers
Printers Folder	printers
Private Characters Editor	eudcedit
Quicktime ( if installed )	quicktime.cpl
Quicktime Player ( if installed )	quicktimeplayer
Real Player ( if installed )	realplay
Regional Settings	intl.cpl
Registry Editor	regedit
Registry Editor	regedit32
Remote Access Phonebook	rasphone
Remote Desktop	mstsc
Removable Storage	ntmsmgr.msc
Removable Storage Operator Requests	ntmsoprq.msc
Resultant Set of Policy ( xp pro )	rsop.msc
Scanners and Cameras	sticpl.cpl
Scheduled Tasks	control schedtasks
Security Center	wscui.cpl
Services	services.msc
Shared Folders	fsmgmt.msc
Sharing Session	rtcshare
Shuts Down Windows	shutdown
Sounds Recorder	sndrec32
Sounds and Audio	mmsys.cpl
Spider Solitare Card Game	spider
SQL Client Configuration	clicongf
System Configuration Editor	sysedit
System Configuration Utility	msconfig
System File Checker Utility ( Scan Immediately )	sfc /scannow
System File Checker Utility ( Scan Once At Next Boot )	sfc /scanonce
System File Checker Utility ( Scan On Every Boot )	sfc /scanboot
System File Checker Utility ( Return to Default Settings)	sfc /revert
System File Checker Utility ( Purge File Cache )	sfc /purgecache
System File Checker Utility ( Set Cache Size to Size x )	sfc /cachesize=x
System Information	msinfo32
System Properties	sysdm.cpl
Task Manager	taskmgr
TCP Tester	tcptest
Telnet Client	telnet
Tweak UI ( if installed )	tweakui
User Account Management	nusrmgr.cpl
Utility Manager	utilman
Volume Serial Number for C:	label
Volume Control	sndvol32
Windows Address Book	wab
Windows Address Book Import Utility	wabmig
Windows Backup Utility ( if installed )	ntbackup
Windows Explorer	explorer
Windows Firewall	firewall.cpl
Windows Installer Details	msiexec
Windows Magnifier	magnify
Windows Management Infrastructure	wmimgmt.msc
Windows Media Player	wmplayer
Windows Messenger	msnsgs
Windows Picture Import Wizard (Need camera connected)	wiaacmgr
Windows System Security Tool	syskey
Windows Script host settings	wscript
Widnows Update Launches	wupdmgr
Windows Version ( shows your windows version )	winver
Windows XP Tour Wizard	tourstart
Wordpad	write
Zoom Utility	igfxzoom

Find text in SQL stored procedures

Nouman Zakir — Mon, 02 Feb 2009 06:43:08 GMT

Method to retrieve every text inside the stored procedures of a database

SELECT B.Name, *
FROM syscomments A INNER JOIN sysobjects B
ON A.id = B.id
WHERE A.text like '%tblNames%'

Launch browser to display HTML contents

Nouman Zakir — Mon, 02 Feb 2009 06:13:30 GMT

You can launch the default browser to display HTML content using the Process.Start method in the System.Diagnostics namespace by specifying the URL as the argument. But I don't recommend this approach because sometimes the browser will launch behind your application.

A more reliable option is to use the Help.ShowHelp method to launch HTML content (even if the content is not on-line help). Call Help.ShowHelp with the active control as argument 1 (or use Form.Active Form). Specify the URL as argument 2, and be sure to include the prefix (file:///, http://, https://, etc).

Log uncaught application exceptions

Nouman Zakir — Sun, 25 Jan 2009 09:50:26 GMT

It's very important to log any uncaught exceptions that your ASP.NET application generates. Otherwise you may not be aware of serious problems with your website. You can trap exceptions by writing an Application_Error method in Global.asax. Call Server.GetLastError() to get the exception.I recommend logging the following data so that you'll be able to troubleshoot the problem:

Data	How to Retrieve
Current Time	DateTime.Now
Request IP Address	Request.UserHostAddress
Referring URL (page user was on when the current page was requested)	Request.UrlReferrer
User Agent (browser or spider that issued the request)	Request.UserAgent
User Host Name	Request.UserHostName
All HTTP Headers	Request.Headers
All Form Fields	Request.Form
Exception Details	ex.GetType(), ex.Message, ex.StackTrace, where ex is the exception returned by Server.GetLastError()
Inner Exception Details	Same as above, if ex.InnerException is not null.

If you find yourself getting spurious entries in your exception log that you'd like to suppress, I suggest putting regular expressions in your web.config file that match the entries that you're not interested in. When an uncaught exception occurs, generate the log entry, but then see if any of the regular expressions match the log entry. If any do, don't save the log entry.

Performance counter without overflow

Nouman Zakir — Sun, 18 Jan 2009 12:13:29 GMT

Environment.TickCount returns a 32-bit signed integer containing the amount of time in milliseconds that has passed since the last time the computer was started. But this value can overflow for systems that stay up for days at a time. To avoid this overflow problem, you can query the "System Up Time" performance counter:

public TimeSpan SystemUpTime()
{

PerformanceCounter upTime = new PerformanceCounter("System", "System Up Time");

// You've got to call this twice. First time it returns 0 and the second time it returns the real info.
upTime.NextValue();

return TimeSpan.FromSeconds(upTime.NextValue());
}

You need proper privileges to query performance counter.

Save Configuration Settings in Registry

Nouman Zakir — Mon, 12 Jan 2009 03:58:59 GMT

First, go to AssemblyInfo.cs and remove all asterisks from the AssemblyVersion:
[assembly: AssemblyVersion("1.0.0.0")]

Otherwise, every time you build your application the registry key will change.
Save configuration values to the registry as follows:

Application.UserAppDataRegistry.SetValue("Value", Value);

Load configuration values to the registry as follows:

try
{
Value = (int) Application.UserAppDataRegistry.GetValue("Value");
}
catch(Exception)
{
}

The GetValue method will throw an exception if the specified value does not exist in the registry.

Hello world!

whiletrue — Fri, 02 Jan 2009 23:16:33 GMT

 class HelloWorldApp
 {
     static void Main()
     {
         System.Console.WriteLine("Hello, world!");
     }
 }