Tag: Sql

Sentez Live Dev – Sql Queryden Hareketlerin Silinmesi

On By semt20In sentez, SqlLeave a comment
Aşağıdaki sorgu liveda (peşin satışsız,ncrsız) kartlar dışında hareketleri siler:(malzeme fişleri ,irsaliyeler,faturalar,teklifler,sipariş,muhasebe fişi,banka çek fişleri).
Sildikten sonra shrink yapılmalı aşağıda nasıl yapıldığı anlatılmıştır.Yaklaşık 14 gblık datayı 700mba düşürdü.

 

 

 

delete Erp_InventoryReceiptItem
update Erp_Invoice set InventoryReceiptId = null
update Erp_InventoryReceiptAttachment set  InventoryReceiptId = null
delete Erp_InventoryReceiptAttachment
delete Erp_InvoiceAttachment
delete Erp_WorkOrderProduction
delete Erp_InventoryReceipt
delete Erp_Invoice
delete Erp_BankAccountTotal
delete Erp_BankCredit
delete Erp_BankReceiptItem
update Erp_ChequeReceipt set BankReceiptId=null
delete Erp_ChequeReceiptItem
delete Erp_ChequeReceiptAttachment
delete Erp_ChequeReceipt
delete Erp_BankReceiptAttachment
delete Erp_BankReceipt
delete Erp_CashTotalItem
delete Erp_CashTotal
delete Erp_Cheque
delete Erp_OrderReceiptItem
delete Erp_OrderReceiptAttachment
delete Erp_OrderReceipt
delete Erp_ContractItem
delete Erp_ContractAttachment
delete Erp_Contract
delete Erp_CurrentAccountReceiptItem
delete Erp_CurrentAccountReceiptAttachment
delete Erp_CurrentAccountReceipt
delete Erp_CurrentAccountTotal
delete Erp_QuotationReceiptItem
delete Erp_QuotationReceiptAttachment
delete Erp_QuotationReceipt
delete Erp_DemandReceiptItem
delete Erp_DemandReceiptAttachment
delete Erp_DemandReceipt
delete Erp_WorkOrderItem
delete Erp_WorkOrderAttachment
delete Erp_WorkOrderExplanation
delete Erp_WorkOrder
delete Erp_GLReceiptItem
delete Erp_GLReceipt
Delete Erp_InventoryTotal
delete Erp_ReceiptPaymentItem
delete Meta_ForexRate
delete Erp_ServiceTotal
Delete Erp_GLAccountTotal
delete Erp_BankAccountTotal
truncate table Log_Transaction

shrink işlemi:
USE LiveHareketler;
GO
ALTER DATABASE LiveHareketler
SET RECOVERY SIMPLE;
GO

--Datadakı log dosyasını shrınk yap yani database sağ tıkla task shrink files log->reorganize page 0 ve tamam ve sonrasındada yine databasee sağ tık task shrink database ok

ALTER DATABASE LiveHareketler
SET RECOVERY FULL;
GO

Sql Server Shrink Database and Log

On By semt20In SqlLeave a comment 


--log dosyasını bul     
 SELECT name FROM sys.master_files WHERE type_desc = 'LOG' and physical_name like '%Jam%'




        ALTER DATABASE DevJamieS
        SET RECOVERY SIMPLE
        GO
        DBCC SHRINKFILE (DevJamieS_log, 1)
        GO
        ALTER DATABASE DevJamieS
        SET RECOVERY FULL

------------

USE LKSDB;
GO
ALTER DATABASE LKSDB
SET RECOVERY SIMPLE;
GO

--datadakı log dosyasını shrınk yap

ALTER DATABASE LKSDB
SET RECOVERY FULL;
GO

EXEC sp_msforeachdb ' Declare @logname varchar(500) = '''';

IF ''?'' not in (''tempdb'',''master'',''msdb'',''model'',''Reportserver'',''ReportserverTempDB'') 
begin 
 
	set @logname = (Select name From [?].Sys.database_files where  type=1) 

	EXEC(''Use [?];
	ALTER DATABASE ?
	SET RECOVERY SIMPLE; 
		DBCC SHRINKFILE (['' + @logname + ''] ,1)
		 
		ALTER DATABASE ?
		SET RECOVERY FULL
		'')  
end 
'

Büyük tablodan sqlden verileri silme

On By semt20In SqlLeave a comment 
/*bu etkili alttakiler deil fazla*/
declare @counter int
declare @numOfRecords int
declare @batchsize int




set @numOfRecords = (SELECT COUNT(*) AS NumberOfRecords  FROM  Erp_InventoryReceiptItem  with(nolock)  where ParentItemId is not null)
set @counter = 0
set @batchsize = 2500

set rowcount @batchsize
while @counter < (@numOfRecords/@batchsize) +1
begin
set @counter = @counter + 1
Update Erp_InventoryReceiptItem set ParentItemId = null;
end
set rowcount 0
----------------------------------------------
/*alttakiler:*/

USE LIVE_UNIMAR_YENI
GO

-- Set to simple mode
ALTER DATABASE LIVE_UNIMAR_YENI SET RECOVERY SIMPLE;
GO

-- Get count of records
SELECT COUNT(*) AS Total FROM Erp_InventoryReceiptItem
GO

-- Delete in batches
DECLARE @VAR_ROWS INT = 1;
WHILE (@VAR_ROWS > 0)
BEGIN
    DELETE TOP (100000) FROM Erp_InventoryReceiptItem
    SET @VAR_ROWS = @@ROWCOUNT;
    CHECKPOINT;
END;
GO

-- Set to full mode
ALTER DATABASE LIVE_UNIMAR_YENI SET RECOVERY FULL;
GO

SQL SERVER PIVOT TABLE SINIRSIZ COLUMN

On By semt20In LOGO ERP, LOGO OBJECTSLeave a comment 
DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);




select @cols = STUFF((SELECT distinct ',' + QUOTENAME(ADI)
            FROM ZV_LOGO_MALZEME  GROUP BY ADI
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)')
        ,1,1,'')

set @query = 'SELECT KODU,' + @cols + ' from
            (
                select KODU,ADI
                from ZV_LOGO_MALZEME
           ) x
            pivot
            (
                COUNT(ADI)
                for ADI in (' + @cols + ')
            ) p
           '

execute(@query)

--------------

İKİ FARKLI TABLODAN
DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);
 
select @cols = STUFF((SELECT distinct ',' + QUOTENAME(DEFINITION_)
            FROM LG_SLSMAN  GROUP BY DEFINITION_
           FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')
 
set @query = 'SELECT STGRPCODE,' + @cols + ' from
            (
                select LG_001_ITEMS.STGRPCODE,DEFINITION_
                from LG_SLSMAN
CROSS JOIN  LG_001_ITEMS  
 
           ) x
            pivot
            (
                MIN(DEFINITION_)
                for DEFINITION_ in (' + @cols + ')
            ) p
           '
 
execute(@query)

sql server kill and rename database

On By semt20In SqlLeave a comment 
--TEST DATABASE AKTİF ET
ALTER DATABASE LKSDB
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
sp_rename 'LKSDB', 'LKSDBORJ' ,'DATABASE';
GO
ALTER DATABASE LKSDBORJ
SET MULTI_USER
GO




ALTER DATABASE LKSDBTEST
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
sp_rename 'LKSDBTEST', 'LKSDB' ,'DATABASE';
GO
ALTER DATABASE LKSDB
SET MULTI_USER
GO

--ORJİNAL DB AKTİF ET
ALTER DATABASE LKSDB
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
sp_rename 'LKSDB', 'LKSDBTEST' ,'DATABASE';
GO
ALTER DATABASE LKSDBTEST
SET MULTI_USER
GO

ALTER DATABASE LKSDBORJ
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
sp_rename 'LKSDBORJ', 'LKSDB' ,'DATABASE';
GO
ALTER DATABASE LKSDB
SET MULTI_USER
GO

SQL 2014TEN 2008E VERİTABANI KOPYALA

On By semt20In SqlLeave a comment
sql server 2014’ten 2008e database kopyalama

önce sql server 2014’ten isdenilen databasee sağ tıklanıp
task
generate script
bütün tablolar
advanceddan schema and table ve serve version 2008 yapılır

next next finish yapılır
nereye kaydedildiyse o script usbye kopyalanır cmdyi yönetici olarak açıp örnek olarak aşağıdaki komut yazilir
sqlcmd -S DESKTOP-VFSMD2M\SQL2008 -i “d:/c.sql”

not sql scriptinin içinden veritabanı adını deiştir alter malter yazan

visual c# gridview with parameter

On By semt20In C SHARP, Devexpress, visual studioLeave a comment 
 private void button1_Click_3(object sender, EventArgs e)
        {
            string connectionString = null;
            SqlConnection cnn;
            connectionString = "Server = " + AYARLAR.txtServerc + "; Database = " + AYARLAR.txtVeritabanic + "; User Id = " + AYARLAR.txtKullanicic + "; Password = " + AYARLAR.txtParolac;




            FileInfo file = new FileInfo(Application.StartupPath + "\\SQL.txt");
            string script = file.OpenText().ReadToEnd();

            cnn = new SqlConnection(connectionString);

            SqlDataAdapter da = new SqlDataAdapter(script, cnn);
            da.SelectCommand.Parameters.AddWithValue("@tarih1", dateEdit1.Text);
            da.SelectCommand.Parameters.AddWithValue("@tarih2", dateEdit2.Text);
            DataTable dt = new DataTable();

            da.Fill(dt);
            gridControl1.DataSource = dt;
            gridView1.ExpandAllGroups();
            gridView1.GroupFooterShowMode = GroupFooterShowMode.VisibleAlways;
            // Create and setup the first summary item.

            GridGroupSummaryItem item1 = new GridGroupSummaryItem();
            item1.FieldName = "Odeme Tutari";
            item1.SummaryType = DevExpress.Data.SummaryItemType.Sum;
            item1.DisplayFormat = "Odeme Tutari {0:c2}";
            item1.ShowInGroupColumnFooter = gridView1.Columns["Odeme Tutari"];
            gridView1.GroupSummary.Add(item1);

            {
                //GridView RunTime Gruplama
                GridColumn Grupla = gridView1.Columns["Odeme Tipi"];

                gridView1.BeginSort();
                try
                {
                    gridView1.ClearGrouping();
                    Grupla.GroupIndex = 0;
                }
                finally
                {
                    gridView1.EndSort();
                }
                gridView1.ExpandAllGroups();
            }

            gridView1.Columns["Odeme Tutari"].SummaryItem.SummaryType = DevExpress.Data.SummaryItemType.Sum;
            gridView1.Columns["Odeme Tutari"].SummaryItem.DisplayFormat = "Odeme Toplam = {0}";
            gridView1.BestFitColumns();
            gridView1.Columns["Odeme Tutari"].DisplayFormat.FormatType = FormatType.Numeric;
            gridView1.Columns["Odeme Tutari"].DisplayFormat.FormatString = "c2";

            gridView1.Columns["Iade Tutari"].SummaryItem.SummaryType = DevExpress.Data.SummaryItemType.Sum;
            gridView1.Columns["Iade Tutari"].SummaryItem.DisplayFormat = "Iade Toplam = {0}";
            gridView1.BestFitColumns();
            gridView1.Columns["Iade Tutari"].DisplayFormat.FormatType = FormatType.Numeric;
            gridView1.Columns["Iade Tutari"].DisplayFormat.FormatString = "c2";

            gridView1.Columns["Odeme Tipi"].SortOrder = DevExpress.Data.ColumnSortOrder.Descending;

            gridView1.BestFitColumns();
        }