April 24, 2008 at 1:29 am
URgent help needed: change sql server collation property
HOw to change the collation property of server to
SQL_1xCompat_CP850_CI_AS
from exisitng
SQL_Latin1_General_CP1_CI_AS
The error thrown was
this
Server: Msg 468, Level 16, State 9, Procedure Line 770
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "SQL_1xCompat_CP850_CI_AS" in the equal to operation.
April 24, 2008 at 3:28 am
To change the collation at server level I believe you need to rebuild the master database, to handle the query:
SELECT ID
FROM ItemsTable
INNER JOIN AccountsTable
WHERE ItemsTable.Collation1Col COLLATE DATABASE_DEFAULT
= AccountsTable.Collation2Col COLLATE DATABASE_DEFAULT
April 24, 2008 at 4:03 am
Carolyn is right. In otrder to change the server collation you need to re-run setup from the command line with the following options
start setup.exe /qn INSTANCENAME=MSSQLSERVER REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=
But be aware that this will rebuild the master and the msdb database. So make sure that you have saved things like logins, jobs, SSIS packages in msdb, etc.
See also http://msdn2.microsoft.com/en-us/library/ms144259.aspx
[font="Verdana"]Markus Bohse[/font]
April 24, 2008 at 4:19 am
Yeah....to change the collation property of the server you need to run setup once again, after backing up everything which are required.....
Well, you can always change the collation property of a database, by going to database property > Option and selecting the relevant collation....
Chandrachurh Ghosh
DBA – MS SQL Server
Ericsson India Global Services Limited
Quality is not an act, it is a habit.
April 24, 2008 at 7:49 am
I might be missing the point, but why do you REALLY need to change the server collation? That is not going to change the collation across all your databases etc so from your initial post I assume the problem is a collation error reported from some sp.
Most common cause of these errors is where a sp or sql batch creates a temporary table including char type columns which are then joined to the main db tables.
This always has the potential for a collation conflict so ALL char type columns in temp tables should always be created as follows: mycolumn nvarchar(50) collate database_default.
That way the temp table will pick up its collations from the current database context rather than tempdb.
You can also add "collate database_default" within pretty much any element of a SQL statement, including select clause, joins etc.
Sorry if I'm way off course but that what it sounds like to me!
April 24, 2008 at 8:01 am
When server collation and db collation differs , the view fails
The DB was deployed from a Back up , so the error has come ,
we was searching for a way without rebuilding the master db or
reinstalling sql server .
April 24, 2008 at 8:04 am
Yes but our application use views and user defined datatypes. , The collate query cant be used across all queries
April 24, 2008 at 1:57 pm
If you have the available time, you could script out the database without any of the collation statments, you could then create the new database with the script and migrate the data into the new database. Drop the old database and rename the new one. In this way the new database will have the server default collation.
Or if you only have the problem with a few tables use the following script to identify which the tables are:-
USE MASTER
GO
SET NOCOUNT ON
/*To find database default*/
DECLARE @name sysname, @Collate varchar(50)
SET @name =db_name()
SET @Collate =(SELECTconvert(sysname,DatabasePropertyEx(@name,'Collation')))
PRINT'Server Collation is '+@Collate
/*--------------------------------------------------------------------------------------------------
Purpose: This script will identify tables on all databases on a server that have differing collations
Author: Carolyn Richardson
Date: 05/10/2007
--------------------------------------------------------------------------------------------------*/
USE MASTER
GO
SET NOCOUNT ON
DECLARE @DB VARCHAR(150),
@Counter INT,
@Rec VARCHAR(150),
@sql VARCHAR(1000),
@SQL1 VARCHAR(1000),
@SQL2 VARCHAR(1000)
SELECT database_id, name INTO #Temp
FROM sys.databases
WHERE name NOT IN ('Master','tempdb','msdb','model')
SET @Counter =(SELECT MIN(database_id) FROM #Temp)
/*Work out if a database has more than one collation, assumes only interested if has more than one collation*/
CREATE TABLE #ctr
( NumRows int)
WHILE @Counter <=(SELECT MAX(database_id) FROM #Temp)
BEGIN
SET @DB =(SELECT name FROM #Temp
WHERE database_id = @Counter)
SET @sql ='INSERT INTO #ctr SELECT count(distinct COLLATION_NAME)
FROM '+ @DB +'.INFORMATION_SCHEMA.columns
WHERE COLLATION_NAME LIKE ''%Latin1%'' '
EXEC (@SQL)
SET @Rec =(SELECT NumRows FROM #ctr)
DELETE FROM #ctr
IF(@Rec > 1)
BEGIN
PRINT @DB
SET @SQL1 ='SELECT TABLE_CATALOG AS [DATABASE], ' SET @SQL1 = @SQL1 +'TABLE_NAME, ' SET @SQL1 = @SQL1 +'COLLATION_NAME, ' SET @SQL1 = @SQL1 +'COLUMN_NAME, ' SET @SQL1 = @SQL1 +'DATA_TYPE ' SET @SQL1 = @SQL1 +'FROM '+ @DB +'.INFORMATION_SCHEMA.columns ' SET @SQL1 = @SQL1 +'WHERE TABLE_NAME <> ''dtproperties'' ' SET @SQL1 = @SQL1 +'AND COLLATION_NAME LIKE ''%Latin1%'' ' SET @SQL1 = @SQL1 +'ORDER BY COLUMN_NAME'
EXEC (@SQL1)
END
SET @Counter = @Counter + 1
END
DROP TABLE #ctr
GO
DROP TABLE #Temp
April 24, 2008 at 11:29 pm
Thanks for u r timely help,
its working fine
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply