select SERVERPROPERTY('COLLATION')

Question

Post reply

select SERVERPROPERTY('COLLATION')

chandan_jha18

SSChampion

Points: 11330
More actions
July 27, 2011 at 3:43 am

#244994

When I run this query in my 2008\2005 server, I get the collation that my server has. I was curious to know, from where SQL Server reads and fetches this data for me?
Appreciate your replies!
Thanks
Chandan

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Brandie Tarvin SSC Guru Points: 173064 More actions · Answer 1

Have you tried running sp_helptext on the function?

EDIT: Nevermind. Apparently it doesn't work on this function.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

chandan_jha18 SSChampion Points: 11330 More actions · Answer 2

Brandie Tarvin (7/27/2011)
Have you tried running sp_helptext on the function?
EDIT: Nevermind. Apparently it doesn't work on this function.

I ran 'sp_helptext'SERVERPROPERTY' in master database and it gave me following results:

Msg 15009, Level 16, State 1, Procedure sp_helptext, Line 54

The object 'SERVERPROPERTY' does not exist in database 'master' or is invalid for this operation.

Brandie Tarvin SSC Guru Points: 173064 More actions · Answer 3

Hence my edited comment.

I'm trying to figure this one out. I know that sys.databases has collation, but I'm not sure where the server information is or even if that's where the function gets its data from.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Brandie Tarvin SSC Guru Points: 173064 More actions · Answer 4

I missed this when I looked earlier. Table sys.servers has a column called collation_name. Mine is NULL because we use the default, which is based on the OS collation. (Note: this is a W.A.G. based on what I know of my system).

I don't know for sure that's where this function gets its information from. But if my W.A.G. is correct, then it's reading the OS collation property because the data in sys.servers is NULL. It could be reading some other hidden system table that I can't find, though.

Someone please correct me if I'm wrong.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

chandan_jha18 SSChampion Points: 11330 More actions · Answer 5

There is an entry for collation in HKLM\Software\Microsoft\Microsoft SQL Server\<Instance ID>\Setup.

the original entry was SQL_Latin1_General_CP1_CI_AS. I changed it to 'chandan' and rebooted the server.

after reboot, the registry entry is 'Chandan' only but when I run SERVERPROPERTY function, i still get SQL_Latin1_General_CP1_CI_AS as my collation. So it means registry entry didn help and this value is stored somewhere else too.

Please help me to reach the end of the tunnel!

Brandie Tarvin SSC Guru Points: 173064 More actions · Answer 6

I'm almost scared to ask...

Why are you changing the registry setting for collation? And to a value that isn't even a proper collation value?

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

chandan_jha18 SSChampion Points: 11330 More actions · Answer 7

Brandie Tarvin (7/27/2011)
I'm almost scared to ask...
Why are you changing the registry setting for collation? And to a value that isn't even a proper collation value?

I tried it on my local machine. I changed this because one person told me on MSDN forum that the collation read from the registry. I changed the registry value for this column but still does not see the change on the query output.

Lowell SSC Guru Points: 323518 More actions · Answer 8

based on this ms doc, where changing the server collation requires a rebuild:

http://msdn.microsoft.com/en-us/library/ms179254.aspx,

i'm guessing that the server collation is actually the collation of the resource database, regardless of whatever might be in the registry.

then as a result, the master database gets the collation of the resource database.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

chandan_jha18 SSChampion Points: 11330 More actions · Answer 9

Lowell (7/27/2011)
based on this ms doc, where changing the server collation requires a rebuild:
http://msdn.microsoft.com/en-us/library/ms179254.aspx,
i'm guessing that the server collation is actually the collation of the resource database, regardless of whatever might be in the registry.
then as a result, the master database gets the collation of the resource database.

I can copy the resource database files and then restore them as a normal database. will the information be stored somewhere?

Brandie Tarvin SSC Guru Points: 173064 More actions · Answer 10

chandan_jha18 (7/27/2011)
Lowell (7/27/2011)
based on this ms doc, where changing the server collation requires a rebuild:
http://msdn.microsoft.com/en-us/library/ms179254.aspx,
i'm guessing that the server collation is actually the collation of the resource database, regardless of whatever might be in the registry.
then as a result, the master database gets the collation of the resource database.
I can copy the resource database files and then restore them as a normal database. will the information be stored somewhere?

The resource database is a hidden db. Copying the files and restoring them as a user db won't change anything in the original db. And it won't make SQL Server look in the user db for any information.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.