Archive for the ‘Tips and Tricks’ Category

Collation precedence conflict in SQL Server 2000

The collation precedence rules apply only to the character string data types, char, varchar, text, nchar, nvarchar, and ntext. Objects with other data types do not participate in collation evaluations.
link to article on msdn
A simple example is as follows

create table #TempRO
Id  int Identity (1,1),
App_No     NVarchar (100)
— insert some values in this table
Select *
from #TempRO T
Inner join App AO
On T.App_No = AO.App_No

We are having the App table using a different collation then the default that is “Arabic_CI_AS”

So the above select will give the following collation error, to remove it we just add the following statement while defining the temp table

create table #TempRO
Id  int Identity (1,1),
App_No     NVarchar (100)    collate Arabic_CI_AS

This not only happens in temp tables but also in the normal tables or at times if we are comparing columns so its better to use sp_help TableName or the syscolumns to find the collation of the two.

Happy Collating 😉


Read Full Post »

In your Visual Studio IDE go to the projects (at the top) -> add references -> com (tab) -> add the Microsoft Excel library installed.


Or you can use the project explorer.

Right click on the references-> add references -> com (tab) -> add the library.


Please refer to the image below.


Then simply what you need to do is Import the Excel.

Imports Microsoft.Office.Interop.Excel for VB

using Microsoft.Office.Interop;              for C#


The “Microsoft Excel X Object Library” where X will depend on whatever version of Excel you have in the computer.
So the X depends on which version of Excel is installed on your machine.

Read Full Post »

A lot of you might have come across this problem just like me.


Firstly we create a temporary table.

create table #temp1


            id Int Identity(1,1),

            invoice varchar (20)


insert into #temp1 values (‘CR’)

insert into #temp1 values (‘CR ‘)

insert into #temp1 values (‘ CR ‘)

insert into #temp1 values (‘ CR’)


Select *  From #temp1

            where DATALENGTH(invoice) <> len(invoice)


The len function in SQL gives you the length of the characters including the spaces white DataLength excludes the trailing not preceding spaces.


Here if you don’t have the column defined as varchar rather you have an nvarchar field then you simply convert then to varchar before calling the respective functions like this.


Select *  From #temp1

            where DATALENGTH(convert(varchar(20),invoice)) <> len(convert(varchar(20),invoice))



Happy searching trailing spaces in your db, please share with us if you have better options.

Read Full Post »

I have been changing machines quite frequently since the last 6 months. And each time I have a new machine I need to setup the environment as I prefer.

The tabs spaces the fonts the immediate window and the list goes on.

But one thing I always forget is where is the setting for the Line Numbers to appear so this blog is for me


Tools -> Options -> Text editor- > All languages -> General -> Display (Line Numbers)


Hope this helps other fellow readers as well.

Read Full Post »

As most of you technology geeks must be using the Windows Server 2008. And a lot might have already ran out of their allocated evaluation period of 60 days J.

 But don’t worry it’s not the end of your exploration with this new enhanced server from Microsoft. If you want to extend the evaluation period it’s really simple. I came across an email from a colleague of mine a great developer rather a master mind; thanks Naweed.


So if your 60 days initial evaluation period nears to end just follow these simple steps to increase the period using the Slmgr.vbs script.


1. Click Start, and then click Command Prompt.

 2. Type slmgr.vbs -dli, and then press ENTER to check the current status of your evaluation period.

 3. To reset the evaluation period, type slmgr.vbs –rearm, and then press ENTER.

 4. Restart the computer.


This resets the evaluation period to 60 days.


You can find more ways to of extending this evaluation here in this kb article:

  1. How to install Windows Server 2008 without activating it
  2. How to manually extend the evaluation period
  3. How to automate the extension of the evaluation period


But keep in mind that you can only extend to a max of 240 days (i.e. 3 extensions)

Read Full Post »

I just started off programming using Windows Server 2008 using Visual Studio 2008. So I came across a number of initail applications that anyone would start off with; a couple of desktop apps and a web app. Just going through some forums I found posts of users asking as to how to turn off the Enhanced Security Configurations for Internet Explorer in Windows Server 2008.
As most of you who might have tried Windows Server 2008 might have seen that the menus and the style is changed a lot as to improve the user experience.
So here it is, simply follow these Steps:
1. Click on the Start -> Administrative Tools -> Server Manager

Strat Menu Windows Server 2008
2. Click on the Configue IE ESC

Configue IE ESC
3. On this pop-up you will find the options to turn off the security configurations.


And you can change the configurations for either users or the administrators, depends on what you need.

I would keep on sharing new tips and tricks that I come accorss in programming as well this is just a configurations change.

Read Full Post »

If you would like to find some charater/s in a column in the table and replace it here is a simple querry.

Note: For beginner please make sure you know what an update statement does. This query is going to update the column values in your table. So if you are just testing please use the Select statement instead. I have added it to the bottome of this article.

Approach 1:
Update T
       Set T.Col1 = Replace (T.Col1, “@” , ‘at’)
       From MyTable T

But If we just want to make it a bit faster depending on the size of your table.
Approach 2:
Update T
       Set T.Col1 = Replace (T.Col1,”@”,’at’)
       From MyTable T
       Where T.Col1 like (“@”)

You can have a hang of what the Replace functions does from the following link. But I would give you a little idea over here too. The Replace function look for the string that you have provided as the second parameter in the table column or string expression  that you have provided as the first parameter and simply replaces that string with the third parameter.

*Select Replace(T.Col1,”@”, ‘at’) As [ColName]
      From MyTable T


Read Full Post »

Older Posts »