SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


COLLATION


COLLATION

Author
Message
deepzzzz
deepzzzz
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1235 Visits: 261
Comments posted to this topic are about the item COLLATION

Thanks
Steve Hall
Steve Hall
SSCertifiable
SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)

Group: General Forum Members
Points: 5004 Visits: 11863
An interesting question about commands that I was not aware of.

However, your explanation of fn_helpcollations is not entirely accurate for those of us using 2008. BOL describes it as 'Returns a list of all the collations supported by SQL Server 2008' with a note 'Deprecated collations that are no longer supported or installed in SQL Server 2008 cannot be queried with fn_helpcollations'. I have no idea how the 2005 BOL describes it, but if you don't specify the version you need to be aware of all of the possible variations in the documentation.

Steve Hall
Linkedin
Blog Site
cengland0
cengland0
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2218 Visits: 1300
Curious why it was being converted to Char in the query.
vk-kirov
vk-kirov
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4300 Visits: 4408
Conversion to CHAR is wrong, because in this case we get a collation name which is truncated to 30 symbols: Chinese_Hong_Kong_Stroke_90_CS instead of Chinese_Hong_Kong_Stroke_90_CS_AS_KS_WS, SQL_Latin1_General_CP1250_CI_A insted of SQL_Latin1_General_CP1250_CI_AS etc.
The type of a collation name is nvarchar(128) (http://msdn.microsoft.com/en-us/library/ms190305.aspx), so the correct statement should be "SELECT CONVERT(nvarchar(128), ...)".
DigitalTeapot
DigitalTeapot
SSC Rookie
SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)

Group: General Forum Members
Points: 40 Visits: 4
Hi

The following TSQL statement would also work (SQL Server 2005 and later):

SELECT collation_name FROM sys.databases WHERE name = N'MyDatabase'



Regards,
DigitalTeapot
Peter Rijs
Peter Rijs
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1378 Visits: 602
I'm also curious for the reason to convert to char, as the following syntax will work just fine for me:
SELECT DATABASEPROPERTYEX('MyDatabase','collation')


It is very clear what answer should be picked on this question, since the other 2 answers do not return (a part of) the database collation (though server collation might be, but is not necessarily, the same value as the database collation).

Peter Rijs
BI Consultant, The Netherlands
SanDroid
SanDroid
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2364 Visits: 1046
Nice question.
I learned something, there were no errors or typos with the answer, and the reference data supported the documentation.

The only negative comment I have is that this code:
SELECT CONVERT(char, SERVERPROPERTY('collation'),'MyDatabaseName')



Returned an error.
cengland0
cengland0
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2218 Visits: 1300
SanDroid (11/3/2010)
Nice question.
I learned something, there were no errors or typos with the answer, and the reference data supported the documentation.

The only negative comment I have is that this code:
SELECT CONVERT(char, SERVERPROPERTY('collation'),'MyDatabaseName')



Returned an error.


It's okay that it returned an error because that wasn't the correct answer. The author of the question probably included that as a distractor in an attempt to get some people to select that answer.
Daniel Bowlin
Daniel Bowlin
SSCertifiable
SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)

Group: General Forum Members
Points: 7702 Visits: 2629
Good question. Thanks to VK to pointing out the Char issue (reinforcing what I know.....but frequently forget).
Michael Poppers
Michael Poppers
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1109 Visits: 416
cengland0 (11/3/2010)
SanDroid (11/3/2010)
Nice question.
I learned something, there were no errors or typos with the answer, and the reference data supported the documentation.

The only negative comment I have is that this code:
SELECT CONVERT(char, SERVERPROPERTY('collation'),'MyDatabaseName')



Returned an error.


It's okay that it returned an error because that wasn't the correct answer. The author of the question probably included that as a distractor in an attempt to get some people to select that answer.

Yes, he might have been better off listing the possible choice as
SELECT CONVERT(char, SERVERPROPERTY('collation','MyDatabaseName'))

and perhaps that was his intention :-).
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search