DYM - Did You Mean?

I coded a web part to present the user with "Did you mean ...?" suggestions in Microsoft Search Server Express. For more information see the complete web part information at my home page. http://www.albert.nu/programs/dym

WSS safe upload hack

Find the upload.aspx file in 12\TEMPLATE\LAYOUTS and change the "OverwriteSingle" checkbox to this:

...asp:CheckBox id="OverwriteSingle" Visible="false" Checked="false"...

The checkbox will not be visible and if a duplicate document is selected the user will get an error instead of overwriting the existing file.

NOTE: This is hack of the standard out of the box solution.

Stored procedure result into a table...

Needed to kill a large range of spid's today... but how can that be done in a simple and flexible way?

Well, put the result of sp_who2 into a table, do a select from that table to generate one column with the text 'kill '. And then copy paste the result and run it.

declare @sp_who table
(
spid smallint,
status nchar(30),
loginame nchar(128),
hostname nchar(128),
blkby char(5),
dbname nchar(128),
cmd nchar(16),
cputime int,
diskio int,
lastbatch varchar(20),
program nchar(128),
spid2 smallint,
requestid int
)

insert into @sp_who execute sp_who2

select * from @sp_who

Modify the final select to suit your needs.

WSS Create Discussion Board item

Creating and manipulating ordinary lists in WSS through the object model is easy, and finding examples on the web is also easy. But what about discussion boards?

Turns out that they are not as easily manipulated, and examples are very hard to find. After much browsing I found this information though:
http://blogs.msdn.com/sfellman/archive/2007/12/17/issue-programmatically-posting-wss-2007-discussion-list-items-shows-sender-as-system-account-in-outlook-2007.aspx

Use this code:
using Microsoft.Sharepoint.Utilities;

SPListItem item = SPUtility.CreateNewDiscussion(.Items, "");
item["Body"] = "";
item.Update();

There is a corresponding method to add discussion replies:
SPUtility.CreateNewDiscussionReply(item);

BizTalk installation/configuration

During the installation of BAM Tools you have an option to "Enable Analysis Services for BAM aggregations". When I tried to configure BizTalk the other day this part simply wouldn't work.

The error displayed looked like this:
Either the '' user does not have permission to create a new object in '', or the object does not exist.

This seemed odd to me because the previous parts of the configuration had successfully created several databases with the same user account.

So what's the problem then? Almost embarrasing... analysis services uses its own port, not the standard 1433 sql server port. So after add the required port to the firewall rules everything worked as expected.

Use SQL Server trace to find problems on other servers!

The trace tool in SQL Server is mostly used to find performance problems related to SQL Server. But it is also a great tool to find performance problems in application servers and web servers.

The general way of thinking is this: Even if your SQL Server is doing fine what impact does the information flow have on other parts of the system? It is not unusual that the SQL Server is the most powerful machine in the system, allowing it to cope with huge loads of requests.

If you see either large amounts of requests to SQL Server or fewer requests but with large amounts of data try to figure out how the application manages this information. The life of an application server is not that simple, by knowing the SQL Server requests you can figure out what it is doing and maybe make it easier on both the application server and SQL Server.

Filtered indexes in SQL Server 2008

Ok, a great feature as it is... a great performance booster when applied correctly. I'm not gonna repeat what others have written, google it!

But what I really like about it is that is solves the age old problem of applying a uniqueness requirement to all but (and usually that is all but null values or something similar).

The classic solution to this problem is of course to use a trigger to verify uniqueness, to make that trigger run fast it requires an index. Instead of the mess of a complete index (larger than we need) and extra code in the form of a trigger that adds complexity and steals more performance we simply apply a filtered index.

Example, we want to make sure that socialid is unique if it is not null:
create table Person (id int identity(1,1) primary key, name nvarchar(100), socialid nvarchar(20))

insert into Person (name, socialid) values ('Albert', '1234'), ('Bengt', null), ('Christian', '2341'), ('Dan', null)

create unique index idx_socialid on Person (socialid) where socialid is not null

select * from Person

insert into Person (name, socialid) values ('Eric', '1234')
Inserting the person named Eric will fail since the socialid is not unique.