martes, 7 de julio de 2009

Change collation for SQL 2008 Instance

In order to change the Default Engine Collation in SQL Server 2008 you will need to stop the (ALL) MSSQL Server instance(s) and execute the following command from the SQL Server setup media:

setup.exe /q /ACTION=RebuildDatabase /INSTANCENAME=MSSQLSERVER /SAPWD="" /SQLSYSADMINACCOUNTS="BUILTIN\ADMINISTRATORS" /SqlCollation=Latin1_General_CI_AI
Bear in mind that by using the above command you are to all intents erasing and recreating your SQL Servers MASTER database, you will invalidate an previous MASTER backups (as they will contain the previous Collation Settings) and the major knock on effect is that your Current Login and Security configuration will also disappear couple that with the fact your existing Databases will be detached from the server. There are other issues such as backup stats being lost – so be VERY careful when using the above command.

Error changing collation

If you try to change Collation in database and you get some error like this,

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 'BI_CONVERTION' cannot be set to Latin1_General_CI_AI.

Try this solution:
You need to get the database to single-user mode. You may need to go through and kill all of the current sessions so that you can get it to single user mode. Once you do, you essentially have an exclusive lock on the database. At that point, you shoudln't haven't a problem changing the collation.

Another way to make it:

To alter the database state to restrict the access to the single user mode, first open the Microsoft SQL Server Management Studio, and connect to a SQL Server instance. Open the list of available databases belonging to the related instance in the Object Explorer window. Right click on the sql server database that you want to set its mode to single user and select Properties in the context menu.

When you click properties menu item, the following Database Properties screen will be displayed for the selected database.

Select the Options page from the list in the left side of the screen. If you scroll down the options list for State options, you will see Restrict Access database options with three options listed in the combo box.

Restrict Access modes : Multiple, Single and Restricted modes.

If you select Single mode and click the OK button, you can either alter the database access mode to single user successfully or you will fail to change the access mode because of existence of active open connections to the Works database. The Management Studio IDE will prompt you to close all other connections to the related database for a successfull alter database option process.

To change the database properties, SQL Server must close all other connections to the database. Are you sure you want to change the properties and close all other connections?

After the alter command runs successfully, the database XXX will be displayed as shown in the Object Explorer window.

If an active connection exists other than the Management Studio, and you click the database XXX within the SQL Server Management Studio, the following warning message will be displayed:

The database XXX is not accessible. (ObjectExplorer)

If you right click on the database XXX, the following error message will be displayed.

Database 'XXX' is already open and can only have one user at a time. (Microsoft SQL Server, Error: 924)

After a database is altered as single user mode, it can be altered again back to multi user mode by running the below sql command.

ALTER DATABASE [XXX] SET MULTI_USER WITH NO_WAIT


TSQL to change collation of database in SQL 2008

This script is wonderful to change all collation columns:

declare @toCollation sysname

SET @toCollation = 'Latin1_General_CI_AI' -- Database default collate

SELECT 'ALTER TABLE ' + INFORMATION_SCHEMA.COLUMNS.TABLE_NAME +
' ALTER COLUMN ' + COLUMN_NAME + ' ' + DATA_TYPE +
CASE WHEN CHARACTER_MAXIMUM_LENGTH = -1 then '(max)'
WHEN DATA_TYPE in ('text','ntext') then ''
WHEN CHARACTER_MAXIMUM_LENGTH IS NOT NULL
THEN '('+(CONVERT(VARCHAR,CHARACTER_MAXIMUM_LENGTH)+')' )
ELSE isnull(CONVERT(VARCHAR,CHARACTER_MAXIMUM_LENGTH),' ') END
+' COLLATE ' + @toCollation+ ' ' + CASE IS_NULLABLE
WHEN 'YES' THEN 'NULL'
WHEN 'No' THEN 'NOT NULL'

END
FROM INFORMATION_SCHEMA.COLUMNS INNER JOIN INFORMATION_SCHEMA.TABLES
ON INFORMATION_SCHEMA.COLUMNS.TABLE_NAME = INFORMATION_SCHEMA.TABLES.TABLE_NAME
AND INFORMATION_SCHEMA.COLUMNS.TABLE_SCHEMA = INFORMATION_SCHEMA.TABLES.TABLE_SCHEMA
WHERE DATA_TYPE IN ('varchar' ,'char','nvarchar','nchar','text','ntext')
AND TABLE_TYPE = 'BASE TABLE'
and COLLATION_NAME <> @toCollation


This print the scripts in console for execute it, Copy the results in console an paste into new query. Execute, and thats all!