.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
MarieAdela
Imran Ghani
Post New Web Links

COLLATE

Posted By:      Posted Date: October 16, 2010    Points: 0   Category :Sql Server
 

Hello,

When I use SELECT statement below it returns wrong results. How can I correct this? I use slovenian language. I executed this in MS SQL server managment studio.

select REPLACE('xxCxx', 'C', ' ')

returns: xx xx

 

---------

select convert(sysname,DatabasePropertyEx(db_name(),'Collation'))-->Latin1_General_CI_AI

 

Thanks




View Complete Post


More Related Resource Links

How to change SQL server 2008 collate after installation

  
Hi, On windows sever 2008 I've installed SQL server 2008 RC0.At the time of installation, I'd specified the collation as LATIN1_GENERAL_BIN, and now I want to change this collation to LATIN1_GENERAL_CI_AS.Please note that I don't want to change the collation of specific db but need to change the sql server 2008 server's settings.How to accomplish this requirement with minimum efforts?Thanks

MS SQL 2005 - order by collate with subquery fails

  

Hi!

This is a simplified version of a real query to show the problem. It can be executed on the sample Adventureworks database:

 

SELECT Person.Address.*,
 (SELECT TOP 1 [Name]
 FROM Person.StateProvince
 WHERE Person.StateProvince.StateProvinceId = Person.Address.StateProvinceId AND Person.StateProvince.TerritoryId IN (5, 6, 7)
 ORDER BY Person.StateProvince.TerritoryId) AS [Name]
FROM Person.Address
ORDER BY [Name]

This works well, but my problem is that if I add collation to the order by field, I get a strange error message:


SELECT Person.Address.*,
 (SELECT TOP 1 [Name]
 FROM Person.StateProvince
 WHERE Person.StateProvince.StateProvinceId = Person.Address.StateProvinceId AND Person.StateProvince.TerritoryId IN (5, 6, 7)
 ORDER BY Person.StateProvince.TerritoryId) AS [Name]
FROM Person.Address
ORDER BY [Name] COLLATE Chinese_PRC_CI_AI

 

And the error is:

 

Msg 207, Level 16, State 1, Line 7
Invalid column name 'Name'.

 

The query with the collate clause works well if there's no subquery, but I need it to be like this (for a complicated reason, but please believe me :)).

Does anybody

Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend