how to check the collation settings?

Hi All,
How to check the current collation setting on SQL Server 2000?
Is there any way to change those collation settings?

In SQL Server 2005, How to see whether the server is running on a built-in account or a domain account?
saratcmAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

chapmandewCommented:
select name, DATABASEPROPERTYEX (name, 'collation')
from sysdatabases
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
saratcmAuthor Commented:
Hi Thanks for the quick response,
Can you tell me how to change the collation setting for a particular database?
bcoz after running that what I found is one user database is having a different collation setting than the rest.
And how to see in SQL Server 2005 that server is runnig on inbuit or domain account?
0
marirajuCommented:
Hi there,

You can check the collation from the above query. But to change follow these queries

To change the Database collation -
ALTER DATABASE MyDB COLLATE SQL_Latin1_General_Cp1250_CS_AS

To change the instance collation -
start /wait setup.exe /q INSTANCENAME=MSSQLSERVER REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=test SQLCOLLATION=SQL_Latin1_General_CP1_CI_AI

Please follow this link for finding out the SQL server account
http://www.sqlmag.com/article/permissions/discovering-your-sql-server-user-account.aspx

Bye for now

Cheers
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

chapmandewCommented:
You need to ask a separate question for how to change.
0
saratcmAuthor Commented:
Hi mariraju,
This was the error I got when I tried to change the collation settings.
will it wrk if I take the database into single user?
Msg 5030, Level 16, State 2, Line 1
The database could not be exclusively locked to perform the operation.
Msg 5072, Level 16, State 1, Line 1
ALTER DATABASE failed. The default collation of database 'mciw_sideB' cannot be set to SQL_Latin1_General_CP1250_CS_AS.

What does this error means?
0
chapmandewCommented:
This is a separate question. Close this one and ask a new one. I answered your original question.
0
saratcmAuthor Commented:
Hi Chapmandew,
I made it a seperate quest.
Can u plz check it?
0
chapmandewCommented:
Sure. Close this one too.
0
marirajuCommented:
Yes , Please use the single user(administrator) connection for the database and it works fine.

I have tried the same query for database with single user, which worked well for me

Please let me know
0
saratcmAuthor Commented:
Good
0
chapmandewCommented:
thanks, but this was not a "B" answer, it was an A.
0
marirajuCommented:
Hello again,

To put the database into single user mode, please try the following. After that, you should be able to run the rest of the commands easily.

ALTER DATABASE database_name  SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

Once the job is done, please put the database back into multi user mode.

ALTER DATABASE database_name  SET MULTI_USER
GO

I hope this solution works for you. Please do let me know.

Cheers
0
saratcmAuthor Commented:
Hi,
I tried to change the instance collation with the cmd you given. It didn't worked out for me.
My case is a defaultinstance and server is running using inbuilt account.
Can you check it once if there is any error in that?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.