This document explains how to change collation name of a database. Before starting,  we need to understand what is Collation.

Collation corresponds to character set in SQL Server. It determines how to use characters in equivalences, order operations, lower and uppercase changes. The "CI" phrase used in  Turkish_CI_AS or SQL_Latin1_General_CP1254_CI_ AS collation that is generally used in Turkish databases means Case Insensitive.

Especially in SQL 2000, Turkish_CI_AS used to be selected as a collation name;  however, SQL_Latin1_General_CP1254_CI_AS is started to be used mostly after upgrading to SQL 2005 and SQL 2008.  

The error received during company update ("Cannot resolve the collation conflict between "SQL_latin1_General_CP1254_CI_AS" and "Turkish_CI_AS" in the equal to operation") occurs when the collation name of company tables (LG_XXX_XX_.... ) and company independent tables (L_....)  are different or if they belong to different company databases or when the collation name of the master database and firm database are different.

Example:

Collation information of Master Database tables is Turkis_CI_AS  and company database collation information is SQL_Latin1_General_CP1254_CI_AS.  Considering this information, this error may occur  if the collation names of the fields of VARCHAR type that are to be equalized are different while equalizing L_TRADGRP table (located under master database given in the LV_XXX_XX_TRDGRP view query) and LG_XXX_XX_CLFLINE table (located under company database) during company update. 

L_TRADGRP.GCODE = LG_XXX_XX_CLFLINE.TRADINGGRP
http://support.logo.com.tr/images/Documents/NDS/resim1.png

When the following steps are followed in the given order to solve collation problems, the collation name information of database and tables (including custom tables) will be equalized.

ATTENTION:

Before starting operation steps, make sure that collation name info of the tables and column are correct. Erroneous records can be detected using the following query.

The following query lists the records except for those of which collation name is 'SQL_Latin1_General_CP1254_CI_AS’.

Note: Make sure that the correct database is selected in Query Analyzer.

SELECT * FROM

INFORMATION_SCHEMA.COLUMNS

WHERE DATA_TYPE IN ('varchar')

AND COLLATION_NAME NOT LIKE 'SQL_Latin1_General_CP1254_CI_AS'

Operation Steps:

1 – Users working with the application must be removed from the system, and database must be backed up.

----------------------------------------------------------------------------------------------------------

2 -  SQL Query Analyzer should be opened and the relevant database should be selected to change collation name.  (If there are different company databases, the same operations must be performed for each company database.)

Note: These operations can be performed only on the Query Analyzer. They should not be performed over Vtyonet.exe.

http://support.logo.com.tr/images/Documents/NDS/resim2.png

----------------------------------------------------------------------------------------------------------

3 –  The following query must be copied to theQuery Analyzer and NonClustered indices should be deleted.

Note: Make sure that the correct database is selected in Query Analyzer and you take a back up.

 declare @ST_Indexes table

(    SiraNo  int identity(1,1) primary key clustered,

    Tablo_Adi nvarchar(255),

    Index_Adi nvarchar(255))

INSERT INTO @ST_Indexes

(   Tablo_Adi, Index_Adi)

SELECT  sys.objects.name AS Tablo_Adi,

        sys.indexes.name AS Index_Adi

FROM    sys.indexes

        JOIN sys.objects ON sys.indexes.object_id = sys.objects.object_id

WHERE   sys.indexes.type_desc = 'NONCLUSTERED'

        AND sys.objects.type_desc = 'USER_TABLE' AND sys.objects.type='U'

DECLARE @Max INT

SET @Max = @@ROWCOUNT

SELECT * FROM @ST_Indexes

SELECT @Max as 'ATTENTION: NonClustered Index listed above will be deleted. Please wait for Query Executed successfully message.'

DECLARE @I INT

SET @I = 1

DECLARE @TblName NVARCHAR(255), @IdxName NVARCHAR(255)

DECLARE @SQL NVARCHAR(MAX)

WHILE @I <= @Max

BEGIN

    SELECT @TblName = Tablo_Adi, @IdxName = Index_Adi FROM @ST_Indexes WHERE SiraNo = @I

    SELECT @SQL = N'DROP INDEX ' +@IdxName+' '+'ON'+' '+@TblName + ' '+'WITH (ONLINE=OFF );'

    EXEC sp_sqlexec @SQL   

    SET @I = @I + 1

END

----------------------------------------------------------------------------------------------------------

4 -  The following query must be copied to the Query Analyzer  and Clustered Indices must be deleted.

Note: Make sure that the correct database is selected in Query Analyzer.

declare @ST_Indexes table

(    SiraNo  int identity(1,1) primary key clustered,

    Tablo_Adi nvarchar(255),

    Index_Adi nvarchar(255))

INSERT INTO @ST_Indexes

(   Tablo_Adi, Index_Adi)

SELECT  OBJ.name AS Tablo_Adi,

        INX.name AS Index_Adi

FROM    sys.indexes AS INX

        JOIN sys.objects AS OBJ ON INX.object_id = OBJ.object_id

WHERE   OBJ.type_desc = 'USER_TABLE'

            AND INX.type_desc = 'CLUSTERED'

DECLARE @Max INT

SET @Max = @@ROWCOUNT

SELECT * FROM @ST_Indexes

SELECT @Max as 'ATTENTION: Clustered Index listed above will be deleted. Please wait for Query executed successfully message'

DECLARE @I INT

SET @I = 1

DECLARE @TblName NVARCHAR(255), @IdxName NVARCHAR(255)

DECLARE @SQL NVARCHAR(MAX)

WHILE @I <= @Max

BEGIN

    SELECT @TblName = Tablo_Adi, @IdxName = Index_Adi FROM @ST_Indexes WHERE SiraNo = @I

    SELECT @SQL = N'ALTER TABLE ' +@TblName+' '+'DROP CONSTRAINT'+' '+@IdxName;

    EXEC sp_sqlexec @SQL   

    SET @I = @I + 1

END

----------------------------------------------------------------------------------------------------------

5 - The following query must be copied to the Query Analyzer  and View tables starting with LV must be deleted.

Note:  Make sure that the correct database is selected in theQuery Analyzer.

DECLARE @ST_LVDROP sysname

DECLARE ST_DROP_LVTBL_CUR INSENSITIVE CURSOR FOR

SELECT NAME FROM sysobjects WHERE name LIKE 'LV_'+'%' AND XTYPE='V'

OPEN ST_DROP_LVTBL_CUR

WHILE 1 = 1

BEGIN

FETCH ST_DROP_LVTBL_CUR INTO @ST_LVDROP

IF @@fetch_status NOT IN ('0')

BREAK

EXEC ('DROP VIEW' +' '+@ST_LVDROP)

END

DEALLOCATE ST_DROP_LVTBL_CUR

----------------------------------------------------------------------------------------------------------

6-  The following query must be copied to the Query Analyzer and the database collation names must be updated as SQL_Latin1_General_CP1254_CI_AS.

Note:   Make sure that the correct database is selected in theQuery Analyzer.

The database name must be written in the Dbname field of the query, and three queries must be run together.    

 ALTER DATABASE Dbname SET SINGLE_USER WITH ROLLBACK IMMEDIATE

--/Database is changed to single user mod.

GO

ALTER DATABASE Dbname COLLATE SQL_Latin1_General_CP1254_CI_AS

--/Database collation name is changed.

GO

ALTER DATABASE Dbname SET MULTI_USER                              

 --/Database is removed from single user mod.

----------------------------------------------------------------------------------------------------------

7- Statistics must be deleted from database before changing collation name.

The following query must be copied to the Query Analyzer and it must be run after changing database name.

-- DELETING STATISTICS FROM DATABASE ---

Declare @TableName nvarchar(250)
Declare @StatsName nvarchar(250)
Declare @TheSQL nvarchar(512)
Declare @DBName nvarchar(200)
----------------------------------------------------------
Set @DBName ='LOGODB' --LOGODB alanına kendi database isminizi yazın...

--- Changing statistics to False status ----
exec('ALTER DATABASE ['+@DBName+';;] SET AUTO_CLOSE OFF WITH NO_WAIT')
exec('ALTER DATABASE ['+@DBName+';;] SET AUTO_CREATE_STATISTICS OFF WITH NO_WAIT')
exec('ALTER DATABASE ['+@DBName+';;] SET AUTO_SHRINK OFF WITH NO_WAIT')
exec('ALTER DATABASE ['+@DBName+';;] SET AUTO_UPDATE_STATISTICS OFF WITH NO_WAIT')

---- Deleting statistics ----
Declare Get_Tables CURSOR FAST_FORWARD FOR
(Select Object_name(object_id) as 'Table Name',name as 'Stats Name'
From sys.stats SS
Where (IndexProperty(object_id, name, 'IsAutoStatistics') = 1 and object_id >255 or user_created = 1) and not
(Select TOP 1 name FROM sys.objects where type_desc 'INTERNAL_TABLE' and object_id = SS.object_id) is null)

Open Get_Tables
FETCH NEXT FROM Get_Tables INTO @TableName,@StatsName
WHILE @@FETCH_STATUS = 0
BEGIN
set @TheSQL = 'DROP STATISTICS ' + QUOTENAME(@TableName) + '.'+ QUOTENAME(@StatsName)
exec (@TheSQL)
FETCH NEXT FROM Get_Tables INTO @TableName,@StatsName
END

Close Get_Tables
DEALLOCATE Get_Tables

 8- The following query must be copied to the Query Analyzer and the collation names of tables and columns must be updated as SQL_Latin1_General_CP1254_CI_AS.

Note:  Make sure that the correct database is selected in theQuery Analyzer.

 declare @ST_ColDeg table

(  SiraNo  int identity(1,1) primary key clustered,

   Tablo_Adi varchar(max),

    Column_Name varchar(max),

      Data_Tipi varchar(max),

      Uzunluk varchar(max))

INSERT INTO @ST_ColDeg

(Tablo_Adi,Column_Name,Data_Tipi,Uzunluk)

SELECT

TABLE_NAME AS Tablo_Adi,

COLUMN_NAME AS Column_Name,

DATA_TYPE AS Data_Tipi,

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 AS Uzunluk

FROM INFORMATION_SCHEMA.COLUMNS

WHERE DATA_TYPE IN ('varchar')

AND TABLE_NAME NOT LIKE ('LV_%')

AND COLLATION_NAME NOT LIKE 'SQL_Latin1_General_CP1254_CI_AS'

DECLARE @Max INT

SET @Max = @@ROWCOUNT

SELECT * FROM @ST_ColDeg

SELECT @Max as 'ATTENTION! Collation name will be changed for the columns listed above. Please wait for Query executed successfully message.'

DECLARE @I INT

SET @I = 1

DECLARE @TblName varchar(max), @ClmnName varchar(max),@DtTp varchar(max),@Uznlk varchar(max)

DECLARE @SQL NVARCHAR(MAX)

WHILE @I <= @Max

BEGIN

    SELECT @TblName= Tablo_Adi,@ClmnName=Column_Name,@DtTp=Data_Tipi,@Uznlk=Uzunluk

 FROM @ST_ColDeg WHERE SiraNo =@I

   SELECT @SQL = N'ALTER TABLE '+@TblName+' ALTER COLUMN'+' '+@ClmnName + ' '+ @DtTp+''+@Uznlk +' '

+ ' '+'COLLATE'+' '+'SQL_Latin1_General_CP1254_CI_AS'+ ' ' +'NULL';

    EXEC sp_sqlexec @SQL

    SET @I = @I + 1

END

----------------------------------------------------------------------------------------------------------

9-  The following query must be copied to the Query Analyzer. Indices,  which are added while creating database for system tables, are created.

Note:  Make sure that the correct database is selected in theQuery Analyzer.

The following queries must be used only when changing collation name over a database that includes company independent tables.

If company database is in use and collation names are changed over this database, move to the next operation step. 

ALTER TABLE [dbo].[L_CAPISIGN] ADD  CONSTRAINT [CAPISIGN_I1] PRIMARY KEY CLUSTERED

([LOGICALREF] ASC)WITH

(PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF,

 IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

---

CREATE UNIQUE NONCLUSTERED INDEX [CDBTMP_I1] ON [dbo].[L_CDBTMP]

(     [MODULE_] ASC,[INFOTYPE] ASC,[OBJID] ASC,[INSTID] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF,

 DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

---

ALTER TABLE [dbo].[L_BRWSSTAT] ADD  CONSTRAINT [BRWSSTAT_I1] PRIMARY KEY CLUSTERED

([LOGICALREF] ASC)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF,

 SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON,

 ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

---

ALTER TABLE [dbo].[L_CAPITERMINAL] ADD  CONSTRAINT [CAPITERMINAL_I1] PRIMARY KEY CLUSTERED

([LOGICALREF] ASC)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF,

 SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  =

 ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

---

ALTER TABLE [dbo].[L_CAPIFIRM] ADD  CONSTRAINT [CAPIFIRM_I1] PRIMARY KEY CLUSTERED

([LOGICALREF] ASC)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF,

 SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

--

ALTER TABLE [dbo].[L_CAPIUSER] ADD  CONSTRAINT [CAPIUSER_I1] PRIMARY KEY CLUSTERED

([LOGICALREF] ASC )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  =

 OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  =

 ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

--

ALTER TABLE [dbo].[L_TSCONT] ADD  CONSTRAINT [TSCONT_I1] PRIMARY KEY CLUSTERED

([LOGICALREF] ASC)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF,

 SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

--

ALTER TABLE [dbo].[L_TSPROPS] ADD  CONSTRAINT [TSPROPS_I1] PRIMARY KEY CLUSTERED

([LOGICALREF] ASC)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF,

 IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

--

ALTER TABLE [dbo].[L_USERCOM] ADD  CONSTRAINT [USERCOM_I1] PRIMARY KEY CLUSTERED

([LOGICALREF] ASC)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF,

 SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

----------------------------------------------------------------------------------------------------------

10-  Check if there is any record with erroneous collation name by running the following query.

Note:  Make sure that the correct database is selected in theQuery Analyzer.

 SELECT * FROM

INFORMATION_SCHEMA.COLUMNS

WHERE DATA_TYPE IN ('varchar')

AND COLLATION_NAME NOT LIKE 'SQL_Latin1_General_CP1254_CI_AS'

 ----------------------------------------------------------------------------------------------------------

11- The following query must be copied to the Query Analyzer and VERS field located in L_CAPISIGN table must be updated.

Note:  Make sure that the correct database is selected in theQuery Analyzer.

 UPDATE L_CAPISIGN SET VERS=1

 ----------------------------------------------------------------------------------------------------------

12-  As shown in the following image, all options listed in the General Table Management window under the Management menu of System Administration must be updated. 

ScreenHunter 04 Jul. 03 15.08

----------------------------------------------------------------------------------------------------------

13-  For each company listed in System Administration > Management > Companies:

The "Version Upgrade for Tables" and "Upgrade Custom Tables" operations must be performed.

Note: Since customization is not available in Go and GoPlus versions, customization update will not be required.

ScreenHunter 07 Jul. 03 15.12

----------------------------------------------------------------------------------------------------------

14-  The "Upgrade Custom Tables" and "Generate Database Scripts" operations must be performed for the periods listed for each company in System Administration > Management > Companies.

Note: Since customization is not available in Go and GoPlus versions, customization update will not be required.

ScreenHunter 09 Jul. 03 15.14

----------------------------------------------------------------------------------------------------------

 15-  After performing all necessary updates in System Administration, the following query is copied to the Query Analyzer and indices under the Database are updated again.

Note: Retake a backup of database before this operation and make sure that the correct database is selected before running the query. (This may take a while.)

EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?')"

After performing all these operations, database collation name will be changed and you can login the application.