Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
frank.svs
Constraint Violating Yak Guru
368 Posts |
 Posted - 2011-03-22 : 18:02:22
|
| Hi Team,Need some claifications on collation settings at server level and database level.Questions on Collations===========================1. I have two servers. On both servers the collation is different and database collations is also different but both have same table structure. Am trying to do a UNION ALL from both the database and am getting the below error.Msg 457, Level 16, State 1, Procedure usp_Staff, Line 16Implicit conversion of varchar value to varchar cannot be performed because the collation of the value is unresolved due to a collation conflict. I resolved it using the below sql SELECT c1, c2 FROM db1..t1UNION allSELECT c1 COLLATE DATABASE_DEFAULT ,c2 COLLATE DATABASE_DEFAULTFROM [linkserver].[db2]..t1 But i have few doubts regarding the collation settings.case - 1. Tried the restore the db1 with collation 'A' (Say) to a different instance whose server collation 'B'. Under this instance whose collation is 'B'(say) and my restored database collation is 'A' which is same as original src instance server collation and db collation, still am getting the above conversion error and i had to use the explcit COLLATE DATABASE_DEFAULT KEYWORD in select statements. I would like to know the reason for this? How internally this collation settings works and why it is not working here in this case.case - 2. For existing data in a database if i change the collation, then also it didnt work. why ? i had to use collate database dfault keyword. Somewhere i have read for existing eventhough the collation is changed it is not affected. what happens internally?case - 3. Also, which is the best recommended way. whether two instances must be always in the same collations? i might have 2 instances for diff app's and few database i need to have this communication using linked servers. which is the best way to implement such scenario's??? These are two collation's am using Latin1_General_CI_AS_KS_WS --collation-A SQL_Latin1_General_CP1_CI_AS --collation-BCan anyone shed some light on the above scenarios with supporting links which justification. This would help me in understanding whats will happen if a collation gets changed and what will be the behaviour of the sql engine and what does sql engine will do internally if i try to do a UNION ALL with two SELECT statements.Any thoughts would be a great help.Looking forward for your suggestions and views on this.Thanks in Advance. |
|
|
|
|
|
collation settings