Skip to content

MikeBevers.be

Putting it out there…

Archive

Tag: MSSQL

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!

Context

I wanted log4net to write its log entries in a SQL Server database. log4net’s AdoNetAppender would have done the trick. But, there are three requirements I need to keep in mind.

  1. I have to use my logging framework
  2. The connectionstring has to be stored the app.config or web.config
  3. The machine’s hostname has to be added to the log entry

continue reading…

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…

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…