NOT: EDIT PENCERESINDE SORGUYU NORMAL SEKILDE GOREBILIRSIN --Bu procedurler olusturulan text dosyasinin boyutunu knotrol etmektedirler.b deki stored procedure da olusturmak gerekmektedir. --a) USE [ANKA] GO /****** Object: StoredProcedure [dbo].[DBA_TIBTOTAL_file_control] Script Date: 10/18/2011 15:31:34 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[DBA_TIBTOTAL_file_control] AS
begin create table #rawoutput (txt varchar(255))
create table #rawoutput2cell ( txt0 varchar(100), txt1 varchar(100), txt2 varchar(100), txt3 varchar(100), txt4 varchar(100), txt5 varchar(100) ) --truncate table #rawoutput insert #rawoutput exec xp_cmdshell 'dir D:\TIBTOTAL\TIB_FARK_ARSIV\*.abn' delete from #rawoutput where txt not like '%_%_%.%' or txt is null
declare @txt varchar(max) declare cur_rawoutput cursor for select * from #rawoutput where txt like '%DOGANTELEKOM_ABONE%' open cur_rawoutput fetch next from cur_rawoutput into @txt while @@FETCH_STATUS = 0 begin insert into #rawoutput2cell exec dba_texttocol @txt,' ' --print @txt fetch next from cur_rawoutput into @txt end close cur_rawoutput deallocate cur_rawoutput DECLARE @tableHTML NVARCHAR(MAX) ; SET @tableHTML = N'
TIBTOTAL FILE CONTROL
' +N'
' +N'
Date
' +N'
+Time
' +N'
Period
' +N'
Size
' +N'
File Name
' + CAST ( ( select td = txt1, '', td = txt2, '', td = txt3, '', td = txt4, '', td = txt5, '' from #rawoutput2cell where convert(bigint, replace(txt4, ',','')) < 200000000 FOR XML PATH('tr'), TYPE ) AS NVARCHAR(MAX) ) + N'
'+ N' ' + N'
' + N'
D:\TIBTOTAL\TIB_FARK_ARSIV dizini altında 200 MB''dan az olan file''lar listelenmiştir!!!
'; select top 1 * from #rawoutput2cell where convert(bigint, replace(txt4, ',','')) < 200000000 if @@rowcount>0 begin --query sonucunu mail atan bölüm EXEC msdb.dbo.sp_send_dbmail @recipients='[email protected];[email protected]', @subject = 'TIBTOTAL File Size Problem', @body = @tableHTML, @body_format = 'HTML'; end drop table #rawoutput2cell drop table #rawoutput end
