Server: Msg 8116, Level 16, State 1, Line 1 Argument data type text is invalid for argument 1 of replace function.
SQL Server 'ın böyle bir hata döndürmesinin nedeni TEXT ya da NTEXT veri tipine sahip sütunlarda normal REPLACE komutunu işletemiyor olmamız. Çünkü SQL Server bu veri tipine sahip kolonlarda veriyi değil verinin saklandığı yeri gösteren 16 byte 'lık bir pointer 'ı saklar. Böyle bir pointer üzerinde de REPLACE komutunu çalıştıramayız elbette...
O halde NTEXT ya da TEXT (ya da IMAGE) veri tiplerine sahip sütunlarda REPLACE işlemini nasıl yapabiliriz ? Güzel soru...
Bu sorunun cevabı olabilecek hazır bir T-SQL komutu var mı peki ? Var; ama pek hazır sayılmaz
Ona biraz çeki düzen vermemiz gerekecek öncelikle. Unutmadan sihirli komutumuzun adı UPDATETEXT...
Aşağıdaki T-SQL scriptini Query Analyzer ya da Management Studio ile çalıştırmanız yeterli olacaktır gerekli REPLACE işlemini gerçekleştirmek için. Bu T-SQL hem SQL Server 2000 hem de SQL Server 2005 üzerinde çalıştırılmış ve test edilmiştir. Gönül rahatlığı ile kullanabilirsiniz. Scriptle ilgili dikkat etmeniz gereken bir nokta var; scripti hem TEXT hem de NTEXT sütunlarda kullanabilirsiniz. Aşağıdaki script NTEXT sütunlar için çalışacak şekilde düzenlenmiş, scripti TEXT sütunları için uygun hale getirmek için yorumlu kısımlarda belirtilen bölümleri scriptten kaldırın.
USE TempDB; GO SET NOCOUNT ON; CREATE TABLE dbo.data ( DataID INT PRIMARY KEY, txt NTEXT -- change to TEXT ); GO INSERT dbo.data SELECT 1, N'ahmet ben test felan filan' UNION ALL SELECT 2, N'ah****hmet ali' UNION ALL SELECT 3, N'meltem hasan ahmet vs' UNION ALL SELECT 4, N'tayfun'; DECLARE @TextPointer BINARY(16), @TextIndex INT, @oldString NVARCHAR(32), -- VARCHAR olarak değiştirin @newString NVARCHAR(32), -- VARCHAR olarak değiştirin @lenOldString INT, @currentDataID INT; SET @oldString = N'ahmet'; -- N i kaldırın SET @newString = N'mehmet'; -- N i kaldırın IF CHARINDEX(@oldString, @newString) > 0 BEGIN PRINT 'Sonsuz donguye girmemek icin cikiyoruz.'; END ELSE BEGIN SELECT 'Islemden once:'; SELECT DataID, txt FROM data; SET @lenOldString = DATALENGTH(@oldString)/2; -- /2 yi kaldırın DECLARE irows CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY FOR SELECT DataID FROM dbo.data WHERE PATINDEX('%'+@oldString+'%', txt) > 0; OPEN irows; FETCH NEXT FROM irows INTO @currentDataID; WHILE (@@FETCH_STATUS = 0) BEGIN SELECT @TextPointer = TEXTPTR(txt), @TextIndex = PATINDEX('%'+@oldString+'%', txt) FROM dbo.data WHERE DataID = @currentDataID; WHILE ( SELECT PATINDEX('%'+@oldString+'%', txt) FROM dbo.data WHERE DataID = @currentDataID ) > 0 BEGIN SELECT @TextIndex = PATINDEX('%'+@oldString+'%', txt)-1 FROM dbo.data WHERE DataID = @currentDataID; UPDATETEXT dbo.data.txt @TextPointer @TextIndex @lenOldString @newString; END FETCH NEXT FROM irows INTO @currentDataID; END CLOSE irows; DEALLOCATE irows; SELECT 'Islemden sonra:'; SELECT DataID, txt FROM data; END DROP TABLE dbo.data;
(UPDATETEXT komutu normal bir UPDATE işleminin aksine herhangi bir trigger 'ın çalıştırılmasını tetiklemeyecektir. Bu nedenle UPDATETEXT kullandığınız bir yerde trigger 'ları pasif hale getirmek için kafanızı yormanıza gerek yoktur. Ancak UPDATETEXT ile yapılan değişikliklerin kayıt altında tutulmayacağı da unuıtulmamalıdır.)
SQL Server 7.0
SQL Server 7.0 da çalışırken yukarıdaki komut bloğunu çalıştırdığınızda aşağıdaki gibi bir hata alabilirsiniz.
Server: Msg 7130, Level 16, State 1, Line 47 UpdateText WITH NO LOG is not valid at this time. Use sp_dboption
to set the 'select into/bulkcopy' option on for database 'pubs'.
Bu hatanın üstesinden gelebilmeniz için seçtiğiniz veri tabanının BULKCOPY seçeneğini "true" olarak atamanız gerekmektedir. Eğer bu özelliği "true" olarak atarsanız SQL Server aşağıdaki işlemleri transaction log 'a kaydetmeyecektir.
EXEC master.dbo.sp_dboption 'pubs', 'BULKCOPY', 'true'
SQL Server 'ın böyle bir hata döndürmesinin nedeni TEXT ya da NTEXT veri tipine sahip sütunlarda normal REPLACE komutunu işletemiyor olmamız. Çünkü SQL Server bu veri tipine sahip kolonlarda veriyi değil verinin saklandığı yeri gösteren 16 byte 'lık bir pointer 'ı saklar. Böyle bir pointer üzerinde de REPLACE komutunu çalıştıramayız elbette...
O halde NTEXT ya da TEXT (ya da IMAGE) veri tiplerine sahip sütunlarda REPLACE işlemini nasıl yapabiliriz ? Güzel soru...
Bu sorunun cevabı olabilecek hazır bir T-SQL komutu var mı peki ? Var; ama pek hazır sayılmaz
Ona biraz çeki düzen vermemiz gerekecek öncelikle. Unutmadan sihirli komutumuzun adı UPDATETEXT...Aşağıdaki T-SQL scriptini Query Analyzer ya da Management Studio ile çalıştırmanız yeterli olacaktır gerekli REPLACE işlemini gerçekleştirmek için. Bu T-SQL hem SQL Server 2000 hem de SQL Server 2005 üzerinde çalıştırılmış ve test edilmiştir. Gönül rahatlığı ile kullanabilirsiniz. Scriptle ilgili dikkat etmeniz gereken bir nokta var; scripti hem TEXT hem de NTEXT sütunlarda kullanabilirsiniz. Aşağıdaki script NTEXT sütunlar için çalışacak şekilde düzenlenmiş, scripti TEXT sütunları için uygun hale getirmek için yorumlu kısımlarda belirtilen bölümleri scriptten kaldırın.
USE TempDB; GO SET NOCOUNT ON; CREATE TABLE dbo.data ( DataID INT PRIMARY KEY, txt NTEXT -- change to TEXT ); GO INSERT dbo.data SELECT 1, N'ahmet ben test felan filan' UNION ALL SELECT 2, N'ah****hmet ali' UNION ALL SELECT 3, N'meltem hasan ahmet vs' UNION ALL SELECT 4, N'tayfun'; DECLARE @TextPointer BINARY(16), @TextIndex INT, @oldString NVARCHAR(32), -- VARCHAR olarak değiştirin @newString NVARCHAR(32), -- VARCHAR olarak değiştirin @lenOldString INT, @currentDataID INT; SET @oldString = N'ahmet'; -- N i kaldırın SET @newString = N'mehmet'; -- N i kaldırın IF CHARINDEX(@oldString, @newString) > 0 BEGIN PRINT 'Sonsuz donguye girmemek icin cikiyoruz.'; END ELSE BEGIN SELECT 'Islemden once:'; SELECT DataID, txt FROM data; SET @lenOldString = DATALENGTH(@oldString)/2; -- /2 yi kaldırın DECLARE irows CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY FOR SELECT DataID FROM dbo.data WHERE PATINDEX('%'+@oldString+'%', txt) > 0; OPEN irows; FETCH NEXT FROM irows INTO @currentDataID; WHILE (@@FETCH_STATUS = 0) BEGIN SELECT @TextPointer = TEXTPTR(txt), @TextIndex = PATINDEX('%'+@oldString+'%', txt) FROM dbo.data WHERE DataID = @currentDataID; WHILE ( SELECT PATINDEX('%'+@oldString+'%', txt) FROM dbo.data WHERE DataID = @currentDataID ) > 0 BEGIN SELECT @TextIndex = PATINDEX('%'+@oldString+'%', txt)-1 FROM dbo.data WHERE DataID = @currentDataID; UPDATETEXT dbo.data.txt @TextPointer @TextIndex @lenOldString @newString; END FETCH NEXT FROM irows INTO @currentDataID; END CLOSE irows; DEALLOCATE irows; SELECT 'Islemden sonra:'; SELECT DataID, txt FROM data; END DROP TABLE dbo.data;
(UPDATETEXT komutu normal bir UPDATE işleminin aksine herhangi bir trigger 'ın çalıştırılmasını tetiklemeyecektir. Bu nedenle UPDATETEXT kullandığınız bir yerde trigger 'ları pasif hale getirmek için kafanızı yormanıza gerek yoktur. Ancak UPDATETEXT ile yapılan değişikliklerin kayıt altında tutulmayacağı da unuıtulmamalıdır.)
SQL Server 7.0
SQL Server 7.0 da çalışırken yukarıdaki komut bloğunu çalıştırdığınızda aşağıdaki gibi bir hata alabilirsiniz.
Server: Msg 7130, Level 16, State 1, Line 47 UpdateText WITH NO LOG is not valid at this time. Use sp_dboption
to set the 'select into/bulkcopy' option on for database 'pubs'.
Bu hatanın üstesinden gelebilmeniz için seçtiğiniz veri tabanının BULKCOPY seçeneğini "true" olarak atamanız gerekmektedir. Eğer bu özelliği "true" olarak atarsanız SQL Server aşağıdaki işlemleri transaction log 'a kaydetmeyecektir.
- "Bulk load" işlemleri
- "SELECT INTO", WRITETEXT ve UPDATETEXT işlemleri
EXEC master.dbo.sp_dboption 'pubs', 'BULKCOPY', 'true'
