SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

More Collation Confusion. (tempdb)

I’ve talked about Collation Confusion before. We had the dev and test instances at one collation and the production instance was another collation. Wow, was that a headache.

Well, we had a similar issue recently. This time we had a vendor reporting the following error:

Msg 207, Level 16, State 1, Line 7
Invalid column name ‘Name’

Now the vendor was certain this was a permissions issue. It worked fine on their systems, it worked fine on some of ours. So why didn’t it always work? Well, the easy answer is permissions! Particularly since we had denied them db_owner just recently.

So why do I sound so dismissive about permissions as a possibility? I mean it COULD be permissions. It certainly is possible. But first of all, we don’t use column level permissions very often (no one uses them all that often from what I can tell) and secondly it worked on several other systems where they had exactly the same permissions as this system.

Ok, so what is the problem? You guessed it! (I really have to stop asking for guesses after I’ve put the answer in the title.) Collation!

See here’s the thing. If your instance collation is case sensitive then tempdb is case sensitive. (The instance this DB got put on was case sensitive.)

This means that if you don’t specify the column collation it’s going to be case sensitive. Regardless of the database you are in a temp table uses the collation of tempdb.

Try running this code from a case-sensitive DB and from a non-case sensitive DB.

CREATE TABLE #temp (id int, name varchar(30));
GO
SELECT TABLE_NAME, COLUMN_NAME, COLLATION_NAME 
FROM tempdb.INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME LIKE '%#temp%'
  AND COLLATION_NAME IS NOT NULL;
GO

In both cases, the results will be the same. The collation name will match the tempdb default.


Ok, but that isn’t going to cause that first error. That first error is caused by the fact that the system table collation is going to match the database collation. Run this on two different databases with different collations.

SELECT collation_name FROM sys.databases
WHERE database_id = DB_ID();
SELECT collation_name FROM sys.all_columns
WHERE object_id = object_id('sys.tables')
  AND [name] = 'name';

In both cases the DB collation and the colation of name in sys.tables is going to be the same.

So end result? If the instance is case sensitive, tempdb is case sensitive, the system tables of tempdb are case sensitive. When querying temp tables you have to treat them as if they are case sensitive. Because they are.


Filed under: Collation, Microsoft SQL Server, SQLServerPedia Syndication, System Databases Tagged: collation, microsoft sql server, tempdb database

SQLStudies

My name is Kenneth Fisher and I am Senior DBA for a large (multi-national) insurance company. I have been working with databases for over 20 years starting with Clarion and Foxpro. I’ve been working with SQL Server for 12 years but have only really started “studying” the subject for the last 3. I don’t have any real "specialities" but I enjoy trouble shooting and teaching. Thus far I’ve earned by MCITP Database Administrator 2008, MCTS Database Administrator 2005, and MCTS Database Developer 2008. I’m currently studying for my MCITP Database Developer 2008 and should start in on the 2012 exams next year. My blog is at www.sqlstudies.com.

Comments

Leave a comment on the original post [sqlstudies.com, opens in a new window]

Loading comments...