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

Blog geri dön
20Nis

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

Destek Hizmetleri, Logo Destek

Bu yazımızda veritabanının collation name bilgisinin nasıl değiştirileceğini anlatacağız.

İşlemlerimize başlamadan önce “collation” nedir sorusunun cevabını verelim.

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

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

LOGO programlarında versiyon geçişi yaptıktan sonra 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 tablaların (L_….)  collation namelerinin farklı olmasından ya da farklı firma veritabanı kullanımı olduğu durumlarda ana veritabanı tabloları ile firma veritabanı tablolarının collation name’ lerinin farklı olduğu durumlarda karşılaşılmaktadır.

Collation name hata görüntüsü

Örneğin;

Firma ana veritabanı tablolarındaki collation bilgisi Turkish_CI_AS, firma veritabanı tablolarındaki 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 ana veritabanı içindeki “L_TRDGRP” tablosu ile firma veritabanı içindeki “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ının çö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 önce tablo ve kolonların collation name bilgilerinin hatalı olduğundan emin olunmalıdır. Aşağıdaki sorgu ile hatalı kayıtlar tespit edilebilir. Bu sorgu collation name bilgisi ‘SQL_Latin1_General_CP1254_CI_AS’ dışındaki kayıtları listelemektedir.
Not: Doğru veritabanının seçildiğinden emin olun.

SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE IN ('varchar') AND COLLATION_NAME NOT LIKE 'SQL_Latin1_General_CP1254_CI_AS'

İŞLEM ADIMLARI:

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

SQL Server Management Studio Express veya Query Analyzer açılmalı ve collation name değişikliği yapılacak veritabanı seçilmelidir. (Farklı firma veritabanı kullanımı mevcut ise her firma veritabanı için aynı işlemler yapılmalıdır.)
Not: Bu işlemler Vtyonet.exe üzerinden yapılmamalıdır.

SQL Server Management Studio Express

Aşağıdaki sorgu kopyalanıp NonClustered indexler silinmelidir.

ÖNEMLİ:
Doğru veritabanının seçildiğinden emin olun.

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

Aşağıdaki sorgu kopyalanıp Clustered indexler silinmelidir.

ÖNEMLİ:
Doğru veritabanının seçildiğinden emin olun.

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

Aşağıdaki sorgu kopyalanıp LV ile başlayan view tabloları silinmelidir.

ÖNEMLİ:
Doğru veritabanının seçildiğinden emin olun.

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

Aşağıdaki sorgu kopyalanıp veritabanının collation name’ i SQL_Latin1_General_CP1254_CI_AS olarak güncellenir.
Sorguda geçen “dbname” alanına veritabanının ismi yazılmalı ve 3 sorgu birlikte çalıştırılmalıdır.

ÖNEMLİ:
Doğru veritabanının seçildiğinden emin olun.

ALTER DATABASE dbname SET SINGLE_USER WITH ROLLBACK IMMEDIATE
-- Database single user moda çekilir.
GO

ALTER DATABASE dbname COLLATE SQL_Latin1_General_CP1254_CI_AS
-- Database collation name değişikliği yapılır.
GO

ALTER DATABASE dbname SET MULTI_USER                              
-- Database single user moddan çıkarılır.
GO

Not: Güncel versiyonlarda fonksiyonlar ile ilgili hata alınması durumda ayrıca aşağıdaki sorgu ç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ı.

Collation name değişikliği işleminin yapılabilmesi için değişiklik öncesi veritabanındaki istatistiklerin silinmesi gerekmektedir. Bunun için aşağıdaki sorgu çalıştırılmalıdır.

-- İstatistiklerin kapatılarak silinmesi sorgusu

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

--- İstatistiklerin 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

Aşağıdaki sorgu kopyalanıp tablo ve kolonların collation name’ leri SQL_Latin1_General_CP1254_CI_AS olarak güncellenir.

ÖNEMLİ:
Doğru veritabanının seçildiğinden emin olun.

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 successfully mesajını 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

Aşağıdaki sorgu kopyalanıp sistem tablolarına ait veritabanı oluşturulurken eklenen indexler oluşturulur.
Bu sorgular sadece firma bağımsız tabloların bulunduğu ana veritabanı üzerinde collation name değişikliği yapılıyor ise kullanılmalıdır. Farklı firma veritabanı kullanımı var ve collation name değişikliği bu veritabanı üzerinde yapılıyor ise bir sonraki işlem adımına geçiş yapılmalıdır.

ÖNEMLİ:
Doğru veritabanının seçildiğinden emin olun.

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]

Aşağıdaki sorguyu çalıştırarak collation name bilgisi hatalı olan kayıt olup olmadığı tekrar kontrol edilebilir.

ÖNEMLİ:
Doğru veritabanının seçildiğinden emin olun.

SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE IN ('varchar') AND COLLATION_NAME NOT LIKE 'SQL_Latin1_General_CP1254_CI_AS'

Aşağıdaki sorgu kopyalanıp L_CAPISIGN tablosundaki VERS alanı güncellenmelidir.

ÖNEMLİ:
Doğru veritabanının seçildiğinden emin olun.

UPDATE L_CAPISIGN SET VERS=1

Aşağıdaki ekran görüntüsünde olduğu gibi Sistem İşletmenine giriş yaparak Yönetim \ Genel Tablo Yönetimi altındaki seçeneklerin hepsi için güncelleme yapılmalıdır.

Genel Tablo Yönetimi

Sistem işletmeninde Yönetim \ Firmalar bölümünde her firma için “Tabloları sürüme göre güncelle” ve “Uyarlama tablolarını güncelle” işlemi yapılmalıdır.

Not : Go, GoPlus, GO 3 setlerinde uyarlama seçeneği olmadığı için uyarlama güncellemesine gerek yoktur.

Firma güncelleme işlemleri

Sistem işletmeninde Yönetim \ Firmalar bölümünde her firma altındaki çalışma dönemleri için “Veritabanı Araçlarını Oluştur” ve “Uyarlama tablolarını güncelle” işlemi yapılmalıdır.

Not : Go, GoPlus, GO 3 setlerinde uyarlama seçeneği olmadığı için uyarlama güncellemesine gerek yoktur.

Çalışma dönemleri güncelleme ekranı

Sistem işletmeninde gerekli güncellemeler yapıldıktan sonra aşağıdaki kopyalanıp veritabanı altındaki indexlerin tekrar güncellenmesi sağlanabilir.
Not: Bu işlem öncesinde tekrar veritabanı yedeği alın ve sorgu çalıştırılmadan önce doğru veritabanı 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 veritabanı collation name değişikliği tamamlanmış olacaktır ve artık programa giriş yapabilirsiniz.

Bu gönderiyi paylaş

Blog geri dön