Excel buffer misbehaving when exporting data from SQL Server...

Ok, I'm bored tonight waiting for a release and thought I would again remind myself what to do when SQL Server 2000 refuses to write data longer than 256 characters in an excel spreadsheet. This seems to be a Jet 3.5 and 4.0 bug and the work around is published here...

http://support.microsoft.com/kb/281517

So, next time get your hands dirty and edit the registry or your data will be cut off at the 255 character when exporting from SQL Server into .xls format or when saving from .xls format into .csv.

Simple recipe for eliminating duplicates

I've used this code over and over, so I better save it for future ocassions.
Problem: A table (MyTable) with a field (name) I need to eliminate records with duplicated name values.

declare @name_unique varchar(50)
declare @tablename table (name_final varchar(50), value text) ---every other field in MyTable

DECLARE cursor_uniquename CURSOR
FOR select distinct name from MyTable

OPEN cursor_uniquename

FETCH NEXT FROM cursor_uniquename
INTO @name_unique

WHILE @@FETCH_STATUS = 0
BEGIN

insert into @tablename select top 1 name,value from MyTable where name=@name_unique

FETCH NEXT FROM cursor_uniquename
INTO @name_unique
END

CLOSE cursor_uniquename
DEALLOCATE cursor_uniquename

truncate MyTable

insert into MyTable select * from @tablename


I wonder if there is a more efficient way, hrmmm...

Oracle Client Tools and the .NET System.Data.OracleClient dll

Remember next time you use OracleClient namespace. This dll depends on the Client Tools from Oracle, so that has to be installed in the server and the folder has to have the proper security settings.
If you get the error:

System.Data.OracleClient requires Oracle client software version 8.1.7 or greater.

and your tnsnames.ora file is fine, your connection strings look good, the client tools you have are for sure of a higher version than 8.1.7 then check the permissions of the folder where the Oracle Client Tools are installed.

Problem
When usign System.Data.OracleClient with Oracle9i client, i got the following message while connecting to the Oracle database from and ASP.NET application.

System.Data.OracleClient requires Oracle client software version 8.1.7 or greater.

Cause
Security permissions were not properly set when the Oracle Client was installed on Windows with NTFS. The result of this is that content of the ORACLE_HOME directory is not visible to Authenticated Users on the machine; this again causes an error while the System.Data.OracleClient is communicating with the Oracle Connectivity software from an ASP.NET using Authenticated User privileges.

Solution
To fix the problem you have to give the Authenticated Users group privilege to the Oracle Home directory.

• Log on to Windwos as a user with Administrator privileges.
• Start Window Explorer and navigate to the ORACLE_HOME folder.
• Choose properties on the ORACLE_HOME folder.
• Click the “Security” tab of the “Properties” window.
• Click on “Authenticated Users” item in the “Name” list.
• Uncheck the “Read and Execute” box in the “Permissions” list under the “Allow” column.
• Re-check the “Read and Execute” box under the “Allow” column
• Click the “Advanced” button and in the “Permission Entries” verify that “Authenticated Users” are listed with permission = “Read & Execute”, and Apply To = “This folder, subfolders and files”. If not, edit that line and make sure that “Apply To” drop-down box is set to “This folder, subfolders and files”. This should already be set properly but it is important that you verify it.
• Click the “Ok” button until you close out all of the security properties windows. The cursor may present the hour glass for a few seconds as it applies the permissions you just changed to all subfolders and files.
• Reboot, to assure that the changes have taken effect.

Try your application again.