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

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

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

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';
FLUSH PRIVILEGES;
 
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.
cls
@echo off
cls
echo.
echo * * * Reset MySql password * * *
echo.
pause
echo.
echo Stop MySql Service
echo.
sc stop MySql
echo.
pause
echo.
echo Kill mysqld.exe
echo.
taskkill -f -im mysqld.exe
echo.
pause
echo.
rem dir "C:\ProgramData\MySQL\MySQL Server 5.5\my.ini"
echo.
echo Run MySqlD
echo.
"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.
pause
echo.
echo Start MySql Service
echo.
sc start MySql
echo.
pause
echo.
echo Try to connect
echo.
"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"
echo.
cd\temp
echo.
** 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”

 

https://docs.microsoft.com/en-us/sql/relational-databases/system-compatibility-views/sys-sysprocesses-transact-sql?view=sql-server-2017


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 : https://www.sqlshack.com/creating-successful-auditing-strategy-sql-server-databases/

 

 

 

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.


sp_Myforeachdb



USE master;
GO
/*
 * 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
as

 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
 else
  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']'
            end
            /* 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
      else
       deallocate hCForEachTable
       
      RAISERROR(55555, 16, 1); -- N'sp_MSforeach_worker assert failed:  command too long'
      return 1
     end
     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
      continue
     end
     /* 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
     end
     select @useq = @useq + 1
    end
    select @cmd = stuff(@cmd, @replacecharindex, 1, @name)
    select @replacecharindex = charindex(@replacechar, @cmd)
   end

   /* 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
    continue
   end

   /* 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
 else
  deallocate hCForEachTable
  
 return 0

GO

GO
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
 AS

 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) 
  exec(@precommand) 
 
 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(d.name, ''UserAccess'') <> ''SINGLE_USER'' and (has_dbaccess(d.name) = 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) 
  exec(@postcommand) 
 
   declare @tempdb nvarchar(258) 
   SELECT @tempdb = REPLACE(@origdb, N']', N']]') 
   exec (N'use ' + N'[' + @tempdb + N']') 
 
 return @retval 
GO  

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 [https://github.com/MichelleUfford/sql-scripts/tree/master/indexes]

 

 

Lire la suite de Reorganize or Rebuild Indexes

Fil RSS des articles de cette catégorie