ReadyBoost USB Drive Not recognized by Windows Vista

I had this problem couple of days before. I had a Toshiba 4GB USB Flash drive, which I was using as a ReadyBoost Drive for my Windows Vista laptop. It was working fine, until I found that it was infected with a Trojan and decided to Format it completely.

Once the format operation is complete, I tried using it as a Readyboost drive and it did not work! I tried rebooting Windows Vista over and again and pressed "test" repeatedly , all in vain. I simply got a message saying that " the drive does not pass the speed test".

A more detailed examination of the logs revealed the following information (under event viewer)

Event Viewer/Applications and Services Logs / Microsoft / Windows / ReadyBoost

The device (TOSHIBA TransMemory) will not be used for a ReadyBoost cache because it has insufficient write performance: 1441 KB/sec.Repeated testing of the device resulted in similar logs, the speed along was different everytime. The windows Event IT is 1004.

I decided to fix this problem and followed this approach, it might be useful for you too

1) Plug in the thumb drive into the USB slot.

2) When prompted for Auto Run, I clicked on Cancel.

3) I navigated to the following path:

Start -> Control Panel -> Administrative Tools -> Computer Management -> Device Manager

4) Under "Disk Drives" selected and right clicked against by USB device, and clicked on Properties.

5) Now, navigate to the "Policies" tab.

6) Under "Write Caching and Safe Removal" select "Optimize for performance" and click on ok. [make sure that "Optimize for Quick Removal" is not selected]



7) Now, click on "My Computer". Right click on the thumb drive, and select FORMAT.

8) Before you start the formatting operation, make sure the following options are selected

a) File System - NTFS
b) Allocation Unit Size - 4096

9) Once the formatting is done, remove the USB and plug it in again.

10) It should now be ready to boost your VISTA.

Any questions, post it..

Analytic Functions - Ranking in Desired Order

A new problem for which I've not found the solution till now..The objective would be achieve the result in a single SQL statement for the problem that I'm listing below:

The table that I have has the following DDL

CREATE TABLE TEMP_REC
(
PARENTID  VARCHAR2(60 BYTE)                   NOT NULL,
CHILDID   VARCHAR2(60 BYTE)                   NOT NULL,
VAL       VARCHAR2(762 BYTE)                  NOT NULL
)

Some preparatory data for this table in the form of insert statements:

Insert into TEMP_REC
  (PARENTID, CHILDID, VAL)
Values
  ('1', '78056359', 'Z1');
Insert into TEMP_REC
  (PARENTID, CHILDID, VAL)
Values
  ('1', '64850981', 'F8');
Insert into TEMP_REC
  (PARENTID, CHILDID, VAL)
Values
  ('1', '62103647', 'F8');
Insert into TEMP_REC
  (PARENTID, CHILDID, VAL)
Values
  ('1', '62102722', 'E3');
Insert into TEMP_REC
  (PARENTID, CHILDID, VAL)
Values
  ('1', '62102567', 'F8');
Insert into TEMP_REC
  (PARENTID, CHILDID, VAL)
Values
  ('1', '62102433', 'E3');
Insert into TEMP_REC
  (PARENTID, CHILDID, VAL)
Values
  ('1', '62102275', 'Z1');
Insert into TEMP_REC
  (PARENTID, CHILDID, VAL)
Values
  ('1', '62102115', 'E3');

Now, my objective is to write a single SQL to get the output in the following format

PARENTID CHILDID VAL  RANK

1 78056359 Z1    1
1 64850981 F8    2
1 62103647 F8    2
1 62102722 E3    3
1 62102567 F8    4
1 62102433 E3    5
1 62102275 Z1    6
1 62102115 E3    7

The catch here, is that the rank should be same as long "VAL" values get repeated. So, the second row and third row with VAL of "F8" gets a rank of 2. This is fine. But the fifth row containing a value of "F8" should get a rank of 4. When I try the following SQL

select parentid,childid,val,dense_rank()over (partition by val order by childid desc)
from temp_rec
order by childid desc

I get the following output,

1 78056359 Z1 1
1 64850981 F8 1
1 62103647 F8 2
1 62102722 E3 1
1 62102567 F8 3
1 62102433 E3 2
1 62102275 Z1 2
1 62102115 E3 3

But, this is not what I'm looking for. I'm not sure if RANK and DENSE RANK analytic functions would help my cause here. I'm not interested in PL SQL based approach to solve this..if somebody can throw some ideas, they are most welcome..

Oracle - Dynamically Drop constraints from a table

I was faced with a task of dropping a constraint from a column in a table dynamically. i.e. the name of the constraint would vary between different environments and the challenge would be to know the name of the constraint by querying the dictionary tables in Oracle and drop them on the fly. As an example, consider the following create table statement;

CREATE TABLE TEST1(
COL1 VARCHAR2(20) NOT NULL,
COL2 NUMBER
)

Here, we are enforcing a NOT NULL constraint on the column COL1 of the table TEST1. If the requirement is to find this constraint dynamically and drop it, then the SQL to find the constraint is straightforward.

SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME='TEST1' AND CONSTRAINT_NAME IN (
SELECT CONSTRAINT_NAME FROM USER_CONS_COLUMNS WHERE TABLE_NAME='TEST1' AND COLUMN_NAME='COL1')

This gives me an output as SYS_C009648. From here on, it is as simple as

Drop constraint SYS_C009648;

Let us now complicate the problem a little. Say, I have two constraints on a table. As an example, consider the same create table statement, but slightly modified as

CREATE TABLE TEST1(
COL1 VARCHAR2(20) NOT NULL CHECK (LENGTH(COL1)<=5),
COL2 NUMBER
)

Now, I have two constraints on the table TEST1. A NOT NULL constraint and a CHECK constraint. The SQL

SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME='TEST1' AND CONSTRAINT_NAME IN (
SELECT CONSTRAINT_NAME FROM USER_CONS_COLUMNS WHERE TABLE_NAME='TEST1' AND COLUMN_NAME='COL1')

would now return two rows for me. One row for the NOT NULL constraint and another row for the check constraint.

Unfortunately, the column CONSTRAINT_TYPE in USER_CONSTRAINTS has a value of 'C' for both the rows and there is no straightforward way to find the NOT NULL constraint.There is only one field in the view USER_CONSTRAINTS that can help us in this case. It is SEARCH_CONDITION. This column has values as given below

NOT NULL -> "COL1" IS NOT NULL
Check Constraint -> LENGTH(COL1)<=5

As this column is of LONG datatype, we will have to go for a "round about" way to find the exact constraint that we are looking for. The solution provided by Tom Kyte for this is to create a function and then use it for our requirement; The function is provided below

CREATE OR REPLACE FUNCTION GET_SEARCH_CONDITION(
P_CONS_NAME IN VARCHAR2 ) RETURN VARCHAR2
AUTHID CURRENT_USER
IS
L_SEARCH_CONDITION USER_CONSTRAINTS.SEARCH_CONDITION%TYPE;
BEGIN
SELECT SEARCH_CONDITION INTO L_SEARCH_CONDITION
FROM USER_CONSTRAINTS
WHERE CONSTRAINT_NAME = P_CONS_NAME;

RETURN L_SEARCH_CONDITION;
END

The SQL needs to be modified as;

SELECT * FROM(
SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME='TEST1' AND CONSTRAINT_NAME IN (
SELECT CONSTRAINT_NAME FROM USER_CONS_COLUMNS WHERE TABLE_NAME='TEST1' AND COLUMN_NAME='COL1')
) WHERE
GET_SEARCH_CONDITION(CONSTRAINT_NAME) LIKE '%NOT NULL%'

and this would do the trick for us;) We can now find the constraint with the help of the SQL above and drop it.

Excel - Startup Problem

Here is the problem summary. I'm trying to start Microsoft Excel from my computer and every time I start it, I get the following error;

Window Title: Microsoft Forms
Error Message: Could not load an object because it is not available on this machine.

A screen dump of the error is provided below

This problem comes, every time I start excel. Be it, trying to open an embedded excel object or excel directly. I've been living with this problem, trying to get over every time by clicking 'OK' until I felt it is too annoying to do and decided to get rid of the problem.

To start with, I tried the error message in Google, but cannot find a convincing solution to get rid of the issue.

I decided to get rid of this on my own and this is what I did.

1) Start Excel -> Navigate to Tools - Add ins

I unchecked on each and every available Add in and restarted Excel to see if that solved my problem. No luck :(. I got the same nice message after that as well.

2) Direction two: I navigated to the following path

c:\Documents and Settings\\Application Data\Microsoft\Excel\XLSTART

Here I found couple of .XLA files which are not listed in my "Add in" list.

3) I tried removing one at a time and restarted Excel every time I did this.

4) When I removed one of the add in, the problem disappeared. In my case, the add in was NitroPDF.xla.

5) When I removed this add in, and restarted Excel, it was working without any problems.

No errors this time :)