While browsing the internet, I came up with an interesting quote by Mark Twain which apparently was highlighted under an individuals signature. Check this out:

A banker is a fellow who lends you his umbrella when the sun is shining, but wants it back the minute it begins to rain.
Mark Twain (1835 - 1910)

This error was a real nightmare for me where I'm being in middle of a migration process of our legacy system. Though there was real hits when you google this problem, but nothing was helpfull for me.

The context of the problem occurs when I did run it on a WinXP pro sp3+ workstation which had installed the latest ODP.NET. However its quite extraordinary to see it was not reproduced in a machine installed Oracle Express Edition 11g (without oracle client).

Later, I understood that this problems occurs if you have a VarChar2 parameter(s) when executing a Oracle Stored Procedure. So people advised to have the size and Parameter direction in the parameter. I incresed the size everytime of the parameter and had the parameter direction as "input", yet this error kept bugging me. (My stored procedures did not have any return/output parameters, so I'm not in a possition to comment its behavior).

Finally after googling for days and finding one post in the Oracle forum from one guy who said to use the direction as "InputOutput" did the work for me. Furthermore to the parameter direction, I also gave all of the VarChar2 parameter size as 2000.

Produced Environment: .NET framework 2.0, Win XP Pro sp3 and the latest ODP.NET

This is the way I got a Percentage Column in my Query. For this I used Microsoft Access's Northwind Database which is available along with Office Package.

Lets Assume that We need to show all Customer's Total Order Value from Northwind.mdb. For that I used the following Query:

SELECT
Customers.CompanyName,
Sum([UnitPrice]*[Quantity]) AS CustTotal,
FROM
(Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID) INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID
GROUP BY Customers.CompanyName, Orders.CustomerID
ORDER BY Sum([UnitPrice]*[Quantity]) DESC;

I figured that to get the percentage i need to use the following simple formula:

Percentage = [Client Total]/[Overall Total] * 100;


So to get the percentage I added the following SQL which is a translation of the above formula:

(
((Sum([UnitPrice]*[Quantity])/(SELECT SUM([UnitPrice]*[Quantity]) FROM [Order Details]))*100)
) AS Percentage

So the Overall SQL looks something as after add:

SELECT
Customers.CompanyName,
Sum([UnitPrice]*[Quantity]) AS CustTotal,
(
((Sum([UnitPrice]*[Quantity])/(SELECT SUM( [UnitPrice]*[Quantity]) FROM [Order Details]))*100)
) AS Percentage
FROM
(Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID) INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID
GROUP BY Customers.CompanyName, Orders.CustomerID
ORDER BY Sum([UnitPrice]*[Quantity]) DESC;

PLEASE! if you found this interesting, do drop me a comment!

Me and My friends at JB

My Boarland Delphi 5 Enterprise Edition While loading

Installing MSDE 2000 is bit complicated thing, unlike in MS SQL Server 2000. There is no user interface and no installation wizard, but you have to use TSQL to all of tasks in the Command Prompt. (Its like going back to the DOS era)

You get MSDE in your Office CD free. I had Microsoft Office XP with me and this is the way I installed it.

Insert your Office CD in, and type the following in your Command Prompt:

C:\>D:\MSDE2000\Setup.Exe SAPWD="" SECURITYMODE=SQL DISABLENETWORKPROTOCOL S=0/L*v C:\MSDELOG.TXT

And to Attach a Database to MSDE, I used the following way:

Again nothing will help you instead of Command Prompt.

Here, first you have to login to MSDE, and this is the way its done (provide your SA password):

C:\>osql/U sa/P

Once logged in, type the following in the command prompt:

EXEC sp_attach_db @dbname='pubs' @filename1='C:\Program Files\Microsoft Sql Server\MSSQL\Data\pubs.mdf' @filename2=''C:\Program Files\Microsoft Sql Server\MSSQL\Data\pubs_log.ldf' GO

Its arent easy with out a proper GUI, yet I was able to surf the internet and get help.