How to generate Excel from SQL data with Unicode characters

private void ExportToExcel(string strFileName, DataGrid dg)
{
Response.ClearContent();
Response.ContentType = "application/excel";
Response.AddHeader("content-disposition", "attachment; filename=" + strFileName);
Response.Charset = "";
Response.ContentEncoding = System.Text.Encoding.Unicode;
Response.BinaryWrite(System.Text.Encoding.Unicode.GetPreamble());
this.EnableViewState = false;
System.IO.StringWriter sw = new System.IO.StringWriter();
HtmlTextWriter htw = new HtmlTextWriter(sw);
dg.RenderControl(htw);
Response.Write(sw.ToString());
Response.End();
}

remove duplicates

1. create another identical table (lazy way: right-click table, script table as, create to, new query editor window)
2. rename constraints
3. create unique index removeduplicates on newtab (uniqfield) with
IGNORE_DUP_KEY
4. insert * from oldtab into newtab
You’ll get a warning “ignoring duplicate field” which is good.

search and replace in all tables

SET NOCOUNT ON
DECLARE @stringToFind NVARCHAR(100)
DECLARE @stringToReplace NVARCHAR(100)
DECLARE @schema sysname
DECLARE @table sysname
DECLARE @count INT
DECLARE @sqlCommand NVARCHAR(max)
DECLARE @where NVARCHAR(max)
DECLARE @columnName sysname
DECLARE @object_id INT
SET @stringToFind = 'Smith'
SET @stringToReplace = 'Jones'
DECLARE TAB_CURSOR CURSOR FOR
SELECT B.NAME AS SCHEMANAME,
A.NAME AS TABLENAME,
A.OBJECT_ID
FROM sys.objects A
INNER JOIN sys.schemas B
ON A.SCHEMA_ID = B.SCHEMA_ID
WHERE TYPE = 'U'
ORDER BY 1
OPEN TAB_CURSOR
FETCH NEXT FROM TAB_CURSOR
INTO @schema,
@table,
@object_id
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE COL_CURSOR CURSOR FOR
SELECT A.NAME
FROM sys.columns A
INNER JOIN sys.types B
ON A.SYSTEM_TYPE_ID = B.SYSTEM_TYPE_ID
WHERE OBJECT_ID = @object_id
AND IS_COMPUTED = 0
AND B.NAME IN ('char','nchar','nvarchar','varchar','text','ntext')
OPEN COL_CURSOR
FETCH NEXT FROM COL_CURSOR
INTO @columnName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sqlCommand = 'UPDATE ' + @schema + '.' + @table + ' SET [' + @columnName + '] = REPLACE(convert(nvarchar(max),[' + @columnName + ']),''' + @stringToFind + ''',''' + @stringToReplace + ''')'
SET @where = ' WHERE [' + @columnName + '] LIKE ''%' + @stringToFind + '%'''
EXEC( @sqlCommand + @where)
SET @count = @@ROWCOUNT
IF @count > 0
BEGIN
PRINT @sqlCommand + @where
PRINT 'Updated: ' + CONVERT(VARCHAR(10),@count)
PRINT '----------------------------------------------------'
END
FETCH NEXT FROM COL_CURSOR
INTO @columnName
END
CLOSE COL_CURSOR
DEALLOCATE COL_CURSOR
FETCH NEXT FROM TAB_CURSOR
INTO @schema,
@table,
@object_id
END
CLOSE TAB_CURSOR
DEALLOCATE TAB_CURSOR

Find / change the datatype for multiple columns/tables

Find all columns of type datetime:
DECLARE @DataType VARCHAR(50)
SELECT @DataType = 'datetime'
SELECT * FROM Information_Schema.Columns WHERE Data_Type = @DataType order by table_name

Find all varchar columns, change to nvarchar:

— SQL 2005
SELECT 'ALTER TABLE ' + quotename(o.name) +
' ALTER COLUMN ' + quotename(c.name) +
' nvarchar(' + ltrim(str(c.max_length)) + ') ' +
CASE c.is_nullable
WHEN 1 THEN ' NULL'
WHEN 0 THEN ' NOT NULL'
END
FROM sys.objects o
JOIN sys.columns c ON o.object_id = c.object_id
JOIN sys.types t ON c.system_type_id = t.system_type_id
WHERE t.name = 'varchar'
ORDER BY o.name, c.name

— SQL 2000
SELECT 'ALTER TABLE ' + quotename(o.name) +
' ALTER COLUMN ' + quotename(c.name) +
' nvarchar(' + ltrim(str(c.length)) + ') ' +
CASE columnproperty(o.id, c.name, 'AllowsNull')
WHEN 1 THEN ' NULL'
WHEN 0 THEN ' NOT NULL'
END
FROM sysobjects o
JOIN syscolumns c ON o.id = c.id
JOIN systypes t ON c.xtype = t.xtype
WHERE t.name = 'varchar'
AND o.xtype = 'U'
ORDER BY o.name, c.name

Running SLES from an USB key

This is a simple attempt to build an easily replaceable and cheap storage server. Cheap means a lot of SATA drives in RAID1 or 5 with both disks and controller expected to fail miserably at any time. Linux makes booting from a RAID array unnecessary difficult (even if it’s in a mirror config, c’mon Windows got that right around NT4) so as a quick solution I used an old and slow ATA drive to boot who held the drivers and configuration for the 1TB array. Obviously, the drive died, and the system image backup was stored on the RAID disks so I:
1. booted a random install disk (knoppix will do)
2. mounted the array (if you don’t dig mdadm, Mandriva’s rescue mode will do the work for you)
3. got a cheap-ass USB key ($20 for 2GB)
3. formatted the key to have one active partition, the filesystem type doesn’t matter, but don’t use it windows-style meaning having a drive like /dev/sdf – you want a partition on it called /dev/sdf1
3. copied everything on /dev/sdf1
4. chroot on the key dir, mind you the next steps are important
5. edited grub.conf and menu.lst to point to this device
5.1 if the USB drive is listed as something like /dev/sdf when you are in the running system, but you do the config without all the SATA disks connected and assume it will be /dev/sda on boot: wrong. SATA disks will be detected first and after all of them are set up using initrd drivers, you’ll see a message “waiting for /dev/sdf to show up” or similar.
5.2 you did include ext, jbd, reiser, etc in initrd, didn’t you?
5.3 grub-install /dev/sdxx
6. fiddled with the bios until it decided to boot from the key – this highly depends on manufacturer and version of motherboard
7. am running SLES just fine in this setup, there might be some wear due to writes in /var/log but for a private storage machine it really doesn’t matter if logs are lost, you can rely on the hardware to spread them, or in Linux to buffer writes or with some time to spare read into unionfs
8. bought two other cheap-ass same manufacturer keys (Sandisk is cool, HP sucks, some other dumbasses changed the sector size from 512 to 2048 to mimic a CD drive and won’t work at all, see this thread for details)
0. remember to use UUID instead of /dev device names for RAID arrays – this will hurt really bad if forgotten.

How to connect to MS SQL from PHP

1. Install FreeTDS. (./configure && make && make install)
2. Install PHP with freetds support (./configure –with-sybase=DIR, where DIR is wherever you placed freetds usually /usr/local)
3. Read a bit of /etc/freetds.conf (not mandatory)
4.
ini_set('mssql.secure_connection','On');
$con = mssql_connect ("1.2.3.4", "user", "pass") or die ("Could not connect to SQL server"); //.mssql_get_last_message()
mssql_select_db ("thedb", $con) or die ("Cannot open database");
$sql= "select top 10 * from test";
$rs= mssql_query ($sql, $con);
while($line = mssql_fetch_row($rs)){
print "$line[0]<br>";
}
mssql_close ($con);
exit(0);

Using the MSSQL Server extension on Win32 requires these steps:
1) Install the SQL Server Client tools on the machine running PHP
(you can get that for free from Microsoft, the one you need is probably sqlncli.msi)
2) Configure a server alias using the client network utility. The alias specifies name, host name, protocol and port numbers used for the db communication
3) Connect to the database using the alias as db name as first parameter in mssql_connect
4) If the SQL Server is configured for trusted connections only, enable this in PHP.INI by setting mssql.secure_connection = On (no need for userid and password then)

Ubuntu on an Acer Aspire 5315

Apparently the wireless card on this model is very stubborn, quick HOWTO:
#-1 madwifi doesn’t work, be it stable version or svn commit up to 3745
#0 upgrade to the latest packages (kernel 2.6.24-19, ndiswrapper 1.50)
#1 rmmod ath_pci, ath_rate_sample, ath_hal, wlan, everything until you’re sure there there are no wireless modules loaded
#2 mcedit /etc/default/linux-restricted-modules-common
DISABLED_MODULES=”ath_hal”
#3 apt-get install ndiswrapper-common
#4 unpack this driver somewhere
#5 ndiswrapper -i net5211.inf
#6 modprobe ndiswrapper
#7 iwconfig (you should have a wlan0 interface now, either that or your system has crashed completely and you’re not reading the rest)
#8 iwconfig wlan0 mode managed essid blah key blah ; dhclient wlan0
#9 profit

How to root your Mac in 5 seconds

Maybe 10 if you’re a slow typist. In short:
osascript -e 'tell app "ARDAgent" to do shell script "/Applications/iTerm.app/Contents/MacOS/iTerm"'

You might get a few screenfulls of errors

but the command WILL execute:

This is by far the dumbest bug I’ve encountered in the past decade, and one that makes me appreciate for the first time the separation between desktop applications and services enforced in Windows NT. A system daemon, running with root privileges, should never ever accept arbitrary input from arbitrary processes, running under arbitrary accounts, and, at the very least, it should try to NOT EXECUTE THE FUCKING INPUT.
Several mentions on the web claim that this requires local access or that the calling user is also logged on to the graphical interface, this is incorrect, AppleScript can be invoked over a ssh session by a different user (hint: Remote Apple Events)

Bad Behavior has blocked 85 access attempts in the last 7 days.