Views: 4.078 views
Hello guys,
Good night!
Remember the post I made about Enabling OLE Automation via T-SQL on SQL Server where did I say I would post about some cool functions exemplifying the use of OLE Automation in SQL Server? So this is the post 🙂
In order not to put too many Stored Procedures and Functions here in the post and make it too long, I created a compressed file with the source code of the scripts that are available here and I will just explain the use of each of them.
fncFile_Exists_File fncFile_Exists_File: Function that returns whether a file exists (1) or not (0).
Use: SELECT CLR.dbo.fncArquivo_Existe_FSO ('C: \ Teste.txt')
fncDirectory_Existe_FSOUse: SELECT CLR.dbo.fncArquivo_Existe_FSO ('C: \ Teste.txt')
Source code:
Transact-SQL
| 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 | CREATE FUNCTION [dbo].[fncArquivo_Existe_FSO] (@strArquivo VARCHAR(1000)) RETURNS INT AS BEGIN DECLARE @hr INT, @objFileSystem INT, @retorno INT, @source VARCHAR(250), @description VARCHAR(2000) EXEC @hr = sp_OACreate 'Scripting.FileSystemObject', @objFileSystem OUT IF @hr <> 0 BEGIN EXEC sp_OAGetErrorInfo @objFileSystem, @source OUT, @description OUT RETURN 0 END EXEC @hr = sp_OAMethod @objFileSystem, 'FileExists', @retorno OUT, @strArquivo IF (@hr <> 0) BEGIN EXEC sp_OAGetErrorInfo @objFileSystem, @source OUT, @description OUT EXEC sp_OADestroy @objFileSystem RETURN 0 END EXEC sp_OADestroy @objFileSystem RETURN @retorno END |
fncDirectory_Exists_FSO: Function that returns whether a directory exists (1) or not (0).
Usage: SELECT CLR.dbo.fncDiretorio_Existe_FSO('C:\Dirceu')
fncLer_FSO_fileUsage: SELECT CLR.dbo.fncDiretorio_Existe_FSO('C:\Dirceu')
Source code:
Transact-SQL
| 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 | CREATE FUNCTION [dbo].[fncDiretorio_Existe_FSO] (@strDiretorio VARCHAR(1000)) RETURNS INT AS BEGIN DECLARE @hr INT, @objFileSystem INT, @retorno INT, @source VARCHAR(250), @description VARCHAR(2000) EXEC @hr = sp_OACreate 'Scripting.FileSystemObject', @objFileSystem OUT IF @hr <> 0 BEGIN EXEC sp_OAGetErrorInfo @objFileSystem, @source OUT, @description OUT RETURN 0 END EXEC @hr = sp_OAMethod @objFileSystem, 'FolderExists', @retorno OUT, @strDiretorio IF (@hr <> 0) BEGIN EXEC sp_OAGetErrorInfo @objFileSystem, @source OUT, @description OUT EXEC sp_OADestroy @objFileSystem RETURN 0 END EXEC sp_OADestroy @objFileSystem RETURN @retorno END |
fncLer_FSO_file: Function that reads the contents of a file and returns a table where each row of the file is a row in the table.
Utilization: SELECT * FROM CLR.dbo.fncLer_Arquivo_FSO ('C: \ Teste.txt')
stpApaga_File_FileUtilization: SELECT * FROM CLR.dbo.fncLer_Arquivo_FSO ('C: \ Teste.txt')
Source code:
Transact-SQL
| 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 | CREATE FUNCTION [dbo].[fncLer_Arquivo_FSO] (@Ds_Arquivo VARCHAR(256)) RETURNS @Tabela_Final TABLE (Ds_Linha VARCHAR(8000)) AS BEGIN DECLARE @OLEResult INT DECLARE @FileSystemObject INT DECLARE @FileID INT DECLARE @Message VARCHAR (8000) DECLARE @Tabela TABLE ( Ds_Linha varchar(8000) ) EXECUTE @OLEResult = sp_OACreate 'Scripting.FileSystemObject', @FileSystemObject OUT IF @OLEResult <> 0 BEGIN SET @Message = 'Scripting.FileSystemObject - Error code: ' + CONVERT (VARCHAR, @OLEResult) INSERT INTO @Tabela_Final SELECT @Message RETURN END EXEC @OLEResult = sp_OAMethod @FileSystemObject, 'OpenTextFile', @FileID OUT, @Ds_Arquivo, 1, 1 IF @OLEResult <> 0 BEGIN SET @Message = 'OpenTextFile - Error code: ' + CONVERT (VARCHAR, @OLEResult) INSERT INTO @Tabela_Final SELECT @Message RETURN END EXECUTE @OLEResult = sp_OAMethod @FileID, 'ReadLine', @Message OUT WHILE (@OLEResult >= 0) BEGIN INSERT INTO @Tabela(Ds_Linha) VALUES( @Message ) EXECUTE @OLEResult = sp_OAMethod @FileID, 'ReadLine', @Message OUT END EXECUTE @OLEResult = sp_OADestroy @FileID EXECUTE @OLEResult = sp_OADestroy @FileSystemObject INSERT INTO @Tabela_Final SELECT Ds_Linha FROM @Tabela RETURN END |
stpApaga_File_File: Stored Procedure that physically erases a file.
Use: EXEC CLR.dbo.stpApaga_Arquivo_FSO 'C: \ Teste.txt'
stpCopia_File_FileUse: EXEC CLR.dbo.stpApaga_Arquivo_FSO 'C: \ Teste.txt'
Source code:
Transact-SQL
| 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 | CREATE PROCEDURE [dbo].[stpApaga_Arquivo_FSO] (@strArquivo VARCHAR(1000)) AS BEGIN DECLARE @hr INT, @objFileSystem INT, @source VARCHAR(250), @description VARCHAR(2000) EXEC @hr = sp_OACreate 'Scripting.FileSystemObject', @objFileSystem OUT IF @hr <> 0 BEGIN EXEC sp_OAGetErrorInfo @objFileSystem, @source OUT, @description OUT END IF (CLR.dbo.fncArquivo_Existe(@strArquivo) = 1) BEGIN EXEC @hr = sp_OAMethod @objFileSystem, 'DeleteFile', NULL, @strArquivo END IF (@hr <> 0) BEGIN EXEC sp_OAGetErrorInfo @objFileSystem, @source OUT, @description OUT EXEC sp_OADestroy @objFileSystem END EXEC sp_OADestroy @objFileSystem END |
stpCopia_File_File: Stored Procedure that physically copies a file from one directory to another.
Use: EXEC CLR.dbo.stpCopia_Arquivo_FSO 'C: \ Teste.txt', 'C: \ Files \ Novo_Arquivo.log', 1 - Will overwrite if it already exists (3rd parameter)
stpWrite_File_FileUse: EXEC CLR.dbo.stpCopia_Arquivo_FSO 'C: \ Teste.txt', 'C: \ Files \ Novo_Arquivo.log', 1 - Will overwrite if it already exists (3rd parameter)
Source code:
Transact-SQL
| 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 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 | CREATE PROCEDURE [dbo].[stpCopia_Arquivo_FSO] ( @strOrigem VARCHAR(MAX), @strDestino VARCHAR(MAX), @sobrescrever INT = 0 ) AS BEGIN IF (dbo.fncArquivo_Existe(@strOrigem) = 1 AND @strOrigem != @strDestino) BEGIN DECLARE @hr INT, @objFileSystem INT, @source VARCHAR(250), @description VARCHAR(2000) EXEC @hr = sp_OACreate 'Scripting.FileSystemObject', @objFileSystem OUT IF @hr <> 0 BEGIN EXEC sp_OAGetErrorInfo @objFileSystem, @source OUT, @description OUT RAISERROR('Object Creation Failed 0x%x, %s, %s',16,1,@hr,@source,@description) RETURN END IF (dbo.fncArquivo_Existe(@strDestino) = 1) IF (@sobrescrever = 1) EXEC dbo.stpApaga_Arquivo_FSO @strDestino ELSE BEGIN PRINT 'O arquivo de destino já existe e o parâmetro sobrescrever foi definido como NÃO' RETURN END EXEC @hr = sp_OAMethod @objFileSystem, 'CopyFile', NULL, @strOrigem, @strDestino IF (@hr <> 0) BEGIN EXEC sp_OAGetErrorInfo @objFileSystem, @source OUT, @description OUT EXEC sp_OADestroy @objFileSystem RAISERROR('Method Failed 0x%x, %s, %s',16, 1, @hr, @source, @description) PRINT @source PRINT @description RETURN END EXEC sp_OADestroy @objFileSystem END ELSE PRINT 'O arquivo de origem não existe ou é igual ao arquivo de destino' END GO |
stpWrite_File_File: Script that writes the contents of a variable or text to a file on disk.
Use: EXEC CLR.dbo.stpEscreve_Arquivo_FSO ('C: \ Teste.txt', 'Writing Test to File')
stpWrite_Arquivo_V2_FSOUse: EXEC CLR.dbo.stpEscreve_Arquivo_FSO ('C: \ Teste.txt', 'Writing Test to File')
Source code:
Transact-SQL
| 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 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 | CREATE PROCEDURE [dbo].[stpEscreve_Arquivo_FSO] ( @String VARCHAR(MAX), @Ds_Arquivo VARCHAR(1501) ) AS BEGIN DECLARE @objFileSystem INT, @objTextStream INT, @objErrorObject INT, @strErrorMessage VARCHAR(1000), @Command VARCHAR(1000), @hr INT SET NOCOUNT ON SELECT @strErrorMessage = 'opening the File System Object' EXECUTE @hr = sp_OACreate 'Scripting.FileSystemObject', @objFileSystem OUT IF @HR = 0 SELECT @objErrorObject = @objFileSystem, @strErrorMessage = 'Creating file "' + @Ds_Arquivo + '"' IF @HR = 0 EXECUTE @hr = sp_OAMethod @objFileSystem, 'CreateTextFile', @objTextStream OUT, @Ds_Arquivo, 2, True IF @HR = 0 SELECT @objErrorObject = @objTextStream, @strErrorMessage = 'writing to the file "' + @Ds_Arquivo + '"' IF @HR = 0 EXECUTE @hr = sp_OAMethod @objTextStream, 'Write', NULL, @String IF @HR = 0 SELECT @objErrorObject = @objTextStream, @strErrorMessage = 'closing the file "' + @Ds_Arquivo + '"' IF @HR = 0 EXECUTE @hr = sp_OAMethod @objTextStream, 'Close' IF @hr <> 0 BEGIN DECLARE @Source VARCHAR(255), @Description VARCHAR(255), @Helpfile VARCHAR(255), @HelpID INT EXECUTE sp_OAGetErrorInfo @objErrorObject, @source OUTPUT, @Description OUTPUT, @Helpfile OUTPUT, @HelpID OUTPUT SELECT @strErrorMessage = 'Error whilst ' + COALESCE(@strErrorMessage, 'doing something') + ', ' + COALESCE(@Description, '') RAISERROR (@strErrorMessage,16,1) END EXECUTE sp_OADestroy @objTextStream EXECUTE sp_OADestroy @objTextStream END |
stpWrite_File_V2_FSO: Script that writes the contents of a variable or text to a file on disk. If the directory doesn't exist, it will create it.
Usage: EXEC dbo.stpEscrita_Arquivo_V2_FSO('C:\Teste.txt', 'Write Test in File')
stpCria_Directory_FSOUsage: EXEC dbo.stpEscrita_Arquivo_V2_FSO('C:\Teste.txt', 'Write Test in File')
Source code:
Transact-SQL
| 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 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 | CREATE PROCEDURE [dbo].[stpEscreve_Arquivo_V2_FSO] ( @String VARCHAR(MAX), @Ds_Arquivo VARCHAR(1501) ) AS BEGIN SET NOCOUNT ON DECLARE @objFileSystem INT, @objTextStream INT, @objFolderExists INT, @objErrorObject INT, @strErrorMessage VARCHAR(1000), @Folder VARCHAR(500) = REVERSE(RIGHT(REVERSE(@Ds_Arquivo), (LEN(@Ds_Arquivo) - CHARINDEX('\', REVERSE(@Ds_Arquivo), 1)) + 1)), @hr INT SELECT @strErrorMessage = 'opening the File System Object' EXECUTE @hr = sp_OACreate 'Scripting.FileSystemObject', @objFileSystem OUT IF @hr = 0 SELECT @objErrorObject = @objFileSystem, @strErrorMessage = 'Checking if the directory "' + @Folder + '" exists' IF @hr = 0 EXECUTE @hr = sp_OAMethod @objFileSystem, 'FolderExists', @objFolderExists OUT, @Folder -- Folder does not exists. Need to create it IF (@objFolderExists = 0) BEGIN IF @hr = 0 SELECT @objErrorObject = @objFileSystem, @strErrorMessage = 'Creating folder "' + @Folder + '"' IF @hr = 0 EXECUTE @hr = sp_OAMethod @objFileSystem, 'CreateFolder', @objFolderExists OUT, @Folder END IF @hr = 0 SELECT @objErrorObject = @objFileSystem, @strErrorMessage = 'Creating file "' + @Ds_Arquivo + '"' IF @hr = 0 EXECUTE @hr = sp_OAMethod @objFileSystem, 'CreateTextFile', @objTextStream OUT, @Ds_Arquivo, 2, True IF @hr = 0 SELECT @objErrorObject = @objTextStream, @strErrorMessage = 'writing to the file "' + @Ds_Arquivo + '"' IF @hr = 0 EXECUTE @hr = sp_OAMethod @objTextStream, 'Write', NULL, @String IF @hr = 0 SELECT @objErrorObject = @objTextStream, @strErrorMessage = 'closing the file "' + @Ds_Arquivo + '"' IF @hr = 0 EXECUTE @hr = sp_OAMethod @objTextStream, 'Close' EXECUTE sp_OADestroy @objFileSystem IF @hr <> 0 BEGIN DECLARE @Source VARCHAR(255), @Description VARCHAR(255), @Helpfile VARCHAR(255), @HelpID INT EXECUTE sp_OAGetErrorInfo @objErrorObject, @Source OUTPUT, @Description OUTPUT, @Helpfile OUTPUT, @HelpID OUTPUT SELECT @strErrorMessage = 'Error whilst ' + COALESCE(@strErrorMessage, 'doing something') + ', ' + COALESCE(@Description, '') RAISERROR (@strErrorMessage,16,1) END END |
stpCria_Directory_FSO: Stored Procedure that creates a directory on disk. If it already exists, the Stored Procedure will ignore it.
Usage: EXEC dbo.stpCria_Diretorio_FSO('C:\Dirceu')
stpInfo_File_InfoUsage: EXEC dbo.stpCria_Diretorio_FSO('C:\Dirceu')
Source code:
Transact-SQL
| 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 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 | CREATE PROCEDURE [dbo].[stpCria_Diretorio_FSO] ( @Folder VARCHAR(500) ) AS BEGIN DECLARE @objFileSystem INT, @objFolderExists INT, @objErrorObject INT, @strErrorMessage VARCHAR(1000), @hr INT SET NOCOUNT ON SELECT @strErrorMessage = 'opening the File System Object' EXECUTE @hr = sp_OACreate 'Scripting.FileSystemObject', @objFileSystem OUT IF @hr = 0 SELECT @objErrorObject = @objFileSystem, @strErrorMessage = 'Checking if the folder "' + @Folder + '" exists' IF @hr = 0 EXECUTE @hr = sp_OAMethod @objFileSystem, 'FolderExists', @objFolderExists OUT, @Folder IF (@objFolderExists = 0) BEGIN IF @hr = 0 SELECT @objErrorObject = @objFileSystem, @strErrorMessage = 'Creating folder "' + @Folder + '"' IF @hr = 0 EXECUTE @hr = sp_OAMethod @objFileSystem, 'CreateFolder', @objFolderExists OUT, @Folder END EXECUTE sp_OADestroy @objFileSystem EXECUTE sp_OADestroy @objFolderExists IF @hr <> 0 BEGIN DECLARE @Source VARCHAR(255), @Description VARCHAR(255), @Helpfile VARCHAR(255), @HelpID INT EXECUTE sp_OAGetErrorInfo @objErrorObject, @Source OUTPUT, @Description OUTPUT, @Helpfile OUTPUT, @HelpID OUTPUT SELECT @strErrorMessage = 'Error whilst ' + COALESCE(@strErrorMessage, 'doing something') + ', ' + COALESCE(@Description, '') EXECUTE sp_OADestroy @objErrorObject RAISERROR (@strErrorMessage,16, 1) END END |
stpInfo_File_Info: Stored Procedure that returns file information, such as type, size, creation date, and modification.
Use: EXEC CLR.dbo.stpInformacoes_Arquivo_FSO ('C: \ Teste.txt')
stpMove_File_FileUse: EXEC CLR.dbo.stpInformacoes_Arquivo_FSO ('C: \ Teste.txt')
Source code:
Transact-SQL
| 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 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 | CREATE PROCEDURE [dbo].stpInformacoes_Arquivo_FSO(@strArquivo VARCHAR(255)) AS BEGIN DECLARE @hr INT, @objFileSystem INT, @objFile INT, @ErrorObject INT, @ErrorMessage VARCHAR(255), @Path VARCHAR(255),-- @ShortPath VARCHAR(255), @Type VARCHAR(100), @DateCreated DATETIME, @DateLastAccessed DATETIME, @DateLastModified DATETIME, @Attributes INT, @size INT SET nocount ON SELECT @hr = 0, @ErrorMessage = 'opening the file system object ' EXEC @hr = sp_OACreate 'Scripting.FileSystemObject', @objFileSystem OUT IF @hr = 0 SELECT @ErrorMessage = 'accessing the file ''' + @strArquivo + '''', @ErrorObject = @objFileSystem IF @hr = 0 EXEC @hr = sp_OAMethod @objFileSystem, 'GetFile', @objFile OUT, @strArquivo IF @hr = 0 SELECT @ErrorMessage = 'getting the attributes of ''' + @strArquivo + '''', @ErrorObject = @objFile IF @hr = 0 EXEC @hr = sp_OAGetProperty @objFile, 'Path', @Path OUT IF @hr = 0 EXEC @hr = sp_OAGetProperty @objFile, 'ShortPath', @ShortPath OUT IF @hr = 0 EXEC @hr = sp_OAGetProperty @objFile, 'Type', @Type OUT IF @hr = 0 EXEC @hr = sp_OAGetProperty @objFile, 'DateCreated', @DateCreated OUT IF @hr = 0 EXEC @hr = sp_OAGetProperty @objFile, 'DateLastAccessed', @DateLastAccessed OUT IF @hr = 0 EXEC @hr = sp_OAGetProperty @objFile, 'DateLastModified', @DateLastModified OUT IF @hr = 0 EXEC @hr = sp_OAGetProperty @objFile, 'Attributes', @Attributes OUT IF @hr = 0 EXEC @hr = sp_OAGetProperty @objFile, 'size', @size OUT IF @hr <> 0 BEGIN DECLARE @Source VARCHAR(255), @Description VARCHAR(255), @Helpfile VARCHAR(255), @HelpID INT EXECUTE sp_OAGetErrorInfo @ErrorObject, @Source OUTPUT, @Description OUTPUT, @Helpfile OUTPUT, @HelpID OUTPUT SELECT @ErrorMessage = 'Error whilst ' + @ErrorMessage + ', ' + @Description RAISERROR (@ErrorMessage,16,1) END EXEC sp_OADestroy @objFileSystem EXEC sp_OADestroy @objFile SELECT [Path] = @Path, [ShortPath] = @ShortPath, [Type] = @Type, [DateCreated] = @DateCreated, [DateLastAccessed] = @DateLastAccessed, [DateLastModified] = @DateLastModified, [Attributes] = @Attributes, [Size] = @size RETURN @hr END |
stpMove_File_File: Stored Procedure that physically moves a file from one directory to another.
Use: EXEC CLR.dbo.stpMove_Arquivo_FSO 'C: \ Teste.txt', 'C: \ Files \ Novo_Arquivo.log', 1 - It will be overwritten if it already exists (3rd parameter)
Use: EXEC CLR.dbo.stpMove_Arquivo_FSO 'C: \ Teste.txt', 'C: \ Files \ Novo_Arquivo.log', 1 - It will be overwritten if it already exists (3rd parameter)
Source code:
Transact-SQL
| 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 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 | CREATE PROCEDURE [dbo].[stpMove_Arquivo_FSO] ( @strOrigem VARCHAR(MAX), @strDestino VARCHAR(MAX), @sobrescrever INT = 0 ) AS BEGIN IF (dbo.fncArquivo_Existe(@strOrigem) = 1 AND @strOrigem != @strDestino) BEGIN DECLARE @hr INT, @objFileSystem INT, @source VARCHAR(250), @description VARCHAR(2000) EXEC @hr = sp_OACreate 'Scripting.FileSystemObject', @objFileSystem OUT IF @hr <> 0 BEGIN EXEC sp_OAGetErrorInfo @objFileSystem, @source OUT, @description OUT RAISERROR('Object Creation Failed 0x%x, %s, %s',16,1,@hr,@source,@description) RETURN END IF (dbo.fncArquivo_Existe(@strDestino) = 1) IF (@sobrescrever = 1) EXEC dbo.stpApaga_Arquivo_FSO @strDestino ELSE BEGIN PRINT 'O arquivo de destino já existe e o parâmetro sobrescrever foi definido como NÃO' RETURN END EXEC @hr = sp_OAMethod @objFileSystem, 'CopyFile', NULL, @strOrigem, @strDestino IF (@hr <> 0) BEGIN EXEC sp_OAGetErrorInfo @objFileSystem, @source OUT, @description OUT EXEC sp_OADestroy @objFileSystem RAISERROR('Method Failed 0x%x, %s, %s',16, 1, @hr, @source, @description) PRINT @source PRINT @description RETURN END ELSE BEGIN EXEC dbo.stpApaga_Arquivo_FSO @strOrigem END EXEC sp_OADestroy @objFileSystem END ELSE PRINT 'O arquivo de origem não existe ou é igual ao arquivo de destino' END |
See how easy and simple it is? Now you can play with files without disrupting having to create functions and stored procedures using C # and compiling the database CLR (If you want to use CLR for file manipulation instead of OLE Automation, WHAT I RECOMMEND, access the post How to list, read, write, copy and move files with CLR (C #)).
As a toast, I'll pass you one more stored procedure, which has the function of starting from a file path, separating the directory and the file into two distinct variables:
stpServerFileName:
Transact-SQL
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | USE [CLR] GO CREATE PROCEDURE [dbo].[stpArquivo_Separa_Nome_Diretorio] (@strArquivo VARCHAR(1000), @str_DIR VARCHAR(500) OUT, @str_FILE VARCHAR(500) OUT) AS BEGIN SET NOCOUNT ON SELECT @str_DIR = REVERSE(RIGHT(REVERSE(@strArquivo), (LEN(@strArquivo) - CHARINDEX('\', REVERSE(@strArquivo), 1)) + 1)) SELECT @str_FILE = REVERSE(LEFT(REVERSE(@strArquivo), CHARINDEX('\', REVERSE(@strArquivo), 1) - 1)) END |
How to use:
Transact-SQL
| 1 2 3 4 5 6 7 8 9 10 11 | DECLARE @str_DIR VARCHAR(500), @str_FILE VARCHAR(500) EXEC CLR.dbo.stpArquivo_Separa_Nome_Diretorio @strArquivo = '\\servidor\Pasta 1\Diretorio 2\Arquivos\Nome do Arquivo - 20140614.xlsx', -- varchar(1000) @str_DIR = @str_DIR OUTPUT, -- varchar(500) @str_FILE = @str_FILE OUTPUT -- varchar(500) PRINT @str_DIR -- Vai imprimir na tela "\\servidor\Pasta 1\Diretorio 2\Arquivos\" PRINT @str_FILE -- Vai imprimir na tela "Nome do Arquivo - 20140614.xlsx" |
That's all, folks!
See you in the next post.
