Add-on Google search link fix

Rédigé par Sozezzo - - Aucun commentaire

Prevents Google and Yandex search pages from modifying search result links when you click them.Google and Yandex search pages have the annoying habit of changing the result link when you click it. So if you right-click the link in order to copy it you get some gibberish instead of what you wanted.



This extension disables that behavior – on any Google or Yandex domain, without having to configure anything. Simply install and enjoy! As an added bonus, the search engine can no longer track your clicks.

Classé dans : firefox - Mots clés : aucun

SQL Script to send a file by FTP

Rédigé par Sozezzo - - Aucun commentaire

This Stored procedure send a file by FTP.

Check if SQL Agent has access to the file and FTP server.

The little trick is to create a file that will run. It can have conflicts easily because filename is always the same. Next version will be with random name. 


Use this code carefully, it is not 100% safe.

USE master;
 PRINT '-- Server name: '+@@servername

         SELECT *
         FROM sysobjects
         WHERE id = object_id(N'[dbo].[sp_FtpSendFile]')
             AND OBJECTPROPERTY(id, N'IsProcedure') = 1
     DROP PROCEDURE [dbo].[sp_FtpSendFile]


Description : Send a file by FTP

 @FTPServer      The host name.
 @FTPUser        The username for the FTP site.
 @FTPPWD         The password for the FTP site.
 @FTPPath        The ftp subfolder within the FTP site to place the file. Make sure to use forward slashes and leave a trailing slash.
 @FTPFileName    (optional) The filename to write within FTP. Typically the same as the source file name.
 @FTPPASV        (optional) Use passive mode
 @SourcePath     The local path to the directory that contains the source file. Make sure to have a trailing slash. c:\MyLocalPath\
 @SourceFile     The local source file to ftp.
 @workdir        (optional) The working local directory. This is where the stored proc will temporarily write a command file containing the FTP commands it will execute. = %TEMP%


     -- Remote
      @FTPServer VARCHAR(128)
     ,@FTPUser VARCHAR(128)
     ,@FTPPWD VARCHAR(128)
     ,@FTPPath VARCHAR(128)
     ,@FTPFileName VARCHAR(128) = NULL  -- (optional)
     ,@FTPPASV INT = 1                  -- (optional)

    -- Local
     ,@SourcePath VARCHAR(128)
     ,@SourceFile VARCHAR(128)
     ,@workdir VARCHAR(128) = '%TEMP%'  -- (optional)

    -- debug
     ,@debug int = 0                    -- (optional)


 -- sandbox

      @FTPServer VARCHAR(128)
     ,@FTPUser VARCHAR(128) = ''
     ,@FTPPWD VARCHAR(128)
     ,@FTPPath VARCHAR(128)
     ,@FTPFileName VARCHAR(128) = NULL -- (optional)
     ,@FTPPASV INT = 1 -- (optional)
     ,@SourcePath VARCHAR(128)
     ,@SourceFile VARCHAR(128)
     ,@workdir VARCHAR(128) = '%TEMP%' -- (optional)
     ,@debug int = 0

     @FTPServer = '' ,
     @FTPUser = 'myusername' ,
     @FTPPWD = 'mypassword' ,
     @FTPPath = '/' ,
     @SourcePath = 'K:\temp\Backup\' ,
     @SourceFile = 'myFile.bak' ,
     @debug = 0


    SET nocount on
     DECLARE @cmd VARCHAR(1000)
     DECLARE @workfilename VARCHAR(128)
     DECLARE @noNull table (r NVARCHAR(MAX))

    SET @workfilename =  @workdir + 'ftpcmd.txt'

    -- deal with special characters for echo commands
     SET @FTPServer = replace(replace(replace(@FTPServer, '|', '^|'), '<', '^<'), '>', '^>')
     SET @FTPUser = replace(replace(replace(@FTPUser, '|', '^|'), '<', '^<'), '>', '^>')
     SET @FTPPWD = replace(replace(replace(@FTPPWD, '|', '^|'), '<', '^<'), '>', '^>')
     SET @FTPPath = replace(replace(replace(@FTPPath, '|', '^|'), '<', '^<'), '>', '^>')

    SET @cmd = 'echo open ' + @FTPServer + ' > ' + @workfilename
     INSERT @noNull EXEC master..xp_cmdshell @cmd

    SET @cmd = 'echo ' + @FTPUser + '>> ' + @workfilename
     INSERT @noNull EXEC master..xp_cmdshell @cmd

    SET @cmd = 'echo ' + @FTPPWD + '>> ' + @workfilename
     INSERT @noNull EXEC master..xp_cmdshell @cmd

    IF (@FTPPASV = 1)
         SET @cmd = 'echo quote PASV >> ' + @workfilename
         INSERT @noNull EXEC master..xp_cmdshell @cmd

    SET @cmd = 'echo cd '+ @FTPPath + '>> ' + @workfilename
     INSERT @noNull EXEC master..xp_cmdshell @cmd

    SET @cmd = 'echo binary >> ' + @workfilename
     INSERT @noNull EXEC master..xp_cmdshell @cmd

    SET @cmd = 'echo put ' + @SourcePath + @SourceFile + ' ' + ISNULL(@FTPFileName, '') + ' >> ' + @workfilename
     INSERT @noNull EXEC master..xp_cmdshell @cmd

    SET @cmd = 'echo dir ' + ISNULL(@FTPFileName, @SourceFile) + ' >> ' + @workfilename
     INSERT @noNull EXEC master..xp_cmdshell @cmd

    SET @cmd = 'echo quit' + ' >> ' + @workfilename
     INSERT @noNull EXEC master..xp_cmdshell @cmd

    SET @cmd = 'ftp -s:' + @workfilename
         id INT identity(1, 1)
         ,s VARCHAR(1000)

    IF (@debug = 1)
         SET @cmd = 'TYPE ' +  @workfilename
         EXEC master..xp_cmdshell @cmd
     INSERT @a EXEC master..xp_cmdshell @cmd;
         SELECT id, ouputtmp = s
         FROM @a;

Classé dans : sqlscript - Mots clés : aucun

Install PHP on Windows Server 2016

Rédigé par Sozezzo - - Aucun commentaire

Easy steps to install PHP on Windows Server 2016

This tutorial was create for Windows Server 2016 x64, but you can do it on Windows Workstation x86, but you just need to use x86 everywhere.



  • Windows Server 2016
  • IIS
  • PHP bin files
  • WinCache
  • VC++



IIS service with CGI extension.

Before to install, verify if you already have it.

Get-WindowsFeature web-cgi



Powershell command to install

Install-WindowsFeature -name web-server,web-cgi –IncludeManagementTools


Installing PHP


Download PHP version you want to install.

Extract file to C:\Program Files\php


Select PHP configuration to use

You have 2 configuration files:

  • php.ini-development
  • php.ini-production

You must rename the file configuration to php.ini.


Add Wincache configuration

Open php.ini file and add this line in the end:



Add PHP path on Environment variables

On Explorer click properties on This PC

Click on Advanced system settings

Click on Advanced \ Environment Variables

On System variables, edit variable : Path

Add new path. ( C:\Program Files\php )


Install WinCache Extention

Download and extract it.

Copy files to folder : C:\Program Files\php\ext

  • php_wincache.dll
  • php_wincache.pdb       (optional)



Configurate PHP in IIS

In IIS open Handler Mappings

Add Module Mapping

  • Request path : *.php
  • Module : FastCgiModule
  • Executable (optional) : C:\Program Files\php\php-cgi.exe
  • Name : PHP

Click on Request Restriction…

Select "FIle or Folder"


Installing VC++

Just install it.


Verify if it works.

Open the default web site path, root, and create a file <phpinfo.php> with this line:

<?php  phpinfo(); ?>


Usually it is this path: C:\inetpub\wwwroot


Open in your local server this page:


MySql Reset root password

Rédigé par Sozezzo - - Aucun commentaire
Step by step how you can change 'root' password
You must have rights to stop/start services, and kill process.
Step 1: Stop MySql service
run: services.msc
Step 2 : Create file to reset password
You can use your password and your file name.
We use password : Pass1Word2
and file name : C:\temp\reset.txt
USE mysql;
UPDATE mysql.user SET Password=PASSWORD('Pass!Word@') WHERE User='root';
Step 3 : Find our my.ini file
It is usually locate on 'C:\ProgramData\MySQL\MySQL Server ????\my.ini'
But, you can try to find using :
C:\ dir my.ini /s /b
You can check MySQL service's property.
For this sample, we use this:
C:\ProgramData\MySQL\MySQL Server 5.5\my.ini
Step 4 : Navigate to MySql bin directory
Open command prompt there.
Type command :
mysqld --defaults-file="C:\\ProgramData\\MySQL\\MySQL Server 5.5\\my.ini" --init-file=C:\temp\reset.txt
Attention : Use double "\\" insted "\"
Step 5 : Open "Task Manager" and kill process "mysqld.exe"
Step 6: Start service "MySql"
By now, we have a new password.
@echo off
echo * * * Reset MySql password * * *
echo Stop MySql Service
sc stop MySql
echo Kill mysqld.exe
taskkill -f -im mysqld.exe
rem dir "C:\ProgramData\MySQL\MySQL Server 5.5\my.ini"
echo Run MySqlD
"C:\Program Files\MySQL\MySQL Server 5.5\bin\mysqld"   --defaults-file="C:\Program Files\MySQL\MySQL Server 5.5\my.ini" --init-file=C:\Temp\reset.txt
echo Start MySql Service
sc start MySql
echo Try to connect
"C:\Program Files\MySQL\MySQL Server 5.5\bin\mysql.exe" "--defaults-file=C:\Program Files\MySQL\MySQL Server 5.5\my.ini" "-uroot" "-p"
** Attention : Make sure all file are ANSI

Simple log connections SQLServer 2000

Rédigé par Sozezzo - - Aucun commentaire

This code create a table to log all connections.

It is very simple, it just logs when, who, from where and which database.

For this we use “master.dbo.sysprocesses”

Create table to log on [Master] database.

 USE [master] GO CREATE TABLE [dbo].[dbaLogAccess] (     [dbaLogAccessId] [int] IDENTITY (1, 1) NOT NULL ,     [ConnectionDate] [datetime] NULL ,     [loginame] [nvarchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,     [hostname] [nchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL ,     [dbname] [nvarchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ) ON [PRIMARY] 

Script to add logs, you can run each minute to update the log. This script avoids to repeater, it only saves new connections. We recommend using a job and schedule each minute to run this script.

 INSERT INTO master.dbo.dbaLogAccess (ConnectionDate, loginame, hostname, dbname)   SELECT DISTINCT     tbNew.ConnectionDate,     tbNew.loginame,     tbNew.hostname,     tbNew.dbname   FROM (SELECT     CONVERT(char(10), GETDATE(), 126) AS ConnectionDate,     RTRIM(loginame) AS loginame,     hostname,     CASE       WHEN dbid = 0 THEN ''       WHEN dbid <> 0 THEN DB_NAME(dbid)     END AS dbname   FROM master.dbo.sysprocesses   WHERE (hostname <> '')) AS tbNew   LEFT OUTER JOIN master.dbo.dbaLogAccess AS tbOld     ON tbNew.ConnectionDate = tbOld.ConnectionDate     AND tbNew.loginame = tbOld.loginame     AND tbNew.hostname = tbOld.hostname     AND tbNew.dbname = tbOld.dbname   WHERE (tbOld.dbaLogAccessId IS NULL); 

This code limit connection by day, we can change by hour or by minute, you just change how many character you use it:
 print CONVERT(char(14), GETDATE(), 126) + '00:00'
print CONVERT(char(17), GETDATE(), 126) + '00'
print CONVERT(char(20), GETDATE(), 126)

Well, you may do not like to use [master] database, but it is the place to record the system-level information. Good news, you can easily change it.

or you can use :




SQL Server Sp_msforeachdb query character without limitation

Rédigé par Sozezzo - - Aucun commentaire

When we write a query than is running for every database in the instance of SQL Server, we use Sp_msforeachdb.
If your query is bigger than 2000 chars, the query cannot work because Sp_msforeachdb has a character limitation of 2000 chars.

The solution is to re-create MySp_myforeachdb. We use sp_helptext to see the definition of sp_MSforeachdb and sp_MSforeach_worker, and we change to a new character limitation of 262144 chars.

Everything works as before but without character limitation of 2000 chars.


USE master;
 * This is the worker proc for all of the "for each" type procs.  Its function is to read the
 * next replacement name from the cursor (which returns only a single name), plug it into the
 * replacement locations for the commands, and execute them.  It assumes the cursor "hCForEach***"
 * has already been opened by its caller.
 * worker_type is a parameter that indicates whether we call this for a database (1) or for a table (0)
create proc dbo.sp_Myforeach_worker
 @command1 nvarchar(max), @replacechar nchar(1) = N'?', @command2 nvarchar(max) = null, @command3 nvarchar(max) = null, @worker_type int =1

 create table #qtemp ( /* Temp command storage */
  qnum    int    NOT NULL,
  qchar    nvarchar(max) COLLATE database_default NULL

 set nocount on
 declare @name nvarchar(517), @namelen int, @q1 nvarchar(max), @q2 nvarchar(max)
   declare @q3 nvarchar(max), @q4 nvarchar(max), @q5 nvarchar(max)
 declare @q6 nvarchar(max), @q7 nvarchar(max), @q8 nvarchar(max), @q9 nvarchar(max), @q10 nvarchar(max)
 declare @cmd nvarchar(max), @replacecharindex int, @useq tinyint, @usecmd tinyint, @nextcmd nvarchar(max)
   declare @namesave nvarchar(517), @nametmp nvarchar(517), @nametmp2 nvarchar(258)

 declare @local_cursor cursor
 if @worker_type=1 
  set @local_cursor = hCForEachDatabase
  set @local_cursor = hCForEachTable
 open @local_cursor
 fetch @local_cursor into @name

 /* Loop for each database */
 while (@@fetch_status >= 0) begin
  /* Initialize. */

      /* save the original dbname */
      select @namesave = @name
  select @useq = 1, @usecmd = 1, @cmd = @command1, @namelen = datalength(@name)
  while (@cmd is not null) begin  /* Generate @q* for exec() */
    * Parse each @commandX into a single executable batch.
    * Because the expanded form of a @commandX may be > OSQL_MAXCOLLEN_SET, we'll need to allow overflow.
    * We also may append @commandX's (signified by '++' as first letters of next @command).
   select @replacecharindex = charindex(@replacechar, @cmd)
   while (@replacecharindex <> 0) begin

            /* 7.0, if name contains ' character, and the name has been single quoted in command, double all of them in dbname */
            /* if the name has not been single quoted in command, do not doulbe them */
            /* if name contains ] character, and the name has been [] quoted in command, double all of ] in dbname */
            select @name = @namesave
            select @namelen = datalength(@name)
            declare @tempindex int
            if (substring(@cmd, @replacecharindex - 1, 1) = N'''') begin
               /* if ? is inside of '', we need to double all the ' in name */
               select @name = REPLACE(@name, N'''', N'''''')
            end else if (substring(@cmd, @replacecharindex - 1, 1) = N'[') begin
               /* if ? is inside of [], we need to double all the ] in name */
               select @name = REPLACE(@name, N']', N']]')
            end else if ((@name LIKE N'%].%]') and (substring(@name, 1, 1) = N'[')) begin
               /* ? is NOT inside of [] nor '', and the name is in [owner].[name] format, handle it */
               /* !!! work around, when using LIKE to find string pattern, can't use '[', since LIKE operator is treating '[' as a wide char */
               select @tempindex = charindex(N'].[', @name)
               select @nametmp  = substring(@name, 2, @tempindex-2 )
               select @nametmp2 = substring(@name, @tempindex+3, len(@name)-@tempindex-3 )
               select @nametmp  = REPLACE(@nametmp, N']', N']]')
               select @nametmp2 = REPLACE(@nametmp2, N']', N']]')
               select @name = N'[' + @nametmp + N'].[' + @nametmp2 + ']'
            end else if ((@name LIKE N'%]') and (substring(@name, 1, 1) = N'[')) begin
               /* ? is NOT inside of [] nor '', and the name is in [name] format, handle it */
       /* j.i.c., since we should not fall into this case */
               /* !!! work around, when using LIKE to find string pattern, can't use '[', since LIKE operator is treating '[' as a wide char */
               select @nametmp = substring(@name, 2, len(@name)-2 )
               select @nametmp = REPLACE(@nametmp, N']', N']]')
               select @name = N'[' + @nametmp + N']'
            /* Get the new length */
            select @namelen = datalength(@name)

            /* start normal process */
    if (datalength(@cmd) + @namelen - 1 > 262144) begin
     /* Overflow; put preceding stuff into the temp table */
     if (@useq > 9) begin
      close @local_cursor
      if @worker_type=1 
       deallocate hCForEachDatabase
       deallocate hCForEachTable
      RAISERROR(55555, 16, 1); -- N'sp_MSforeach_worker assert failed:  command too long'
      return 1
     if (@replacecharindex < @namelen) begin
      /* If this happened close to beginning, make sure expansion has enough room. */
      /* In this case no trailing space can occur as the row ends with @name. */
      select @nextcmd = substring(@cmd, 1, @replacecharindex)
      select @cmd = substring(@cmd, @replacecharindex + 1, 262144)
      select @nextcmd = stuff(@nextcmd, @replacecharindex, 1, @name)
      select @replacecharindex = charindex(@replacechar, @cmd)
      insert #qtemp values (@useq, @nextcmd)
      select @useq = @useq + 1
     /* Move the string down and stuff() in-place. */
     /* Because varchar columns trim trailing spaces, we may need to prepend one to the following string. */
     /* In this case, the char to be replaced is moved over by one. */
     insert #qtemp values (@useq, substring(@cmd, 1, @replacecharindex - 1))
     if (substring(@cmd, @replacecharindex - 1, 1) = N' ') begin
      select @cmd = N' ' + substring(@cmd, @replacecharindex, 262144)
      select @replacecharindex = 2
     end else begin
      select @cmd = substring(@cmd, @replacecharindex, 262144)
      select @replacecharindex = 1
     select @useq = @useq + 1
    select @cmd = stuff(@cmd, @replacecharindex, 1, @name)
    select @replacecharindex = charindex(@replacechar, @cmd)

   /* Done replacing for current @cmd.  Get the next one and see if it's to be appended. */
   select @usecmd = @usecmd + 1
   select @nextcmd = case (@usecmd) when 2 then @command2 when 3 then @command3 else null end
   if (@nextcmd is not null and substring(@nextcmd, 1, 2) = N'++') begin
    insert #qtemp values (@useq, @cmd)
    select @cmd = substring(@nextcmd, 3, 262144), @useq = @useq + 1

   /* Now exec() the generated @q*, and see if we had more commands to exec().  Continue even if errors. */
   /* Null them first as the no-result-set case won't. */
   select @q1 = null, @q2 = null, @q3 = null, @q4 = null, @q5 = null, @q6 = null, @q7 = null, @q8 = null, @q9 = null, @q10 = null
   select @q1 = qchar from #qtemp where qnum = 1
   select @q2 = qchar from #qtemp where qnum = 2
   select @q3 = qchar from #qtemp where qnum = 3
   select @q4 = qchar from #qtemp where qnum = 4
   select @q5 = qchar from #qtemp where qnum = 5
   select @q6 = qchar from #qtemp where qnum = 6
   select @q7 = qchar from #qtemp where qnum = 7
   select @q8 = qchar from #qtemp where qnum = 8
   select @q9 = qchar from #qtemp where qnum = 9
   select @q10 = qchar from #qtemp where qnum = 10
   truncate table #qtemp
   exec (@q1 + @q2 + @q3 + @q4 + @q5 + @q6 + @q7 + @q8 + @q9 + @q10 + @cmd)
   select @cmd = @nextcmd, @useq = 1
  end /* while @cmd is not null, generating @q* for exec() */

  /* All commands done for this name.  Go to next one. */
  fetch @local_cursor into @name
 end /* while FETCH_SUCCESS */
 close @local_cursor
 if @worker_type=1 
  deallocate hCForEachDatabase
  deallocate hCForEachTable
 return 0


CREATE PROC sp_Myforeachdb
 @command1 nvarchar(max), @replacechar nchar(1) = N'?', @command2 nvarchar(max) = null, @command3 nvarchar(max) = null, 
 @precommand nvarchar(max) = null, @postcommand nvarchar(max) = null

 set deadlock_priority low 
 /* This proc returns one or more rows for each accessible db, with each db defaulting to its own result set */ 
 /* @precommand and @postcommand may be used to force a single result set via a temp table. */ 
 /* Preprocessor won't replace within quotes so have to use str(). */ 
 declare @inaccessible nvarchar(12), @invalidlogin nvarchar(12), @dbinaccessible nvarchar(12) 
 select @inaccessible = ltrim(str(convert(int, 0x03e0), 11)) 
 select @invalidlogin = ltrim(str(convert(int, 0x40000000), 11)) 
 select @dbinaccessible = N'0x80000000'  /* SQLDMODbUserProf_InaccessibleDb; the negative number doesn't work in convert() */ 
 if (@precommand is not null) 
 declare @origdb nvarchar(128) 
 select @origdb = db_name() 
 /* If it's a single user db and there's an entry for it in sysprocesses who isn't us, we can't use it. */ 
   /* Create the select */ 
 exec(N'declare hCForEachDatabase cursor global for select name from master.dbo.sysdatabases d ' + 
   N' where (d.status & ' + @inaccessible + N' = 0)' + 
   N' and (DATABASEPROPERTYEX(, ''UserAccess'') <> ''SINGLE_USER'' and (has_dbaccess( = 1))' ) 
 declare @retval int 
 select @retval = @@error 
 if (@retval = 0) 
  exec @retval = dbo.sp_Myforeach_worker @command1, @replacechar, @command2, @command3, 1 
 if (@retval = 0 and @postcommand is not null) 
   declare @tempdb nvarchar(258) 
   SELECT @tempdb = REPLACE(@origdb, N']', N']]') 
   exec (N'use ' + N'[' + @tempdb + N']') 
 return @retval 

Reorganize or Rebuild Indexes

Rédigé par Sozezzo - - Aucun commentaire

This script remedy index fragmentation by reorganizing or rebuilding an index.

Small tables, small indexes, low fragmentation, we do not care, and keep simple & easy

We are talking about the guidance which is:
•if a table has less than 10000 rows, to do nothing
•if an index has less than 1000 pages, to do nothing
•if the index has:
    less than 5% logical fragmentation, to do nothing
    between 5% and 30% logical fragmentation, reorganize it
    more than 30% logical fragmentation, rebuild it
• New FILLFACTOR = 98%

Why do we fix something we do not need it?
We should start to delete unused indexes if it is possible: [see Unused indexes]
•Unused index, delete it, when :
    - Nonclustered indexes
    - Non-primary keys
    - Non unique
    - Non-unused  (#Total read < 100 and #total write > 1000 )
    - Hypothetical Index.


Alternative solutions:
1. We can buy an application for index monitoring and analysis.
2. We can change the configuration. ex: MIXED_PAGE_ALLOCATION
3. We can create better indexes.
4. We can use this nice solution of Michelle Ufford []



Lire la suite de Reorganize or Rebuild Indexes

Fil RSS des articles