Dealing with null in sql queries in SQL Server 2000/2005

We may need to deal with null values often when we write queries in SQL Server 2000/2005. Here I provide some examples that are commonly used. One of my friend asked how to check whether a field is null, he tried col1=null but didn't worked. So I thought I will share this small but useful examples to all.

1) To check whether a field/column value is null
Just check if it is null

Eg:
select * from tbl where col1 is null;

2) To check whether a field/column value is not null
Just check if it is not null

Eg:
select * from tbl where col1 is not null;

3) To set a field/column value to null (update a field value to null)
Just set it to NULL

Eg:
update tbl set col1= NULL Where val1 = 1;

Another important Note:
When we want to take the maximum value (max) or the total(sum) using aggregate functions, it is always good to check for null values.If the table do not have any records or the result of the query is null, this may result in error.

select @nextVal = max(col1) + 1 from table1; --Not preffered way

So we will try
select @nextVal = max(isnull(col1,0)) + 1 from table1; --Not always success
Hey, this is also not working...

Ok finally, the right way
select @nextVal = isnull(max(col1),0) + 1 from table1;

To find the sum
select @Total = isnull(sum(col1),0) from table1;

Happy Coding ....
:)

Calling an exe from Windows Service

Some times we may need to call an exe from our windows service program. The following code can be used to call an exe from an appication. The sample code is for VB.NET applications. Here the calculator (calc.exe) is called.

Dim startInfo As System.Diagnostics.ProcessStartInfo
Dim pStart As New System.Diagnostics.Process

'Start the process

startInfo = New System.Diagnostics.ProcessStartInfo("calc.exe")

pStart.StartInfo = startInfo

pStart.Start()

pStart.WaitForExit()

pStart.Close()

Here
pStart.WaitForExit() makes your application wait till the called exe is closed.
pStart.Close() will free all the resources that are associated with this component.

This will work if you are using a windows Application. But in a windows service this will not work straight, you need to change some setting for the windows service. A service won't be able to show the GUI of an application, so if the application you are calling has any GUI you won't be able to see it. But the process will get kicked off and you can see the evidence in TaskManager. But we can enable the service to popup the UI by enabling this settings.

Allow Services to interact with desktop

1. Start > Run > Services.msc
2. Locate the services which you have created
3. Right click and select properties.
4. Select "Log On" tab on the top of the screen
5. Select "Allow Services to interact with desktop" check box.
6. Click on "Apply" and start the services.

If you want to kill an application during Windows Service Stop, try the following code

Dim myProcess As Process

Dim myProcesses As Process() = Process.GetProcessesByName("calc")

For Each myProcess In myProcesses

myProcess.Kill()

Next

How to send email using C#

This article explains the steps to send email from your c# windows applications.

Send Email using C#.NET (Visual Studio 2005)

A detailed explanation of SmtpClient Class from msdn .NET Framework Developer Center is provided in the following link.

http://msdn.microsoft.com/en-us/library/system.net.mail.smtpclient.aspx

Show or Hide system tables/stored procedures in SQL Server Enterprise Manager

Some times you may need to see the system tables or system stored procedures/views or may want to hide it so that only user created objects will be visible.
Follow these Steps...
In the SQL Server Enterprise Manager,

• Right-click the server node under "SQL Server Group" (Say Local)
• Select "Edit SQL Server Registration properties..."
• Check/UnCheck "Show system databases and system objects"
• Click Ok, thats it Done.

This will work for SQL Server 7.0 and SQL Server 2000.

Remove spyware from your system

Spyware programs are malicious softwares that can

• Collect different types of personal information
• Install additional softwares
• Redirect your browser activity
• Access websites blindly that will cause more harmful viruses
• Can even change computer settings, resulting in slow connection speeds

If you are a regular internet user, and feel any of the symptoms, there is a chance to have spyware in your system. In such cases it is good to to scan your system with a spyware removal tool. Spybot - Search & Destroy is a good tool and it is free. The good thing is you can keep on updating the definitions so you can be safe from even new spyware.

Here is their official website
http://www.spybot.info/index2.html

Spybot - Search & Destroy can detect and remove spyware of different kinds from your computer. Spyware is a relatively new kind of threat that common anti-virus applications do not cover yet. If you see new toolbars in your Internet Explorer that you didn't intentionally install, if your browser crashes, or if you browser start page has changed without your knowing, you most probably have spyware. But even if you don't see anything, you may be infected, because more and more spyware is emerging that is silently tracking your surfing behaviour to create a marketing profile of you that will be sold to advertisement companies. Spybot-S&D is free, so there's no harm in trying to see if something snooped into your computer, too :)

Spybot-S&D can also clean usage tracks, an interesting function if you share your computer with other users and don't want them to see what you worked on. And for professional users, it allows to fix some registry inconsistencies and offers some extended reporting features.

Some Cool Text Editors

Text editors are normally used for editing plain text files. Some text editors are having very simple interface and nice features like syntax coloring. Crimson Editor is one of my favorite text editors. You can have a look at some of the best text editors.

Crimson Editor

Crimson Editor is a professional source code editor for Windows.This program is not only fast in loading time, but also small in size (so small that it can be copied in one floppy disk).While it can serve as a good replacement for Notepad, it also offers many powerful features for programming languages such as HTML, C/C++, Perl and Java.
http://www.crimsoneditor.com/

EditPad Lite

EditPad Lite is a general-purpose text editor, designed to be small and compact, yet offer all the functionality you expect from a basic text editor. EditPad Lite works with Windows NT4, 98, 2000, ME, XP and Vista.
EditPad Lite is free for non-commercial use.
http://www.editpadpro.com/editpadlite.html

NoteTab

NoteTab is a leading-edge text and HTML editor. Winner of software industry awards since 1998, this application does it all: it easily handles a stack of huge files; lets you format text to your heart's content; does system-wide searches, and multi-line global replacements. It even corrects your spelling mistakes.
http://www.notetab.com/

jEdit

jEdit is a mature programmer's text editor with hundreds (counting the time developing plugins) of person-years of development behind it.
While jEdit beats many expensive development tools for features and ease of use, it is released as free software with full source code, provided under the terms of the GPL 2.0.
http://www.jedit.org/

Which is your Web Browser?

What is your expectations about a web browser? A nice user interface, best functionalities, easy navigation, Tabbed Browsing, customizable, faster... . Compare different browsers and dowload your favourite browser. Most common browsers are listed here with the links to their downloads. We will be actually confused about what to download when we compare these browsers because each one is rich in features. The best option is to try all one by one ....

Internet Explorer 7

Internet Explorer 7 provides improved navigation through tabbed browsing, web search right from the toolbar, advanced printing, easy discovery, reading and subscription to RSS feeds, and much more.

IE 7

Opera Browser

Opera is a super fast and free Web browser. Designed to make your life easier!
Why Opera? Safe, secure, powerful and fully customizable, the Opera Web browser is faster and more secure than other browsers available on the market. Opera delivers robust security and a far-richer feature set than any other Web browser and it's free.

http://www.opera.com/

Firefox

Firefox 2 is the next-generation release of the award-winning Firefox web browser from Mozilla.Features include Improved Tabbed Browsing, Spell Checking, Search Suggestions, Session Restore, Web Feeds (RSS), Live Titles, Integrated Search, Live Bookmarks, Pop-up Blocker, Streamlined Interface, Accessibility

Firefox keeps you safe from spyware, hackers, scammers and spammers, using the power of a vigilant and passionate community to protect you 24/7. Provides facilities like Phishing Protection, Open Source, More Secure, Automated Update, Protection from Spyware, Clear Private Data.

http://www.mozilla.com/en-US/

Apple - Safari

It means ...

Blazing Performance, Elegant User Interface, Easy Bookmarks , Pop-up Blocking , Inline Find , Tabbed Browsing , SnapBack, Forms AutoFill , Built-in RSS , Resizable Text Areas , Private Browsing , Security.

http://www.apple.com/safari/

Avant Browser

Avant Browser's user-friendly interface brings a new level of clarity and efficiency to your browsing experience, and frequent upgrades have steadily improved its reliability. Avant Browser is freeware That's right. 100% Free! No cost to you ever. No limitations. No Adware. No Spyware.
Top Rated Features of Avant Browser includes Online Profile Storage, AutoFills, Flash Animation Filter, Built-in AD/Pop-up Blocker,Built-in RSS/ATOM Reader, Additional Mouse Functions, Multi-Window Browsing, Real Full Screen Mode and Alternative Full Desktop mode, Built-in Search Engine, Full IE Compatibility, Control Your Privacy, Safe Recovery, Customizable Skins.

http://www.avantbrowser.com/

System Optimization and Privacy Tools

There are many system optimization and privacy tools available for free download. These tools remove unused files from your system and enables your system to perform much faster. These tools include registry operations, so you should read the help files carefully before attempting with these tools.

CCleaner

CCleaner is a freeware system optimization and privacy tool. It removes unused files from your system - allowing Windows to run faster and freeing up valuable hard disk space. It also cleans traces of your online activities such as your Internet history. But the best part is that it's fast (normally taking less than a second to run) and contains NO Spyware or Adware! :)

http://www.ccleaner.com/

Other Similar types

iSysCleaner :

iSysCleaner is a free ware system optimization tool. It removes all unused files from your system with few clicks and faster - freeing up valuable hard disk space.

http://www.scanwith.com/download/iSysCleaner.htm

IceClean - For Mac OS :

IceClean is a powerful System optimization tool using ONLY built-in Unix system tasks to help your Mac stay healthy and to keep it running smoothly.

http://www.soft32.com/download_212414.html