Solved

collation on system tables

Posted on 2014-03-08
2
253 Views
Last Modified: 2014-03-10
how come this gives error?

Msg 468, Level 16, State 9, Line 2
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "SQL_Latin1_General_CP850_BIN" in the EXCEPT operation.

are system tables subject to collation too?


select name from VKDVUL.sys.all_objects where type = 'fn'
except
select name from VKDVUL1.sys.all_objects where type = 'fn'
0
Comment
Question by:25112
2 Comments
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 250 total points
ID: 39915260
are system tables subject to collation too?
Absolutely.  They acquire the same COLLATION as the database.

Incidentally, sys.all_objects is not a table.  It is a system VIEW.
0
 
LVL 35

Accepted Solution

by:
David Todd earned 250 total points
ID: 39916715
Hi,

Welcome to the world of similar collations that need to be explicitly cast.

I've found that unless there are very good (ie vendor documented) reasons, the collation on the database should be the same as the collation on the server. As tempdb collation follows that from Model, you can have issues comparing the results from temp tables vs your databases tables if the collations are different.

Also if you install a case insensitive collation for SQL 2000, the SQL authenticated passwords are case insensitive.

Regards
  David
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question