Skip to content

MikeBevers.be

Putting it out there…

Archive

Category: Databases & Query Languages

The Problem

At a customer, I had to gather data from two databases. So I started writing a SQL query that combined data from two databases and wanted to join them on an ID field. Then this popped up: “Cannot resolve the collation conflict between “SQL_Latin1_General_CP1_CI_AS” and “Latin1_General_BIN” in the equal to operation“. Apparently along the way they decided, or by accident, switched collations.

The Solution

It’s actually very simple, you have to explicitly specify the collation in your query. I posted the code because it’s too lengthy and error-prone to write it manually several times a day…

SELECT p.KeyField
FROM DB1.PrimaryTable p
INNER JOIN DB2.SecondaryTable s ON LTRIM(RTRIM(p.KeyField))COLLATE SQL_Latin1_General_CP1_CI_AS = s.KeyField

That should do the trick!

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'

Context

Working with DateTime fields in MSSQL is not the same as you do in .NET. I found myself googling the same things over and over. That’s why I decided to group the things I need most in one blogpost. This way I don’t have to keep googling, I just need to browse my blog and voilà.

I will update this blogpost when I’m in need of different functionalities.

Comparing the date part of DateTime fields

You shouldn’t cast or convert your DateTime fields to text and then do a text compare like:

SELECT CASE WHEN CONVERT(varchar(10), getdate(), 101) = CONVERT(varchar(10), getdate(), 101) THEN 'Equal' ELSE 'Different' END AS 'Compare result'

You should use the DateTime methods provided by Microsoft:

SELECT CASE WHEN DATEDIFF(d, getdate(), getdate()) = 0 THEN 'Equal' ELSE 'Different' END AS 'Compare result'

The DATEDIFF method accepts a datepart argument. The method will base its result on the supplied datepart argument. More info: DATEDIFF on MSDN

Displaying DateTime fields

I found this example on the internet:

SELECT DATEADD(dd, -DATEDIFF(dd, getdate(), 1), 1) -- 2010-08-02 00:00:00.000

I don’t find it very useful to put this in a SELECT statement, maybe when you’re using subqueries to be sure it will be parsed as a DateTime. Again, use the provided methods. In this case it would be the Convert. It’s always nice to see an example result, so here it goes:

SELECT CONVERT(varchar(50), getdate(), 100) -- Aug  2 2010 10:40AM
SELECT CONVERT(varchar(50), getdate(), 101) -- 08/02/2010
SELECT CONVERT(varchar(50), getdate(), 102) -- 2010.08.02
SELECT CONVERT(varchar(50), getdate(), 103) -- 02/08/2010
SELECT CONVERT(varchar(50), getdate(), 104) -- 02.08.2010
SELECT CONVERT(varchar(50), getdate(), 105) -- 02-08-2010
SELECT CONVERT(varchar(50), getdate(), 106) -- 02 Aug 2010
SELECT CONVERT(varchar(50), getdate(), 107) -- Aug 02, 2010
SELECT CONVERT(varchar(50), getdate(), 108) -- 10:40:10
SELECT CONVERT(varchar(50), getdate(), 109) -- Aug  2 2010 10:40:10:870AM
SELECT CONVERT(varchar(50), getdate(), 110) -- 08-02-2010
SELECT CONVERT(varchar(50), getdate(), 111) -- 2010/08/02
SELECT CONVERT(varchar(50), getdate(), 112) -- 20100802
SELECT CONVERT(varchar(50), getdate(), 113) -- 02 Aug 2010 10:40:10:870
SELECT CONVERT(varchar(50), getdate(), 114) -- 10:40:10:870

Context

I’m not a big fan of handling exceptions in stored procedures. It means that you have complicated queries, thus have some business logic in them. Although I like to keep my logic in code, sometimes it’s necessary to have some logic in your stored procedure.

Usually, I only use it to increase performance.

continue reading…

The Problem

I generally like using the IDE’s that Microsoft provide for developers. But in this case, I had an annoying problem. When creating my database model I always use Microsoft SQL Server Management Studio. As you all know, during development your tables often change a bit. You create new columns, delete old ones, change the type of a column, define new keys, etc.

In SQL Server 2005 SSMS I don’t remember doing one of the above actions resulted in an error. Now I’m using SQL Server 2008 SSMS and I repeatedly got the error: “Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created. You have either made changes to a table that can’t be re-created or enabled the option Prevent saving changes that require the table to be re-created”.

continue reading…

A new task

Recently I was given a little task about extracting data from a SQL Server 2005 to a Microsoft Access database. The objective was to drop an Access file on a shared filesystem location within the company.

I chose to work with the SQL Server Integration Services (SSIS), which was built for such tasks. You can find some general info about SSIS on the Microsoft SSIS homepage.

SQL Server integration Services is an available option when you’re installing SQL Server 2005 (Standard, Developer or Enterprise Edition).

continue reading…

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…

When you are working with different types of databases (MSSQL, MySQL, Oracle, …), you tend to forget the syntax for some logic you want to implement.

Today I’m going to list different statements on how to insert multiple rows with a single statement.
Let’s say that we have a table, called “User”. Our table contains 3 fields: “UserId”, “UserName”, “Email”. Now we can start adding some rows.

continue reading…