Wednesday, April 21, 2010

Generating Random numbers in a SELECT statement using MS SQL Server

Generating random numbers in SQL statement is not as trivial as it may look. Simply invoking the rand() method in a query will not work. For example, if you have a table with 1000 rows, doing:

select rand() from my_table

Will not work.

The result of this query will be 1000 rows of the same random number. MS SQL optimizer evaluates the rand() function only once.

In order to override this problem, we can do the following steps:

1) Create a view named: random_view that selects rand() function:

create VIEW [dbo].[random_view]
AS
SELECT rand() 'rnd'   


2) Create a function that makes a select on the random_view:

CREATE FUNCTION [dbo].[random]()
RETURNS float
AS
BEGIN
return (select rnd from random_view)
END


Now we can use our new generated function in a any query:

select dbo.random() from my_table

Assuming my_table has has 1000 rows, we will get 1000 different random numbers.

Tuesday, April 20, 2010

Unable to delete files in Windows Vista/Windows 7

If you have reinstalled windows machine, without entirely erasing your disk you have probably encountered a problem in which you are unable to delete files or directories. These files belonged to the older windows machine (the previous user(s) of that machine), and therefore, your new operating system will allow you to access these files, but not delete them.

In order to delete these files, we first have to take control over them. This can be done by using 2 command line utilities. First open a command line as Administrator. This can be pressing the “Start” button and writing “cmd” on the search edit box. When the “cmd.exe” file is shown, right click on it and choose: “Run as administrator”. Another way of doing it, is writing “cmd” on the search box, and then pressing “Ctrl + Shift + Enter” instead of Enter.

After the the command line is open as administrator, we first run this command on the file we would like to delete:
takeown /f "file_name" /d y

For example:
takeown /f "c:\some dir\some undeletable file" /d y

If we would like to delete a directory instead of a file, we can use this command, which runs recursively on all the sub directories:
takeown /f "directory_name" /r /d y

For example:
takeown /f "c:\some undeletable directory" /r /d y

After running this command we should run this command on a single file:
icacls "file_name" /grant administrators:F

For example:
icacls "c:\some dir\some undeletable file" /grant administrators:F

And for a directory and it’s sub directories:
icacls "c:\some undeletable directory" /grant administrators:F /t

After running these 2 commands, simply delete the file or directory using the Windows explorer.

Sunday, April 11, 2010

Access Linux file system from Windows

Few months ago I had a server running Linux. It was running Apache Server connected with 2 Tomcat servers and mySQL database server. Each Tomcat was running several web applications. Everything was running smoothly, until one day I made some mistake and damaged the operating system to a point it was no longer running.


All my efforts to repair the problem were failed. Since my knowledge in Linux is not so good as my knowledge in Windows, I made a decision to move to Windows 2008 Server. But, unfortunately I needed lots of the data that was on my Linux machine. Since I was unable to repair the Linux machine, I decided to look for a way of a accessing directly to my Linux file system from Windows.


Removing the disk from the Linux machine and putting it on my Windows machine was an easy task. Finding a tool that allows access to my Linux file system was a real challenge. I downloaded several tools from the web and had no luck accessing my Linux file system with them. The tools simply didn’t work.


Finally after doing quite a search, I came across this project: http://www.ext2fsd.com/ which offers a software named: Ext2Fsd. This software is not so user friendly, and it took me quite a while understanding how to define a drive that will point to my Linux file system. But after connecting my Linux file system with a drive, I was able to see my entire Linux file system through Windows. I simply made a copy & paste to all the files I needed, and after few minutes I was able to save all my lost data.