Sql 2005 Ve SQL 2008 Collation Name Değişikliği işlemleri
Bu işleme başlamadan önce collation nedir sorusunun cevabını verelim.
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 tablaları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.
Not: Query Analizer'da Doğru database 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 adımları;
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: Bu işlemler sadece Query Analizer üzerinde yapılabilir. Vtyonet.exe üzerinden bu işlemler yapılmamalı.
3-Aşağıdaki sorgu Query Analizer'a kopyalanıp NonClustered İndexler Silinmelidir.
Not: Query Analizer'da Doğru database seçildiğinden ve yedekler alındığından 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
4-Aşağıdaki sorgu Query Analizer'a kopyalanıp Clustered İndexler Silinmelidir.
Not: Query Analizer'da Doğru database 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
5-Aşağıdaki sorgu Query Analizer'a kopyalanıp LV ile başlayan View tabloları silinmelidir.
Not: Query Analizer'da Doğru database 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
6-Aşağıdaki sorgu Query Analizer'a kopyalanıp database collation nameleri SQL_Latin1_General_CP1254_CI_AS olarak güncellenir.
Not: Query Analizer'da Doğru database seçildiğinden emin olun.
Sorguda geçen Dbname alanına database ismi yazılmalı ve 3 sorgu birlikte çalıştırılmalıdır.
ALTER DATABASE Dbname SET SINGLE_USER WITH ROLLBACK IMMEDIATE
--/Database single user moda cekilir.
GO
ALTER DATABASE Dbname COLLATE SQL_Latin1_General_CP1254_CI_AS
--/Database collation name degisikligi yapilir.
GO
ALTER DATABASE Dbname 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-Collatin name değişikliği işleminin yapılabilmesi için Collation name değişikliği öncesi database'deki istatisliklerin silinmesi gerekmektedir.
Aşağıdaki sorgu Query Analizer'a kopyalanıp database ismi değiştirildikten sonra çalıştırılmalıdır.
– DATABASE'DEKİ İSTATİSLİKLERİN SİLİNMESİ —
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 StatsName WHILE @@FETCH_STATUS = 0
BEGIN
set @TheSQL = 'DROP STATISTICS ' + QUOTENAME(@TableName) + '.'+ QUOTENAME(@StatsName)
exec (@TheSQL)
FETCH NEXT FROM Get_Tables INTO StatsName END
Close Get_Tables DEALLOCATE Get_Tables
8-Aşağıdaki sorgu Query Analizer'a kopyalanıp tablo ve kolonların collation nameleri SQL_Latin1_General_CP1254_CI_AS olarak güncellenir.
Not: Query Analizer'da Doğru database 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 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-Aşağıdaki sorgu Query Analizer'a kopyalanıp sistem tablolarına ait database oluşturulurken eklenen indexler oluşturulur.
Not: Query Analizer'da Doğru database seçildiğinden emin olun.
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-Aşağıdaki sorguyu çalıştırarak collation name bilgisi hatalı olan kayıt varmı tekrar kontrol edilebilir.
Not: Query Analizer'da Doğru database 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'
11-Aşağıdaki sorgu Query Analizer'a kopyalanıp L_CAPISIGN tablosundaki VERS alanı güncellenmeli.
Not: Query Analizer'da Doğru database seçildiğinden emin olun.
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ıkdaki seçenekler in hepsi için güncellenme yapılmalıdır.
13- SYS'yede 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,GoPlus setlerinde uyarlama seçeneği olmadığı için uyarlama güncellemesine gerek bulunmamaktadır.
14- SYS'yede 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,GoPlus setlerinde uyarlama seçeneği olmadığı için uyarlama güncellemesine gerek bulunmamaktadır.
15- SYS'de gerekli güncellemeler yapıldıktan sonra aşağıdaki sorgu Query Analizer'a kopyalanıp Database altındaki Indexlerin tekrar güncellenmesi sağlanabilir.
Not: Bu işlem öncesinte 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.