Day: September 28, 2006

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.

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…