[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

how to check the collation settings?

Posted on 2010-03-31
14
Medium Priority
?
825 Views
Last Modified: 2012-05-09
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?
0
Comment
Question by:saratcm
  • 5
  • 5
  • 3
13 Comments
 
LVL 60

Accepted Solution

by:
chapmandew earned 1000 total points
ID: 29267825
select name, DATABASEPROPERTYEX (name, 'collation')
from sysdatabases
0
 

Author Comment

by:saratcm
ID: 29269195
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
 
LVL 2

Assisted Solution

by:mariraju
mariraju earned 1000 total points
ID: 29269531
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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
LVL 60

Expert Comment

by:chapmandew
ID: 29270605
You need to ask a separate question for how to change.
0
 

Author Comment

by:saratcm
ID: 29273665
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
 
LVL 60

Expert Comment

by:chapmandew
ID: 29275152
This is a separate question. Close this one and ask a new one. I answered your original question.
0
 

Author Comment

by:saratcm
ID: 29276039
Hi Chapmandew,
I made it a seperate quest.
Can u plz check it?
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 29277106
Sure. Close this one too.
0
 
LVL 2

Expert Comment

by:mariraju
ID: 29297487
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
 

Author Closing Comment

by:saratcm
ID: 31709672
Good
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 29790980
thanks, but this was not a "B" answer, it was an A.
0
 
LVL 2

Expert Comment

by:mariraju
ID: 29842026
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
 

Author Comment

by:saratcm
ID: 29907289
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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Microsoft provides a rich set of technologies for High Availability and Disaster Recovery solutions.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.
Suggested Courses

612 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question