Intrebari

Re: Change Server Collation - intrebare pe ITBoard

xmldeveloper — Mon, 11 Dec 2006 11:11:27 GMT

Razvan un ALTER DATABASE e mai pasnic decat un rebuild de master. Parerea mea personala!

Re: Change Server Collation - intrebare pe ITBoard

MrSmersh — Mon, 11 Dec 2006 08:31:14 GMT

Eu am confundat putin "collation" cu "collation setting" cred despre care in ghidul de studiu pentru 70-431 de la Sybex se zice:

"once you have installed SQL Server, you cannot change the collation setting. To change it, you must reinstall SQL Server and rebuild all your databases. So, choose wisely; it is usually best to use the default sort setting of case insensitivity and build sensitivity into your applications if you need it."

Si cu collation de baza de date m-am tot jucat, si in general a mers bine si fara probleme.

Re: Change Server Collation - intrebare pe ITBoard

rsocol — Mon, 11 Dec 2006 08:07:35 GMT

xmldeveloper:
Insa eu ti-as recomanda sa schimbi collation la nivel de baza de date cu ALTER DATABASE.

În general, eu nu recomand să folosiţi baze cu un collation diferit de cel al server-ului, deoarece puteţi avea probleme atunci când folosiţi join-uri cu tabele temporare sau cu tabele sistem.

De exemplu:

CREATE DATABASE db COLLATE French_CI_AS
GO
USE db
CREATE TABLE TBL(X varchar(10) PRIMARY KEY)
CREATE TABLE #TMP(X varchar(10) PRIMARY KEY)
GO
SELECT * FROM TBL A INNER JOIN #TMP B ON A.X=B.X

GO
/*
DROP TABLE #TMP
USE master
DROP DATABASE db
*/

produce eroarea:

Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "French_CI_AS" in the equal to operation.

Sigur, există şi cazuri în care trebuie să folosim baze de date cu collation-uri diferite pe acelasi server, din cauza unor factori care nu sunt sub controlul nostru. În asemenea cazuri, putem rezolva problema specificând collation-ul la crearea tabelelor temporare sau folosind clauza COLLATE în comparaţie:

SELECT * FROM TBL A INNER JOIN #TMP B ON A.X=B.X COLLATE French_CI_AS

Răzvan

Re: Change Server Collation - intrebare pe ITBoard

xmldeveloper — Sun, 10 Dec 2006 12:33:57 GMT

Pentru SQL Server 2005: Setting and Changing Collations

SQL Server 2005 collations can be specified at any level. When you install an instance of SQL Server 2005, you specify the default collation for that instance. Each time that you create a database, you can specify the default collation used for the database. If you do not specify a collation, the default collation for the database is the default collation for the instance. Whenever you define a character column, variable, or parameter, you can specify the collation of the object. If you do not specify a collation, the object is created by using the default collation of the database.
The following topics explain how to work with collations at the server, database, column, expression, and identifier levels:
Setting and Changing the Server Collation
Setting and Changing the Database Collation
Setting and Changing the Column Collation
Setting Expression Collation
Identifier Collation

La nivel de instanta: Setting and Changing the Server Collation

Changing the Server Collation
Changing the default collation for an instance of SQL Server 2005 can be a complex operation and involves the following steps:
Make sure you have all the information or scripts needed to re-create your user databases and all the objects in them.
Export all your data using a tool such as bulk copy.
Drop all the user databases.
Rebuild the master database specifying the new collation in the SQLCOLLATION property of the setup command. For example:
start /wait setup.exe /qb INSTANCENAME=MSSQLSERVER REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=test SQLCOLLATION=SQL_Latin1_General_CP1_CI_AI
For more information about rebuilding the master database, see How to: Rebuild the Master Database for SQL Server 2005.
Create all the databases and all the objects in them.
Import all your data.
Note:
Instead of changing the default collation of an instance of SQL Server 2005, you can specify a default collation for each new database you create.

Note:
Instead of changing the default collation of an instance of SQL Server 2005, you can specify a default collation for each new database you create.

Re: Change Server Collation - intrebare pe ITBoard

MrSmersh — Sun, 10 Dec 2006 12:20:00 GMT

Sa inteleg ca pe 2000 se putea face asta?
Sau nu am inteles eu despre ce e vorba?
Sau nu am inteles cum e in 2005?

Re: Change Server Collation - intrebare pe ITBoard

xmldeveloper — Sun, 10 Dec 2006 11:50:14 GMT

In general se face rebuild la master http://support.microsoft.com/kb/257716

Q. How do I rebuild the master database?

A: To rebuild the master database of a SQL Server 2000 instance, use the Rebuild Master Utility, Rebuildm.exe. You must rebuild the master database to fix corrupted master databases or to change collation settings for the instance. Detailed steps and information are available in the "How to rebuild the master database" topic in SQL Server Books Online. Also, be aware of the issue that is mentioned in the following article in the Microsoft Knowledge Base:
273572 (http://support.microsoft.com/kb/273572/) BUG: Rebuildm.exe utility stops responding when source directory is on a CD

Insa eu ti-as recomanda sa schimbi collation la nivel de baza de date cu ALTER DATABASE.

Change Server Collation - intrebare pe ITBoard

xmldeveloper — Sun, 10 Dec 2006 11:47:57 GMT

Valy Greavu wrote the following post at 12-08-2006 14:01:
Salut,
Imi poate spune cinve care ar fi calea sa modific Collate pentru tot serverul de SQL 2000?
Stiu ca am mai facut odata treaba asta dar nu mai gasesc nici in ruptul capului cum se o face....
Multumesc!