COLLATION

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Anantheswar G » COLLATION

16 posts, Page 1 of 2

1 2 »»

COLLATION

Rate Topic

Display Mode

Topic Options

Author

Message

deepzzzz

Posted Tuesday, November 02, 2010 9:41 PM

SSC Eights!

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 4:23 AM
Points: 876, Visits: 253

Comments posted to this topic are about the item COLLATION

Thanks

Post #1015012

BrainDonor

Posted Wednesday, November 03, 2010 2:10 AM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 6:59 AM
Points: 1,400, Visits: 6,894

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

Post #1015063

cengland0

Posted Wednesday, November 03, 2010 2:44 AM

Ten 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

vk-kirov

Posted Wednesday, November 03, 2010 3:03 AM

Hall of Fame

Group: General Forum Members
Last Login: Today @ 3:58 AM
Points: 3,191, Visits: 4,149

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

DigitalTeapot

Posted Wednesday, November 03, 2010 3:32 AM

Grasshopper

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

Peter Rijs

Posted Wednesday, November 03, 2010 4:35 AM

SSC Eights!

Group: General Forum Members
Last Login: Thursday, April 25, 2013 6:03 AM
Points: 856, Visits: 573

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
MCITP BI Dev & DB Dev (SQL 2008 & 2005)

Post #1015105

SanDroid

Posted Wednesday, November 03, 2010 7:05 AM

Ten 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

cengland0

Posted Wednesday, November 03, 2010 7:10 AM

Ten 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

Daniel Bowlin

Posted Wednesday, November 03, 2010 8:19 AM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 1:10 PM
Points: 2,673, Visits: 2,418

Good question. Thanks to VK to pointing out the Char issue (reinforcing what I know.....but frequently forget).

Post #1015250

Michael Poppers

Posted Wednesday, November 03, 2010 9:39 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, January 17, 2013 2:34 PM
Points: 565, Visits: 360

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 Smile

Post #1015357

« Prev Topic | Next Topic »

16 posts, Page 1 of 2

1 2 »»

Permissions