SQL Script to send a file by FTP

Rédigé par M 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

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

/************************************************************

Description : Send a file by FTP

 @FTPServer      The host name.    ftp.example.com
 @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%

*************************************************************/
 CREATE PROCEDURE sp_FtpSendFile

     -- 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)

AS
 BEGIN

/*
     
 -- sandbox

DECLARE
      @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

SELECT
     @FTPServer = 'ftp.mysite.com' ,
     @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)
     BEGIN
         SET @cmd = 'echo quote PASV >> ' + @workfilename
         INSERT @noNull EXEC master..xp_cmdshell @cmd
     END

    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
     DECLARE @a TABLE (
         id INT identity(1, 1)
         ,s VARCHAR(1000)
         );

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

Install PHP on Windows Server 2016

Rédigé par M 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.

 

REQUIREMENTS

  • 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

image

 

Powershell command to install


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

 

Installing PHP

 

Download PHP version you want to install.

http://windows.php.net/download/

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:

extension=php_wincache.dll

 

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

https://www.iis.net/downloads/microsoft/wincache-extension

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++

https://support.microsoft.com/en-ca/help/2977003/the-latest-supported-visual-c-downloads

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:

http://localhost/phpinfo.php

Fil RSS des articles