Solved

collation on system tables

Posted on 2014-03-08
2
255 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This video Micro Tutorial shows how to password-protect PDF files with free software. Many software products can do this, such as Adobe Acrobat (but not Adobe Reader), Nuance PaperPort, and Nuance Power PDF, but they are not free products. This vide…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…

690 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