Solved

collation on system tables

Posted on 2014-03-08
2
249 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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

706 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now