Views: 9.268 views
Hello everybody!
In this post, I will demonstrate how to perform file operations using the xp_cmdshell procedure, how to copy files, delete, rename, move, create directories, etc.
This post is a complement to other file operations using other solutions:
- File Operations Using OLE Automation in SQL Server
- SQL Server - How to list, read, write, copy, delete and move files with CLR (C #)
To enable xp_cmdshell, simply run the command below:
| 1 2 3 4 5 | sp_configure 'advanced options', 1 RECONFIGURE sp_configure 'xp_cmdshell', 1 RECONFIGURE |
stpArchive_Exists
Stored Procedure that returns a Boolean variable (BIT) informing if a given file or directory exists.
stpArchive_ListarSource code
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | CREATE PROCEDURE dbo.stpArquivo_Existe ( @Ds_Arquivo VARCHAR(255), @Saida BIT OUTPUT ) AS BEGIN DECLARE @Query VARCHAR(8000) = 'IF EXIST "' + @Ds_Arquivo + '" ( echo 1 ) ELSE ( echo 0 )' DECLARE @Retorno TABLE ( Linha INT IDENTITY(1, 1), Resultado VARCHAR(MAX) ) INSERT INTO @Retorno EXEC master.dbo.xp_cmdshell @command_string = @Query SELECT @Saida = Resultado FROM @Retorno WHERE Linha = 1 END GO |
Usage examples
Checking if a directory exists
Checking if a file exists
Stored Procedure that returns the listing of files and subdirectories of a given directory.
stpArchive_LerSource code
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 | CREATE PROCEDURE dbo.stpArquivo_Listar ( @Ds_Diretorio VARCHAR(255) ) AS BEGIN DECLARE @Query VARCHAR(8000) = 'dir/ -C /4 /N "' + @Ds_Diretorio + '"' DECLARE @Retorno TABLE ( Linha INT IDENTITY(1, 1), Resultado VARCHAR(MAX) ) DECLARE @Tabela_Final TABLE ( Linha INT IDENTITY(1, 1), Dt_Criacao DATETIME, Fl_Tipo BIT, Qt_Tamanho INT, Ds_Arquivo VARCHAR(255) ) INSERT INTO @Retorno EXEC master.dbo.xp_cmdshell @command_string = @Query INSERT INTO @Tabela_Final(Dt_Criacao, Fl_Tipo, Qt_Tamanho, Ds_Arquivo) SELECT CONVERT(DATETIME, LEFT(Resultado, 17), 103) AS Dt_Criacao, 0 AS Fl_Tipo, 0 AS Qt_Tamanho, SUBSTRING(Resultado, 37, LEN(Resultado)) AS Ds_Arquivo FROM @Retorno WHERE Resultado IS NOT NULL AND Linha >= 6 AND Linha < (SELECT MAX(Linha) FROM @Retorno) - 2 AND Resultado LIKE '%<DIR>%' AND SUBSTRING(Resultado, 37, LEN(Resultado)) NOT IN ('.', '..') ORDER BY Ds_Arquivo INSERT INTO @Tabela_Final(Dt_Criacao, Fl_Tipo, Qt_Tamanho, Ds_Arquivo) SELECT CONVERT(DATETIME, LEFT(Resultado, 17), 103) AS Dt_Criacao, 1 AS Fl_Tipo, LTRIM(SUBSTRING(LTRIM(Resultado), 18, 19)) AS Qt_Tamanho, SUBSTRING(Resultado, CHARINDEX(LTRIM(SUBSTRING(LTRIM(Resultado), 18, 19)), Resultado, 18) + LEN(LTRIM(SUBSTRING(LTRIM(Resultado), 18, 19))) + 1, LEN(Resultado)) AS Ds_Arquivo FROM @Retorno WHERE Resultado IS NOT NULL AND Linha >= 6 AND Linha < (SELECT MAX(Linha) FROM @Retorno) - 2 AND Resultado NOT LIKE '%<DIR>%' ORDER BY Ds_Arquivo --SELECT * FROM @Retorno SELECT * FROM @Tabela_Final END GO |
Result:
Stored Procedure that reads a text file and returns its contents in table form.
stpWrite_FileSource code
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | CREATE PROCEDURE dbo.stpArquivo_Ler ( @Ds_Arquivo VARCHAR(255) ) AS BEGIN DECLARE @Query VARCHAR(8000) = 'type "' + @Ds_Arquivo + '"' DECLARE @Retorno TABLE ( Linha INT IDENTITY(1, 1), Resultado VARCHAR(MAX) ) INSERT INTO @Retorno EXEC master.dbo.xp_cmdshell @command_string = @Query SELECT Linha, Resultado FROM @Retorno END GO |
Result:
As you may have noticed, importing accented files doesn't work very well, because the DOS Prompt type command, even under Windows in Portuguese, doesn't work that well. Alternatively, we can use the old known Power Shell.
Source code in Powershell
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | CREATE PROCEDURE dbo.stpArquivo_Ler ( @Ds_Arquivo VARCHAR(255) ) AS BEGIN DECLARE @Query VARCHAR(8000) = 'powershell.exe -ExecutionPolicy Bypass -Command "Get-Content ""' + @Ds_Arquivo + '"""' DECLARE @Retorno TABLE ( Linha INT IDENTITY(1, 1), Resultado VARCHAR(MAX) ) INSERT INTO @Retorno EXEC master.dbo.xp_cmdshell @command_string = @Query SELECT * FROM @Retorno END |
Result:
Stored Procedure that writes text to a file. Due to a limitation of DOS Prompt, it is not possible to write text with line breaks (for this, use the solution with OLE Automation or CLR).
stpApaga_ArchiveSource code
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | CREATE PROCEDURE dbo.stpEscreve_Arquivo ( @Ds_Arquivo VARCHAR(255), @Ds_Texto VARCHAR(MAX), @Fl_Sobrescrever BIT = 0 ) AS BEGIN SET NOCOUNT ON DECLARE @Query VARCHAR(8000) = 'ECHO ' + @Ds_Texto + (CASE WHEN @Fl_Sobrescrever = 1 THEN ' > ' ELSE ' >> ' END) + ' ' + @Ds_Arquivo + '"' DECLARE @Retorno TABLE ( Resultado VARCHAR(MAX) ) INSERT INTO @Retorno EXEC master.dbo.xp_cmdshell @command_string = @Query END |
Result:
Stored Procedure that physically deletes a file on disk or network.
stpCopia_FileSource code
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | CREATE PROCEDURE dbo.stpApaga_Arquivo ( @Ds_Arquivo VARCHAR(255) ) AS BEGIN SET NOCOUNT ON DECLARE @Query VARCHAR(8000) = 'del /F /Q "' + @Ds_Arquivo + '"' DECLARE @Retorno TABLE ( Resultado VARCHAR(MAX) ) INSERT INTO @Retorno EXEC master.dbo.xp_cmdshell @command_string = @Query END |
Result deleting a file:
Result using wildcard:
Stored Procedure that copies a file to another directory, either locally or on the network.
stpMove_FileSource code
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 | CREATE PROCEDURE dbo.stpCopia_Arquivo ( @Ds_Arquivo VARCHAR(255), @Ds_Diretorio VARCHAR(255), @Fl_Sobrescrever BIT = 0 ) AS BEGIN SET NOCOUNT ON DECLARE @Query VARCHAR(8000), @Nm_Arquivo_Destino VARCHAR(500) = @Ds_Diretorio + REVERSE(LEFT(REVERSE(@Ds_Arquivo),CHARINDEX('\', REVERSE(@Ds_Arquivo), 1) - 1)), @Resultado VARCHAR(MAX) IF (@Fl_Sobrescrever = 0) SET @Query = 'IF EXIST "' + @Nm_Arquivo_Destino + '" ( ECHO Arquivo já existe ) ELSE ( copy "' + @Ds_Arquivo + '" "' + @Ds_Diretorio + '")' ELSE SET @Query = 'copy ' + (CASE WHEN @Fl_Sobrescrever = 1 THEN '/Y' ELSE '' END) + ' "' + @Ds_Arquivo + '" "' + @Ds_Diretorio + '"' DECLARE @Retorno TABLE ( Linha INT IDENTITY(1, 1), Resultado VARCHAR(MAX) ) INSERT INTO @Retorno EXEC master.dbo.xp_cmdshell @command_string = @Query SELECT @Resultado = LTRIM(RTRIM(Resultado)) FROM @Retorno WHERE Linha = 1 PRINT @Resultado END |
Result:
Stored Procedure that moves a file to another directory, either local or network.
stpCria_DirectorySource code
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 | CREATE PROCEDURE dbo.stpMove_Arquivo ( @Ds_Arquivo VARCHAR(255), @Ds_Diretorio VARCHAR(255), @Fl_Sobrescrever BIT = 0 ) AS BEGIN SET NOCOUNT ON DECLARE @Query VARCHAR(8000), @Nm_Arquivo_Destino VARCHAR(500) = @Ds_Diretorio + REVERSE(LEFT(REVERSE(@Ds_Arquivo),CHARINDEX('\', REVERSE(@Ds_Arquivo), 1) - 1)), @Resultado VARCHAR(MAX) IF (@Fl_Sobrescrever = 0) SET @Query = 'IF EXIST "' + @Nm_Arquivo_Destino + '" ( ECHO Arquivo já existe ) ELSE ( move "' + @Ds_Arquivo + '" "' + @Ds_Diretorio + '")' ELSE SET @Query = 'move ' + (CASE WHEN @Fl_Sobrescrever = 1 THEN '/Y' ELSE '' END) + ' "' + @Ds_Arquivo + '" "' + @Ds_Diretorio + '"' DECLARE @Retorno TABLE ( Linha INT IDENTITY(1, 1), Resultado VARCHAR(MAX) ) INSERT INTO @Retorno EXEC master.dbo.xp_cmdshell @command_string = @Query SELECT @Resultado = LTRIM(RTRIM(Resultado)) FROM @Retorno WHERE Linha = 1 PRINT @Resultado END |
Result:
Stored Procedure that creates a directory in a specified location.
stpApaga_DirectorySource code
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | CREATE PROCEDURE dbo.stpCria_Diretorio ( @Ds_Diretorio VARCHAR(255) ) AS BEGIN SET NOCOUNT ON DECLARE @Query VARCHAR(8000) = 'mkdir "' + @Ds_Diretorio + '"' DECLARE @Retorno TABLE ( Resultado VARCHAR(MAX) ) INSERT INTO @Retorno EXEC master.dbo.xp_cmdshell @command_string = @Query END |
Result:
Stored Procedure that deletes a directory, with the possibility of deleting files and subfolders as well.
Source code
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | CREATE PROCEDURE dbo.stpApaga_Diretorio ( @Ds_Diretorio VARCHAR(255), @Fl_Recursivo BIT = 0 ) AS BEGIN SET NOCOUNT ON DECLARE @Query VARCHAR(8000) = 'rmdir' + (CASE WHEN @Fl_Recursivo = 1 THEN ' /S' ELSE '' END) + ' "' + @Ds_Diretorio + '"' DECLARE @Retorno TABLE ( Resultado VARCHAR(MAX) ) INSERT INTO @Retorno EXEC master.dbo.xp_cmdshell @command_string = @Query END |
Result:
As I mentioned in some posts here on the blog, I'm not a fan of enabling and using xp_cmdshell, since it gives "powers" to any sysadmin user of the instance to execute any command that the user who starts the SQL Server service has privilege , from a dir command, to delete, format, etc.
For this reason, I prefer to use CLR, where you program the routines using the C # programming language and the .NET Framework and publish the routines in the instance. Both sysadmin and normal users are only able to use the methods implemented in the CLR library, not allowing unscheduled malicious code to run, but I understand that not every DBA wants to do the work of creating C # libraries and ends up choosing to use a simpler approach using xp_cmdshell and DOS and / or PowerShell commands.
That's it folks!
I hope you enjoyed this post and see you next time.
I tried to move the file, but gave "Access denied". What profile is missing?
Thank you helped me a lot