close all connections to current database

Useful if the database refuses to go offline for backup/restore

DECLARE @DatabaseName nvarchar(50)
DECLARE @SPId int
DECLARE @CMD varchar(100)
SET @DatabaseName = DB_NAME()
DECLARE my_cursor CURSOR FAST_FORWARD FOR
SELECT SPId FROM MASTER..SysProcesses
WHERE DBId = DB_ID(@DatabaseName) AND SPId <> @@SPId
OPEN my_cursor
FETCH NEXT FROM my_cursor INTO @SPId
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @CMD = 'KILL ' + CAST(@SPId AS varchar(10))
--SELECT @CMD
EXEC (@CMD)
FETCH NEXT FROM my_cursor INTO @SPId
END
CLOSE my_cursor
DEALLOCATE my_cursor

Uninstall a .pkg

The OS X installer doesn’t offer a default tracking method for the files created by Installer packages (.pkg). While the list of files copied is present within the package, and is saved in /Library/Receipts/packagename, scripts run during install can create/modify others, and I think they can only be detected by running the install chrooted. Anyway, a quick solution to free disk space:
cd /Library/Receipts/packagename/Contents
lsbom -pf Archive.bom | cut -c 2- | sort -r | xargs -I# -p -t rm -d "#" ;

Warnings:
1. this has to be run from a root shell (not sudo)
2. mistake a single letter, and you might wipe the whole system
3. do not try to be clever and add a -fr to the rm above

How to delete an user from SQL db

Specifically, a MS SQL 200/2005 db. Enterprise Manager and the equivalent Management Studio fail with no helpful details if said user owns any object in the database. To find out first if there are any schemas owned by the user:

use [thedatabase];
select s.* from sys.schemas as s
inner join sys.database_principals as dp on dp.principal_id =
s.principal_id
where dp.[name] = 'domain\user or sqluser';

Those schemas may or may not hold anything useful, check with
select s.[name] as schemaname, o.[name] as objectname, o.type_desc
from sys.objects as o
inner join sys.schemas as s on s.schema_id = o.schema_id
inner join sys.database_principals as dp on dp.principal_id =
s.principal_id
where dp.[name] like 'schema_name from above'
order by s.[name], o.type_desc, o.[name];

If there’s no output, you can safely
drop schema schema_name;
otherwise, remove the user’s ownership
alter authorization on schema::schema_name to dbo;
then
drop user [username];

How to restore Grub from a live CD

mkdir /zzz
mount /dev/sda3 /zzz
mount -t proc none /zzz/proc
mount -o bind /dev /zzz/dev
chroot /zzz /bin/bash
grub
find /boot/grub/stage1
root (hd0,2)
setup (hd0)

For /boot/grub/menu.lst, a minimal config would be

default Linux
timeout 2
title Linux
root (hd0,2)
kernel=/boot/vmlinuz root=/dev/sda3 ro

also check /zzz/etc/fstab.

Ou sont les neiges d’antan

Quote from some unknown person that struck a nerve at the time read

When did the userbase of linux go so wrong? When did it start? Those of us that have been around for quite a while have watched the rockstar rise and subsequent plummet in the communities surrounding it. 10+ years ago you could jump into IRC type /join #[your distro] and be surrounded by people that truly loved their systems and would help you without being condescending, resorting to ad hominem attacks, or calling other *nix variants crap. A lot of those guys, myself included, were using Solaris, HP-UX, Tru64, AIX, etc. during the day and spending some of our evenings just helping out others that were interested in or ‘testing the water’ with linux. The slackware community was great. Even a little more recently when Gentoo first really got rolling between 1.0 and 1.2 the community on EFNET in #gentoo was a shining example of what a userbase should be. I spent many an hour in there helping people figure out their CFLAGS, configuring their XF86Config, and the like.

With the influx of more and more folks that seem to be vastly more focused on hating Windows, Mac OSX, and even other UNIX variants, the face of linux has changed. People that used to use *NIX as a personal choice did so because they truly loved computers. Now it seems to be the equivalent of a battle standard. Your Operating System is your country, your flag, and your religion. Thousands of angry people focused on their hatred of anything that is unlike themselves. It’s GNU/Xenophobia.

The programming ramifications of this have become pervasive throughout many of the more popular distributions. In fact, the fundamental idea of the ideological Bazaar has been replaced by the Cathedral of intolerance. Instead of a focus on excellence and listening to the end-users, more and more developers are dismissive and prone to flame. More time is spent developing completely worthless and unrelated ‘features’ than in solidifying and optimizing the current code. Instead of, say fixing GNOME’s inability to remember where I want my launch icons on the panel, we get wobbly windows that add absolutely nothing to the value of the desktop. Instead of writing just one really, really good IDE for C development, we get oodles of feature-incomplete environments that can’t even compete with older Visual Studios or XCode; and this is supposed to be forte of *NIX. As children we are reinforced to eat our meat and vegetables before we get dessert. Yet more and more developers focus on the candy and leave the meat (optimizing) and vegetables (squashing bugs) virtually untouched. It’s not as exciting of course, but it is necessary. In *buntu I struggle with yet another audio layer to cover the other layers to figure out why my sound card is doing a darn good impression of a french mime when I try to play some music. Meanwhile, a thousand fanboys upload yet another Youtube video of a spinning desktop cube with a Moby soundtrack.

Perhaps it is the fate of those of us from the previous generation to make way for the new one, but as we do so there should be some guidance, some hope, and some direction given.

How to connect to a MS SQL database using ASP.Net 3.5?

In web.config:
<configuration>
...
<connectionStrings>
<add name="defaultconn" connectionString="Data Source=server.domain.com; Initial Catalog=thedatabase; Persist Security Info=True; User ID=theuser; Password=thepass" providerName=".NET Framework Data Provider for SQL Server"/>
</connectionStrings>
...
</configuration>

In App_Code:
using System.Data.SqlClient;
namespace whatever
{
public static DataTable GetTable(string connstr, string commstr, params object[] parameters)
{
if (commstr == null) return null;
if (connstr == null) connstr = ConfigurationManager.ConnectionStrings["defaultconn"].ConnectionString;
SqlConnection scon = new SqlConnection(connstr);
SqlCommand scom = new SqlCommand(commstr, scon);
for (int j = 0; j < parameters.Length; j += 2)
scom.Parameters.AddWithValue((String)parameters[j], (parameters[j + 1] == null ? DBNull.Value : parameters[j + 1]));
SqlDataAdapter sda = new SqlDataAdapter(scom);
DataSet ds = new DataSet();
sda.Fill(ds); scon.Close();
if (ds.Tables[0].Rows.Count == 0)
return null;
return ds.Tables[0];
}
public static DataRow GetRow(string connstr, string commstr, params object[] parameters)
{
if (commstr == null) return null;
if (connstr == null) connstr = ConfigurationManager.ConnectionStrings["defaultconn"].ConnectionString;
SqlConnection scon = new SqlConnection(connstr);
SqlCommand scom = new SqlCommand(commstr, scon);
for (int j = 0; j < parameters.Length; j += 2)
scom.Parameters.AddWithValue((String)parameters[j], (parameters[j + 1] == null ? DBNull.Value : parameters[j + 1]));
SqlDataAdapter sda = new SqlDataAdapter(scom);
DataSet ds = new DataSet();
sda.Fill(ds); scon.Close();
if (ds.Tables[0].Rows.Count == 0)
return null;
return ds.Tables[0].Rows[0];
}
public static int Execute(string connstr, string commstr, params object[] parameters)
{
if (commstr == null) return -1;
if (connstr == null) connstr = ConfigurationManager.ConnectionStrings["defaultconn"].ConnectionString;
SqlConnection scon = new SqlConnection(connstr); scon.Open();
SqlCommand scom = new SqlCommand(commstr, scon);
for (int j = 0; j < parameters.Length; j += 2)
scom.Parameters.AddWithValue((String)parameters[j], (parameters[j + 1] == null ? DBNull.Value : parameters[j + 1]));
return scom.ExecuteNonQuery();
}

Use as
DataTable dt = whatever.SQL.GetTable(null, "select * from Users");
DataRow dr = whatever.SQL.GetRow(null, "select * from Users where uname=@u and pass=@p", "@u", uname.Text, "@p", pass.Text);

etc

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.

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