Skip to content

MikeBevers.be

Putting it out there…

Archive

Tag: SQL Server

The problem

I was trying to “DropCreate” a database using Entity Framework 4.3. Suddenly I get an exception that EF was unable to drop the database. I fired up my SQL Server Mngt Studio and tried to take the database offline. Again, an error message that it couldn’t get an exclusive lock because of open connections.

The solution

After a bit of searching on the SQL Server processes I ended up with this:

USE master
go

DECLARE @dbname sysname
SET @dbname = 'MyDbName'

DECLARE @spid int
SELECT @spid = min(spid) from master.dbo.sysprocesses where dbid = db_id(@dbname)
WHILE @spid IS NOT NULL
BEGIN
EXECUTE ('KILL ' + @spid)
SELECT @spid = min(spid) from master.dbo.sysprocesses where dbid = db_id(@dbname) AND spid > @spid
END

And instantly, it was my! database yet again.

The MD5 function

When you’re working with hashed passwords, it might be useful to have the MD5 encryption feature in your database.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- ==================================================
-- Author:		Mike Bevers
-- Create date: 07/04/2011
-- Description:	Hashes a value with the MD5 algorithm
-- ==================================================
CREATE FUNCTION MD5
(
	@value varchar(255)
)
RETURNS varchar(32)
AS
BEGIN
	RETURN SUBSTRING(sys.fn_sqlvarbasetostr(HASHBYTES('MD5', @value)),3,32);
END
GO

Usage:

select dbo.MD5('test')
-- Will return '098f6bcd4621d373cade4e832627b4f6'

How to kill open processes in MS SQL Server

You may frequently need in especially development and test environments instead of the production environments to kill all the open connections to a specific database in order to process maintenance task over the SQL Server database.

In such situations when you need to kill or close all the active or open connections to the SQL Server database, you may manage this task by using the Microsoft SQL Server Management Studio or by running T-SQL commands or codes.

continue reading…

More than a few clicks…

It’s common that you create a database on your local development machine. Afterwards, you move the database to a production server.

Well, I thought you could just backup the database with the SQL Management Studio from one machine and restore it to another. The Backup/Restore process wasn’t the problem. When I tried to logon with the associated User/Password account, that I created on both machines and which was owner of the development database, I didn’t have any permissions to my database.

continue reading…