SQL 2008 Collation Name Değişikliği İşlemleri

Collation, SQL Server da character set anlamına gelmektedir. Eşitliklerde, order işleminde, büyük küçük harf ayrımında karakterlerin hangi mantıkta kullanılacağını belirler. Genelde Türkçe databaselerde kullanılan Turkish_CI_AS yada SQL_Latin1_General_CP1254_CI_AS collationındaki CI ifadesi Case Insensitive(Büyük küçük harf ayrımı olmasın) anlamına gelmektedir.

Özellikle SQL 2000'de collation name olarak Turkish_CI_AS seçilmekteydi, fakat SQL 2005 ve SQL 2008'e geçiş sonrasında daha çok SQL_Latin1_General_CP1254_CI_AS kullanılmaya başlanmıştır.

Firma güncelleme sırasında alınan (Cannot resolve the collation conflict between "SQL_latin1_General_CP1254_CI_AS" and "Turkish_CI_AS" in the equal to operation) hatası güncellenmek istenen firmaya ait tablolar (LG_XXX_XX_) ile firma bağımsız tabloların (L_) collation namelerinin farklı olmasından yada farklı firma database kullanımı olduğunda ve master database ile firma database tablolarının collation nameleri farklı olduğu durumlarda karşılaşılmaktadır.

Örneğin;

Master database tablolarındaki collation bilgisi Turkis_CI_AS, firma database collation bilgisi ise SQL_Latin1_General_CP1254_CI_AS olduğu durumda firma güncellemesi sırasında LV_XXX_XX_TRDGRP view sorgusunda geçen master databese altındaki L_TRADGRP tablosu ile firma database altındaki LG_XXX_XX_CLFLINE tablosu eşitlenirken, eşitlenmek istenen varchar tipindeki alanların collation namelerinin farklı olmasından kaynaklanıyor olabilir.

L_TRADGRP.GCODE = LG_XXX_XX_CLFLINE.TRADINGGRP

Yaşanan collation name sorunları çözümü için aşağıdaki adımlar sırası ile izlendiğinde database ve tabloların collation name bilgileri (Uyarlama tabloları da dahil) eşitlenmiş olacaktır.

ÖNEMLİ:

İşlem adımlarına başlamadan tablo ve kolonların collation name bilgilerinin hatalı olduğundan emin olunmalıdır. Aşağıdaki sorgu ile hatalı kayıtlar tespit edilebilir. Aşağıdaki sorgu collation name bilgisi 'SQL_Latin1_General_CP1254_CI_AS' dışındaki kayıtları listelemektedir.

--Tablo ve Kolonların Collation Bilgisi

Select TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE, CHARACTER_SET_NAME, COLLATION_NAME 

From INFORMATION_SCHEMA.COLUMNS

Where DATA_TYPE IN ('varchar')

and COLLATION_NAME NOT LIKE 'SQL_Latin1_General_CP1254_CI_AS'

--veya

SELECT t.name as TableName, c.name as ColumnName, collation_name  

FROM sys.columns c  

inner join sys.tables t on c.object_id = t.object_id

-- Server Collation Bilgisi

Select CONVERT (varchar(256), SERVERPROPERTY('collation')) AS ServerCollation

--veya

EXECUTE sp_helpsort

-- Database Collation Bilgisi

Select name, collation_name from sys.databases where name='LOGODB'

--veya

Select convert(varchar(256), DATABASEPROPERTYEX('LOGODB','collation')) AS DatabaseCollation

1. Programı kullanan kullanıcılar sistemden çıkartılmalı ve database yedeği alınmalıdır.

2. SQL Query Analizer açılmalı ve collation name değişikliği yapılacak database seçilmelidir. Farklı firma database kullanımı var ise her firma databasesi için aynı işlemler yapılmalıdır.

Not: Query Analizer'da Doğru database seçildiğinden emin olun.

3. NonClustered İndexler Silinmelidir.

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 'DIKKAT:Yukarida Listelenen NonClustered Index silinecektir. Query executed successffully mesajini bekleyin'

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. Clustered İndexler Silinmelidir.

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 'DIKKAT:Yukarida Listelenen Clustered Index silinecektir. Query executed successffully mesajini bekleyin'

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. LV ile başlayan View tabloları silinmelidir.

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. Database collation nameleri SQL_Latin1_General_CP1254_CI_AS olarak güncellenir.

ALTER DATABASE LOGODB SET SINGLE_USER WITH ROLLBACK IMMEDIATE

-- Database single user moda çekilir.

GO

ALTER DATABASE LOGODB COLLATE SQL_Latin1_General_CP1254_CI_AS

-- Database collation name değişikliği yapılır.

GO

ALTER DATABASE LOGODB SET MULTI_USER                              

-- Database single user moddan cikarilir.

Not: Güncel versiyonlarda fonksiyonlar ile ilgili hata alınması durumda, aşağıdaki sorgu ayrıca çalıştırılmalıdır;

DECLARE @BKPDROP sysname

DECLARE DROP_BKPTBL_CUR INSENSITIVE CURSOR FOR

SELECT NAME FROM SYSOBJECTS WHERE NAME LIKE '%GET%COEF%' AND XTYPE='FN' AND NAME LIKE '%002%'

OPEN DROP_BKPTBL_CUR

WHILE 1 = 1

BEGIN

FETCH DROP_BKPTBL_CUR INTO @BKPDROP

IF @@fetch_status = 0

BREAK

EXEC ('DROP FUNCTION' +' '+@BKPDROP)

END

DEALLOCATE DROP_BKPTBL_CUR

-- NOT:  %002%  -- Alanına firma numarası yazılmalı.

7. İstatisliklerin silinmesi gerekmektedir.

-- Databasedeki İstatistiklerin Silinmesi

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

-- İstatisliklerin False duruma getirilmesi

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')

-- İstatisliklerin silinmesi

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. Tablo ve kolonların collation nameleri SQL_Latin1_General_CP1254_CI_AS olarak güncellenir.

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 'DIKKAT! Yukarida listelenen kolonlar için collation name degisikligi yapilacaktir,Query executed successffully mesajini bekleyin'

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. Sistem tablolarına ait database oluşturulurken eklenen indexler oluşturulur.

Aşağıdaki sorgular sadece firma bağımsız tabloların bulunduğu ana database üzerinde collation name değişikliği yapılıyor ise kullanılmalıdır. Firma database kullanımı var ve collation name değişikliği bu database üzerinde yapılıyor ise bir sonraki işlem adımına geçiş yapılmalıdır.

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. Collation name bilgisi hatalı olan kayıt var mı?

SELECT TABLE_CATALOG,TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,DATA_TYPE,CHARACTER_SET_NAME,COLLATION_NAME 

FROM INFORMATION_SCHEMA.COLUMNS

WHERE DATA_TYPE IN ('varchar')

AND COLLATION_NAME NOT LIKE 'SQL_Latin1_General_CP1254_CI_AS'

11. L_CAPISIGN tablosundaki VERS alanı güncellenmeli.

UPDATE L_CAPISIGN SET VERS=1

12-Aşağıdaki ekran görüntüsünde olduğu gibi SYS'ye giriş yaparak Yönetim / Genel Tablo Yönetimi altındaki seçenekler in hepsi için güncellenme yapılmalıdır.

13- SYS'de Yönetim / Firmalar bölümünde her firma için;

Tabloları sürüme göre güncelleme ve Uyarlama tablolarını güncelleme işlemi yapılmalıdır.

Not: Go setlerinde uyarlama seçeneği olmadığı için uyarlama güncellemesine gerek bulunmamaktadır.

14- SYS'de Yönetim / Firmalar bölümünde, Her firma altındaki dönemler için Veritabanı Araçlarını Oluştur ve Uyarlama Tablolarını Güncelleme yapılmalıdır.

Not: Go setlerinde uyarlama seçeneği olmadığı için uyarlama güncellemesine gerek bulunmamaktadır.

15. Database altındaki Indexler tekrar güncellenir.

Not: Bu işlem öncesinde tekrar database yedeği alın ve sorgu çalıştırılmadan önce Doğru database seçildiğinden emin olun. (Bu işlem uzun sürebilir.)

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

Bütün bu işlemler sonrasında database collation name değişikliği tamamlanmış olacaktır ve artık programa giriş yapabilirsiniz.