Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

COLLATION Expand / Collapse
Author
Message
Posted Tuesday, November 2, 2010 9:41 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, August 14, 2014 6:01 AM
Points: 885, Visits: 259
Comments posted to this topic are about the item COLLATION

Thanks
Post #1015012
Posted Wednesday, November 3, 2010 2:10 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 4:12 AM
Points: 1,541, Visits: 8,191
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.


BrainDonor
Linkedin
Blog Site
Post #1015063
Posted Wednesday, November 3, 2010 2:44 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 24, 2013 9:59 PM
Points: 1,354, Visits: 1,299
Curious why it was being converted to Char in the query.
Post #1015077
Posted Wednesday, November 3, 2010 3:03 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, May 9, 2014 12:47 AM
Points: 3,448, Visits: 4,407
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), ...)".
Post #1015082
Posted Wednesday, November 3, 2010 3:32 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, June 13, 2011 4:03 AM
Points: 14, 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
Post #1015088
Posted Wednesday, November 3, 2010 4:35 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, August 18, 2014 9:39 AM
Points: 898, Visits: 600
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
Post #1015105
Posted Wednesday, November 3, 2010 7:05 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 31, 2013 8:01 AM
Points: 1,232, Visits: 1,046
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.
Post #1015180
Posted Wednesday, November 3, 2010 7:10 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 24, 2013 9:59 PM
Points: 1,354, Visits: 1,299
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.
Post #1015185
Posted Wednesday, November 3, 2010 8:19 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 8:39 AM
Points: 2,818, Visits: 2,558
Good question. Thanks to VK to pointing out the Char issue (reinforcing what I know.....but frequently forget).
Post #1015250
Posted Wednesday, November 3, 2010 9:39 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Monday, July 7, 2014 8:58 AM
Points: 645, Visits: 400
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 .
Post #1015357
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse