SQL COALESCE

I find it difficult to use this function. Its use is really limited.

The function coalesce(esp1,esp2....) is actually an expansion of NVL(esp, replacement) in some language.

Both function checks for NULL. NVL() is translated to if "esp" is null then use "replacement" otherwise use "esp". Coalesce() extends the "if" to many evaluations and has no "replacement" unless you specifically define one expression that is always true.

What then is the use of coalesce()? Basically, it allows you to return a value from a list of expressions in the order that you defines it. It will pick up the value of the first expression that return a NOTNULL.

In a more English term, the function allows you to choose the first field from a list of fields that is not empty.

In practice, I can't find a real use of it. At least not for the field of work I am in. However, I can imagine a practical use. For example, I have a list of prices for a given part. The prices are select in the order of preferences refurbished, local, regional and worldwide. If the higher preference price is not available then the next lower price preference is used. Usually, the different prices are not compiled. Rather, it is taken from joins that refers to different tables which are updated independently. Thus it is normally unknown if a particular preference has a value. Obviously, there must be another coalesce to indicate where the source of the price is from.

Another example is as follows.

I have a list of free gifts which I need to keep track of the stock. When customer indicates a choice. It is updated into the database at the particular choice field. I am then able to simply "count" the different choices made by customers. At the same time, I am able to view the choices made by individual customer. Two actions with just one update.

Choosing dates in SQL

If there is a datetime field in your database, chances is that you would want to choose a period between dates. There are more than one way to choose a period.

First I would show the wrong way.

Select mydate from mytable where datefield > '1/1/1900' and datefield < '1/31/1900'

This SQL is not wrong by itself. However, without time qualification, the default time is 00:00:00. It then posts a problem. ">" actually means greater than. This means that the date 1/1/1900 00:00:00 is excluded. "<" refers to less than. This means that the date 1/31/1900 00:00:00 and above will not be included too. In English, this condition means "exclusive" term.

A more correct way is as follows.

select my date from mytable where datefield >= '1/1/1900' and datefield < '2/1/1900'

Another way to select a period is as follows.

select my date from mytable where datefield between '1/1/1900' and '1/31/1900'

I am not sure if that is correct too since the time definition on the second date is missing. To be more exact the second date should be 1/31/1900 23:59:59.

Vlan Tag

Read from Blogger (mili liew) that you don't really have to use 2wire 5012NV for your Singtel NGBN.

There is a blog that details how you can use your own switch to connect to the Optical hub.

The suggested switch used is a "managed switch that support vlan tagging". Vlan tagging is actually a protocol IEEE 802.1Q. The switch must be able to support this tagging.

Tag used as belows.

vlan 10 - Internet

vlan 20 - MIO TV

vlan 30 - VOIP

vlan 40 - Management

I don't quite know what vlan 40 is used for.

Cleanup vista.

My harddisk has only 100G. 15 of which is used for Linux partitioning. There is not much left to play with. Recently, it has been reduced to 20G free space. Some desperate measures has to be done.

First I tried to remove manually all orphans from un-installed programs. It looked like AVG left quite a few GB storage. Don't know what they are used for. On my other pc, it only took up 150MB even without uninstalling.

Next I go to "my computer" right click c:\ and select "properties". Clicked "Disk Cleanup". It seems that there is a way to clean up "hibernation" file from here. Also at "more options" tab, you could clear all shadows except the latest. Shadows are actually "System Restore points". It took up almost 27GB for all the hdd in the computer. 19GB is actually for c:\. I did a "clean up" for "system restore and shadow copies".

I also did a defrag using "defragger". The result is impressive. I had 49GB free now.

SQL Server Replication issues

Still having problem doing the replication.

One thing I notice is that the user had to be both OS admin user and SQL Server Admin user. Just add servername\adminuser as user and allow sysadmin right to it. Subsequently, use this user for all replication.

Another thing is that error 2812 can be caused by a number of reasons. One blogger suggest to use "SP_removedbreplication 'databasename' (not the distribution database rather the actual database to be replicated). I tried that and it actually works.

It is a bad idea to remove the "distribution" database. SQL server does not cleanup after you. The publisher and distributor somehow still retains the information.

Brioquery does not refresh table structure

When you use BrioQuery, be aware that the tables in the sections does not refresh itself. If the server database alters the table structure, it will not be updated in your section. You need to delete the table and re-insert the same table to get updates.

WEBGL

After trying to get CUDA to work, I come across this technology called WebGL. It is a Web-based Graphics Library that could be controlled by Javascript and worked on HTML5 canvas.

I started on Firefox, it displays a message that "javascript" is needed. I enabled "javascript". It then tells me that "WebGL is enabled" but a setting in the browser stops it from running. However, there is no indication of what was it that stops it from running. I gave up on Firefox.

Next, I turn to Chrome. It runs perfectly without problem. Now this is done on Vista.

Today, I tried on my XP. Chrome refused to run WebGL. Firefox does not run also. On further digging, I managed to get Firefox to run WebGL. The setting is quite simple. Go to about:config and search for webgl. Just change webgl.force-enabled to "true". Also ensure that webgl.disabled is "false".

Turning back to Chrome, I have tried various methods as advised by forums without success. Most suggest having the command line setting like '--ignore-gpu-blacklist'. '--no-sandbox', '--enable-webgl', etc...

There is a suggestion to use about:flags and change "GPU accelerated composition" and "webgl" to on. However, "webgl" setting is not found. Thus, this setting does not apply for Chrome 11.

Further reading shows that Google disabled "webgl" on xp. Finally, I give up just like the "CUDA". It is just a waste of time. Until such utilities are mature, it is pointless to keep digging.

Haredware Acceleration is good if it works generally without technical knowledge. Browsers are not for techies only. It should not get the non-technical users scratching the head as to what went wrong. Chrome has made the situation even worse by blocking it from XP. At least Firefox still enables it to run if we do the correct setting.

I had enough of playing with Graphics. For crying out loud, I don't even have Vista premium. Why am I bothered with graphics on the web? Games? I don't play games that requires GPU or 3D.

CUDA

I have installed a program called FreeMake. It is a program that converts video to various formats. My primary use is to extract audios from VCDs as MP3 format. It worked quite well except that the program is CUDA cabable but it could not detect the GPU in my labtop.

On digging further I discovered that my graphic card is nVidia GeForce 7150M/nForce 630M. Going to the nVidia web site there are downloads available for this model. It also states that it has CUDA capability.

However, on searching its CUDA site, my card is not specified. Confused, I just tried to install the graphic driver. It installed successfully. Then I tried to install various versions of CUDA tool developer driver. None can detect my GPU.

Digging further, it was discovered to my dismay that the laptop graphic driver should come from HP and not directly from nVidia. I tried to install the HP version of the graphic driver. The CUDA tools still fails to detect the GPU.

On further search on the internet, many forums specifically states that the CUDA can only work for 8 series of the nVidia cards. Mine is the 7 series thus it is not supported. The hateful thing is that the nVidia graphic driver's description (for my model) gives me false hope and wasted my whole day trying.

Creating Routes in Goggle Maps

I used to create Maps in Google Maps. Recently it has heen a point of irritation to me. Regardless of whether I create the route by "line" or "along roads". There seems to be some pointer problem. The waypoint may not always appear at the point you clicked.

Creating the route by "along roads" method is worst. The pointer will simply jump about. The trace lines may disappear for no reason. Worst of all, the resultant route may have all the waypoints but the distance indicated is not the full length. The "show the driving direction" feature was also cut short in this way.

I tried to plot the route by line then turn on "along roads". The result is the same. The route is cut short despite showing all the waypoints.

One other thing is that the "line" mode end of line clicking is hell. I don't seem to be able to click the same point to end the edit mode.

No matter what I tried I could not correct the problem. It has been like that for almost a year already and there seems to be no remedy.

IEndomondo has route creation feature also. The editing of the route is a bit crude and I cannot add additional waypoints along the route after it has been created.

Look like I am stuck with Google Map and had to live with the problem or quite creating routes all together.

3G in HTC DESIRE

I installed Quick Settings and it does not seem to be able to enable the 3G whenever I requires it. It is able to disable the 3G. Tried as I might, it just don't work properly.

I discovered that by pressing the on/off switch for 5 seconds, you will get a menu where there is an option to turn on/off 3G. I tried it and it can turn on the 3G while the Quick Settings was killed. If "Quick Settings" is on then it behaves weirdly.

My final decision is to take out "Quick Settings" completely. Now I can just turn on/off 3G easily using the on/off switch.

The only thing I missed is the other switches in "Quick Settings" like WiFi, Bluetooth and GPS. However, there are widgets that could turn on/off the three devices. Just that they take up three spaces in the handphone display panel.

According to what I have read in internet, apps turning on/off 3G are not very successful. I am glad HTC already provided the facility.

SP not found

When you try to setup Replication in SQL Server and you encounter Error 2812 "Stored Procedure" not found. Basically you will be lost for answers.

The actual problem could be in the creating of the distributor. For example, my labtop is a XP when I create a distributor, it promps a "XP agent" error 15281.

The creation process is then just terminated without it being fully completed. It is possible then the necessary SP is not setup properly. I did not actually test the setup of the publisher in such condition. I actually make change to the "SQL Server Surface Area Configuration" on "Features" and checked "enable OPENROWSET..." immediately before reconfiguring the distributor.

The reconfiguration of the distributor is quite straight forward. Right click on the Replication icon and then choose "Distributor properties". Click on "Publisher" and un-check what ever you have set earlier. Add the same publisher to it and you can create local publication now.

Oracle Client

Oracle client is a facility where you can use to query oracle databases. If you are only interested to just install the ODBC part of the client, then you don't need to install the whole package. Just download the basic package and the odbc package from Oracle.

Create a new folder and unzip the two packages into the directory. You will need two extra file from Microsoft to be present in the same directory also. They are mfc71.dll and msvcr71.dll. Somehow the two files are required but never available in the packages.

Run odbc_install in the directory in dos prompt. It is important to run it in dos prompt as you can see the run result of the installation.

Go to system environment and add ORACLE_HOME and TNS_ADMIN variable. The value of the variables are the directory of the installed packages.

Don't forget to create/copy the sqlnet.ora and tnsnames.ora into the same directory also.

Now you can create an ODBC for the database.

I will not go into detail of the ODBC DSN setup as some users like DSNLESS way to query databases.

Make db offline for SQL Server.

Have trouble take some db offline. According to some blogger, they say it is some other sql manager connection to the db. However, I manage to take two of bd off line on the same server. There could be some one else connected to the db but I cannot be sure.

One blogger suggests the following command

ALTER DATABASE db SET OFFLINE WITH ROLLBACK IMMEDIATE 

Another blogger says do this to kick out other users.

ALTER DATBASE db SET SINGLE_USER WITH ROLLBACK IMMEDIATE
Yet to try it but both looks promising. Will update this if successful over the weekend.

Not a trusted SQL Connection

When you set up a sqlserver, there is an option that you must always set before any remote administrator can access to the sqlserver using "Sql Server Management Studio".

At the server where you install the sql server. Start "Sql Server Management Studio". (Assuming you have already setup sql server).

Goto menu bar View>Registered Server

Choose the local instance that you have setup previously and double click on it.

You can skip the above two steps if you can see the local instance in the object explorer already.

Right click on the instance in the object explorer and choose properties.

Select Security.

Under "Server Authentication", check "Sql Server and Windows Authentication mode".

Click "OK"

You should now be able to connect to the sql server from remote sql server management tool.

Sending to a group of external email in Outlook

Normally if you want to send to external email, you would add the email directly to your new mail. However, if you need to send a mass mail of more than a thousand, like send mail to all your clients, it will be a headache.

Fortunately, there is a facility call "distribution list". However, you will still have to add the recipients one by one. Here is a way where you can send to a distribution list without having to add recipients one by one.

1. Start Outlook.

2. File>New>Folder

3. Type the name you want to use

4. Select “contact items”

5. Choose “Contacts” from the list.

6. Click “OK”.

7. From the main Outlook window choose File> Import and Export…

8. Choose “Import from another program or file

9. Click Next

10. Choose “Comma separated Value (windows)”

11. Click Next

12. Use the “Browse” button to select the email list.

13. Click “Do not import duplicate items”.

14. Click Next

15. Select the newly created folder from contacts.

16. Click “ok”

17. Click “Finish” when import completes.

When you want to send the mail, you can just go to contacts and choose the name of the distribution list. Should you need to keep the recipients from seeing other emails in the list then just add the list to BCC. Click the "TO" then select the list name (don't double click) and click "BCC".

Editing the list is easy. On the menu bar click "Go" then choose "Folder list" and you can find the list name in the contacts folder.

Vista taskbar blinking

Recently my taskbar have been blinking on and off and it really annoys me. This happens when I "auto hide" the task bar. If I don't hide then the blinking does not exit but then it took space on my desktop.

From the internet blogs I manage to find the solution. Just simply turn on the network icon. I did not notice when I turned it off. It has been on for a long time before that.

Anyhow, it worked.

MsAccess utility to calculate offset time.

There is a requirement from user to calculate time difference in minutes between two date. The requirement is that you will not count the time from 6pm to the next day 9am.

Since we know that stored procedures and user defined functions cannot work in MsAccess if you want to use the database in web pages, you will have to do it in the views itself.

The formula is as follows.

tat: IIf(DatePart("d",[CREADATETIME]) <> DatePart("d",[ACKDATETIME]),IIf(DatePart("w",[CREADATETIME])=7,DateDiff("n",[creadatetime],[ackdatetime])-2520,IIf(DatePart("h",[creadatetime]) >18,DateDiff("n",Format([ackdatetime],"m/d/yyyy"),[ackdatetime])-540,DateDiff("n",[creadatetime],[ackdatetime])-900)),DateDiff("n",[creadatetime],[ackdatetime]))

It looks awful to concatenate the iifs but it works.

The script has a few conditions.

1. If it is Saturday, it will offset the time from 3pm to the next Monday 9am (2520 min).

2. If it is weekday and greater than 6pm, calculate the time from 9am (540 min) of the second date.

3. If is normal hours and span more than one day then offset by 15 hours (900 min)

4. If both date are on the same day then just calculate the difference between the two date.

MsAccess user function

MsAccess can add user function in modules and used in MsAccess. However, never try it if you want to use it on web sites. It will fail with the message "function xxx not found". Even if you put the function in View, it will still fail.