24. February 2011 21:05
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
- Provide list of index's with fragmentation percentage
- Take recommended action on fragmented index's
- Rebuild selected index's
- Rebuild all index's in a database
You can read more about it here
23. February 2011 18:22
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...
22. February 2011 18:47
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)
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
FETCH NEXT FROM cur
WHILE @@FETCH_STATUS = 0
PRINT CONVERT(varchar, @spid)
SET @sql = 'KILL ' + RTRIM(@spid)
FETCH NEXT FROM cur
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.
21. February 2011 17:39
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...
20. February 2011 20:23
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 (
CONSTRAINT [PK_ID] PRIMARY KEY CLUSTERED
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.