SQL SERVER TUNING TARAFINDAN OLUSTURULAN INDEXLERI BULMA

On By semt20In SqlLeave a comment 
select * from(
select i.[name] as index_name,
    substring(column_names, 1, len(column_names)-1) as [columns],
    case when i.[type] = 1 then 'Clustered unique index'
        when i.type = 2 then 'Unique index'
        end as index_type,
    schema_name(t.schema_id) + '.' + t.[name] as table_view, 
    case when t.[type] = 'U' then 'Table'
        when t.[type] = 'V' then 'View'
        end as [object_type],
    case when c.[type] = 'PK' then 'Primary key'
        when c.[type] = 'UQ' then 'Unique constraint'
        end as constraint_type, 
    c.[name] as constraint_name
from sys.objects t
    left outer join sys.indexes i
        on t.object_id = i.object_id
    left outer join sys.key_constraints c
        on i.object_id = c.parent_object_id 
        and i.index_id = c.unique_index_id
   cross apply (select col.[name] + ', '
                    from sys.index_columns ic
                        inner join sys.columns col
                            on ic.object_id = col.object_id
                            and ic.column_id = col.column_id
                    where ic.object_id = t.object_id
                        and ic.index_id = i.index_id
                            order by col.column_id
                            for xml path ('') ) D (column_names)
 
 ) 
AS T WHERE  T.index_name LIKE '_dta%'

--DROP INDEX _dta_index_LG_211_CLCARD_7_118812031__K1_7  ON

=”DROP INDEX “&A2&” ON “&B2

SQL SERVER TEK HUCREDE VIRGULLU DATAYI ROWA CEVIRME

On By semt20In SqlLeave a comment 
create FUNCTION [dbo].[SM_SPLIT]( 
@delimited NVARCHAR(MAX), 
@delimiter NVARCHAR(100) 
) RETURNS @table TABLE (id INT IDENTITY(1,1), [value] NVARCHAR(MAX)) 
AS 
BEGIN 
DECLARE @xml XML 
SET @xml = N'' + REPLACE(@delimited,@delimiter,'') + '' 
INSERT INTO @table([value]) 
SELECT r.value('.','Nvarchar(MAX)') as item 
FROM @xml.nodes('/t') as records(r) 
RETURN 
END 








select * from dbo.SM_SPLIT(REPLACE('0, 111, 121, 131, 141, 211, 221, 231, 241, 251, 261, 271, 281, 212, 222, 232, 242, 252, 262, 272',' ',''),',')  

LOD LOGO OBJECTS DESIGNER

On By semt20In LOGO ERP, LOGO OBJECTSLeave a comment
STRUCTURE – > CUSTENTR1
EVENTS BEFORESAVEE AŞAĞIDAKINI YAPIŞTIR
Sub BeforeSave(proceed as Integer)
      MaxGridLine=MGridLines(“InvoiceGrid”)-1
     for i=0 to MaxGridLine
       GetGridRowHandle(“InvoiceGrid”,i,recH)
       AppTables[9].GetIntFieldR(recH,”LINETYPE”,intLineType)
       AppTables[9].GetIntFieldR(recH,”GLOBTRANS”,intGlobTrans)
       AppTables[9].GetIntFieldR(recH,”PARENTLNREF”,intPARENTLNREF)
       AppTables[9].GetIntFieldR(recH,”TRCODE”,intTRCODE)
        if (intLineType = 1) and (intGlobTrans = 1) and (intPARENTLNREF = 0) and (intTRCODE = 1) then
          warn(“Fiş Genelindeki Promosyonları silmelisiniz”)
          proceed = 0
       end if
     next i
End Sub

 

 
Sub BeforeSave(proceed as Integer)
trcode=0
dontsave=1
      MaxGridLine=MGridLines("InvoiceGrid")-1
     for i=0 to MaxGridLine
       GetGridRowHandle("InvoiceGrid",i,recH)
       AppTables[9].GetIntFieldR(recH,"LINETYPE",intLineType)
       AppTables[9].GetIntFieldR(recH,"GLOBTRANS",intGlobTrans)
       AppTables[9].GetIntFieldR(recH,"PARENTLNREF",intPARENTLNREF)
       AppTables[9].GetIntFieldR(recH,"TRCODE",intTRCODE)
       AppTables[9].GetFltFieldR(recH,"DISCPER",floatDISCPER)
       if   intTRCODE>0 then
         trcode=intTRCODE
       end if
        'if (intLineType = 1) and (intGlobTrans = 1) and (intPARENTLNREF = 0) and (intTRCODE = 1) then
        'warn(floatDISCPER)
        if intGlobTrans = 1 and trcode=1 then

        if intLineType=2 and floatDISCPER>0 then 
        dontsave = 0
        end if

        if intLineType=1 then 
        dontsave = 0
        end if
        
        if dontsave=0 then 
        warn("Fiş Geneline(Çift Çizgi Altına) Promosyon/Indirim Eklenemez")
        proceed=dontsave
        end if
       
       end if
     next i
End Sub
'LINETYPE 0 MALZEME
'LINETYPE 1 PROMOSYON
'LINETYPE 2 INDIRIM

logo sql mail at sp_Send_DBMail

On By semt20In LOGO ERP, LOGO OBJECTSLeave a comment 
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON


/*
EXEC sp_configure 'Database Mail XPs'
go*/
/*management database mailden MAIL_PROFILE isminde profil yarat*/

DECLARE @Delimiter Char(1)
DECLARE @Dosyaadi VARCHAR(249)

SET @Delimiter = CHAR(9) /*Tab char(9) Line feed char(10) Carriage return char(13)*/
SET @Dosyaadi='Sanayi_Güvenli_Stok_Seviye_Kontrol_'+CONVERT(VARCHAR(10),GETDATE(),104)+'-'+ REPLACE( CONVERT(VARCHAR(8), CONVERT(TIME, GETDATE())),':','.')+'.xls';

EXEC MSDB.dbo.sp_Send_DBMail
@profile_name = 'MAIL_PROFILE',
@Recipients='ALICI@MAIL.com',
@Subject='SUBJECT',
@Body='BODY',
@Query='select TOP 10 * from LG_101_ITEMS;',
@Attach_Query_Result_As_File = 1,
@Query_Result_Header = 1,
@Query_Attachment_Filename = @Dosyaadi,
@query_result_no_padding=1,
@query_result_width = 32767,
@Query_Result_Separator = @Delimiter  ,
@execute_query_database = 'DATABASENAME'

 
SET ANSI_NULLS OFF
SET QUOTED_IDENTIFIER OFF  
 

//HTML FORMAT (MAIL_PROFILE Unutma)
DECLARE @HTML VARCHAR(MAX);  

SET @HTML = CAST(DAY(GETDATE()) AS VARCHAR)+'.'+CAST(MONTH(GETDATE()) AS VARCHAR)+'.'+CAST(YEAR(GETDATE()) AS VARCHAR)+' '+
				CAST(DATEPART(HOUR, GETDATE()) AS VARCHAR)+':'+CAST(DATEPART(MINUTE, GETDATE()) AS VARCHAR)+' Tarihli Xx Aktarım Kontrolü
<table border="1">
    <tr>
        <th>TABLO</th>
        <th>BUGUN_XXDEN_LOGOYA_AKTARILAN_SATIR_ADET</th> 
    </tr>' +  
    CAST ( 
    (
        SELECT TOP 20 
            td = TABLO, '', 
            td = BUGUN_XXDEN_LOGOYA_AKTARILAN_SATIR_ADET
        FROM [LOGO].[dbo].[BM_301_XXTRANSACTIONSMAIL]
        FOR XML PATH('tr'), TYPE
    ) AS NVARCHAR(MAX) ) + '
</table>';


-- Envia o e-mail
EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'MAIL_PROFILE', -- sysname
    @recipients = 'xx@gmail.com', -- varchar(max)
    @subject = N'Bilmark XxTransaction Control', -- nvarchar(255)
    @body = @HTML, -- nvarchar(max)
    @body_format = 'html'

//Clear mail queue
DECLARE @GETDATE datetime  
SET @GETDATE = GETDATE();  
EXECUTE msdb.dbo.sysmail_delete_mailitems_sp @sent_before = @GETDATE;  
GO