So many people have written ms sql index rebuild script that there are already so many to find on the internet. However not many people have written a small simple application that simply solves the problem for most people. Allowing them to connect, view the status of the index's and rebuild them if required.

Main Features so far

Here is a quick guide to get the new value of NEWSEQUENTIALID() when inserting a row into a table in ms sql. If you have attempted to use NEWSEQUENTIALID() before you will know that it is only possible to use as a generated value in the table when the row is being inserted. This is a guide to get this value from c# when inserting the row. More...

This can be really useful if you need to kill all the connections by a user. An example might be a out of control web application which is running under a certain user. Or because you just want to abort a single users quries because they have left an open transaction and a pile of tables locked for example.

CREATE PROCEDURE [dbo].[KillConnectionsUser] @username varchar(MAX)
AS
	DECLARE @spid int
	DECLARE @sql varchar(MAX)

	DECLARE cur CURSOR FOR
		SELECT spid FROM sys.sysprocesses P
			JOIN sys.sysdatabases D ON (D.dbid = P.dbid)
			JOIN sys.sysusers U ON (P.uid = U.uid)
			WHERE loginame = @username
			AND P.spid != @@SPID

	OPEN cur

	FETCH NEXT FROM cur
		INTO @spid
		
	WHILE @@FETCH_STATUS = 0
	BEGIN
		PRINT CONVERT(varchar, @spid)

		SET @sql = 'KILL ' + RTRIM(@spid)
		PRINT @sql
		EXEC(@sql)

		FETCH NEXT FROM cur
			INTO @spid
	END

	CLOSE cur
	DEALLOCATE cur
GO

It is really simply to use.

Just call the stored procedure with in the following way where 'Test' happens to be the name of the user you want to kill off.

Here is a q quick exmaple.

EXEC KillConnectionsUser @username = 'Test'

Note: If you use this on your own username it will not attempt to kill its self in the process.

Most of us have problem heard of confused.com. I used them a while ago only to be receiving marketing email since. So I followed the correct guidlines inside and email a while ago and attempt to unsubscribe. Only today I recived another email from them with more marketing junk that i don't want. So i went back to the unsubscribe system and found the following settings from the previous time I attempted to unsubscribe. More...

Normally temp table's are only visible to the current connection that they are originally created on. However this is not the case. This can lead to a race situation occuring between mutliple connections using temp tables. Or worse between applications that are using the same constraint name in two seperate temp tables.

If you run the following code on a connection and then on a 2nd connection (in any other database) it will fail!

IF (OBJECT_ID('tempdb..#Import') IS NOT NULL)
      DROP TABLE #Import

CREATE TABLE #Import (
      ID int
      CONSTRAINT [PK_ID] PRIMARY KEY CLUSTERED 
      (
            [ID] ASC
      )
)

The code above will fail with the following error message.

There is already an object named 'PK_ID' in the database.
Could not create constraint. See previous errors.

So there we have it. In sql server 2005/2008 normal temp table's are not just visible to the current connection. The constraint name is global in the temp table. Of course the solution to this problem is easy. That would be to use a guid for the constraint name.