URgent help needed: change sql server collationproperty

  • 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.

  • 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[/url]

  • 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]

  • 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.

  • 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!

  • 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 .

  • Yes but our application use views and user defined datatypes. , The collate query cant be used across all queries

  • 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[/url]

  • 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