Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

URgent help needed: change sql server collationproperty Expand / Collapse
Author
Message
Posted Thursday, April 24, 2008 1:29 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, April 8, 2014 2:57 AM
Points: 48, Visits: 253
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.
Post #489706
Posted Thursday, April 24, 2008 3:28 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 12:52 AM
Points: 1,454, Visits: 2,959
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


Facts are stubborn things, but statistics are more pliable - Mark Twain
Carolyn
SQLServerSpecialists
Post #489746
Posted Thursday, April 24, 2008 4:03 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 6:13 AM
Points: 4,432, Visits: 4,167
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




Markus Bohse
Post #489762
Posted Thursday, April 24, 2008 4:19 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, February 27, 2014 3:44 AM
Points: 179, Visits: 331
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.
Post #489774
Posted Thursday, April 24, 2008 7:49 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, February 22, 2010 3:13 AM
Points: 798, Visits: 36
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!
Post #489936
Posted Thursday, April 24, 2008 8:01 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, April 8, 2014 2:57 AM
Points: 48, Visits: 253
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 .





Post #489943
Posted Thursday, April 24, 2008 8:04 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, April 8, 2014 2:57 AM
Points: 48, Visits: 253
Yes but our application use views and user defined datatypes. , The collate query cant be used across all queries
Post #489947
Posted Thursday, April 24, 2008 1:57 PM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 12:52 AM
Points: 1,454, Visits: 2,959
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



Facts are stubborn things, but statistics are more pliable - Mark Twain
Carolyn
SQLServerSpecialists
Post #490290
Posted Thursday, April 24, 2008 11:29 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, April 8, 2014 2:57 AM
Points: 48, Visits: 253
Thanks for u r timely help,

its working fine
Post #490418
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse