Mass delete

16/11/2016

DECLARE @Deleted_Rows AS INT
DECLARE @TopRowsToDelete AS INT

SET @Deleted_Rows = 1
SET @TopRowsToDelete  = 5000

/* 1. CRIA TMP COM INDICES*/
CREATE TABLE #xcci_log_tmp
  (
    ID int,
    LOGICENTITY nvarchar(64)
  )
CREATE NONCLUSTERED INDEX #IDX_CC_xcci_log_tmp ON #xcci_log_tmp (ID)

/* 2. PREENCHE TMP COM CHAVE*/
SELECT  [ID]
  ,[LOGICENTITY]
INTO #xcci_log_tmp
FROM [SIRC]..[OSUSR_YAA_S_LOG] WITH (NOLOCK) WHERE [LOGICENTITY] = 'XCCI' AND [DETAIL] = 'Actualização de cliente em XCCI'

/* 3. CICLO DE DELETE DE 5000 DE CADA VEZ*/
WHILE (@Deleted_Rows > 0)

BEGIN
  BEGIN TRANSACTION
     
  DELETE TOP(@TopRowsToDelete) log FROM [SIRC]..[OSUSR_YAA_S_LOG] log INNER JOIN #xcci_log_tmp tmp ON log.ID=tmp.ID
  SET @Deleted_Rows = @@ROWCOUNT

  COMMIT TRANSACTION
END

/* 4. DESTROI TMP */
DROP TABLE #xcci_log_tmp

Count Rows and Size SQL Tables

23/09/2016

CREATE TABLE #RowCountsAndSizes (TableName NVARCHAR(128),rows CHAR(11),      
       reserved VARCHAR(18),data VARCHAR(18),index_size VARCHAR(18), 
       unused VARCHAR(18))

EXEC       sp_MSForEachTable 'INSERT INTO #RowCountsAndSizes EXEC sp_spaceused ''?'' '


SELECT     TableName,CONVERT(bigint,rows) AS NumberOfRows,
           (CONVERT(bigint,left(data,len(data)-3))) / 1024 AS 'Size (MB)'
FROM       #RowCountsAndSizes 
ORDER BY  TableName

DROP TABLE #RowCountsAndSizes​
VALUES(ID,

Patern to replace/remove

\VALUES \([1-9][0-9]{0,4}|10000,

Home Setup [NAS, pi]

15/08/2016

Two raspberrypi with raspbian and acer aspire on with lubuntu

- Raspberrypi

  1. Install raspian
  2. sudo raspian-config (on desktop), setup keyboard, language, locale
    1. If needed sudo nano /etc/default/keyboard then XKBLAYOUT=”gb”
  3. Install xrdp
  4. Locally run  sudo xrdp-genkeymap /etc/xrdp/km-0409.ini (fix auth issue)
- Lubuntu
  1. Repeat 3. and 4. (Raspberrypi - xrdp)
  2. Install transmission
  3. Config web access
    1. sudo find / -iname settings.json
    2. Important to edit right file: /etc/transmission-daemon/settings.json
    3. sudo /etc/init.d/transmission-daemon stop !IMPORTANT
    4. edit settings sudo nano /etc/transmission-daemon/settings.json
    5. sudo /etc/init.d/transmission-daemon start
  4. *It works* - After install lubuntu, configure remote access on transmission client, edit > preferences > Remote. Done



bootstrap templates

23/02/2014

http://startbootstrap.com/

http://bootsnipp.com/
(form builder  / Snippets / buttons )

edit host file at windows

19/08/2013

%systemroot%\system32\drivers\etc\

Execute shell command with PHP

06/08/2013

$cmd = escapeshellcmd('mysql -u username -p -e "create database dbname"');
$test = shell_exec($cmd);

Event - how to load data infile using windows tasks

08/03/2013

program: "C:\Program Files\MySQL\MySQL Server 5.5\bin\mysql.exe"
arguments: "-uroot" "-ppasword12*" "-eSOURCE c:/loadquery.sql"

Using Batch

cd C:\inetpub\wwwroot\folderScript
start php.exe -f C:\inetpub\wwwroot\folderScript\somescript.php

/var/www permissions

06/03/2013

Add your user to www-data group