Olá pessoal…
Hoje vou mostrar uma procedure criada no SQL Server para criar arquivos txt via SQL Server, a procedure é a spWriteStringToFile.
SQL Server
Essa procedure já existia (se quiser saber mais, olhe nos links de referência), porém eu deixei alguns comentários em português, e montei um exemplo.
Abaixo o exemplo desenvolvido para criar o arquivo texto.
1 2 3 4 5 6 7 8 9 10 11 12 | --Declara as variáveisDECLARE @Texto Varchar(800)DECLARE @Enter Varchar(2)--Seta os conteúdosSET @Enter = Char(13)+Char(10)SET @Texto = 'Linha 1, teste de escrita'+@EnterSET @Texto += 'Linha 2, Daniel Atilio'+@EnterSET @Texto += 'Linha 3, Terminal de Informação'+@Enter--Cria o arquivo textoexec spWriteStringToFile @texto, 'E:\', 'teste.txt' |
Abaixo um print do arquivo gerado.
Arquivo gerado
Abaixo o código fonte completo da procedure.
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 | --Exclui procedureIF OBJECT_ID('dbo.spWriteStringToFile', 'P') IS NOT NULL DROP PROCEDURE [dbo].[spWriteStringToFile]GO--Recria a procedureCREATE PROCEDURE spWriteStringToFile ( @String Varchar(max), @Path VARCHAR(255), @FileName VARCHAR(100))AS--Declara as variáveisDECLARE @objFileSystem int, @objTextStream int, @objErrorObject int, @strErrorMessage Varchar(1000), @Command varchar(1000), @hr int, @FileAndPath varchar(80)SET NOCOUNT ON--Reconfigura opções do SQL ServerEXEC sp_configure 'show advanced options', 1RECONFIGUREEXEC sp_configure 'Ole Automation Procedures', 1RECONFIGURE--Abrindo o Objetoselect @strErrorMessage='Abrindo File System Object'EXECUTE @hr = sp_OACreate 'Scripting.FileSystemObject' , @objFileSystem OUT--Tratamento para string nulaSET @String = Iif(@String = NULL, '', @String)--Define o diretório e a pastaSelect @FileAndPath = @Path+'\'+@FileName--Criando o arquivoif @hr = 0 Select @objErrorObject = @objFileSystem, @strErrorMessage = 'Criando arquivo "'+@FileAndPath+'"'if @hr=0 execute @hr = sp_OAMethod @objFileSystem, 'CreateTextFile', @objTextStream OUT, @FileAndPath, 2, True--Escrevendo texto no arquivoif @hr=0 Select @objErrorObject = @objTextStream, @strErrorMessage = 'Escrevendo no arquivo "'+@FileAndPath+'"'if @hr=0 execute @hr = sp_OAMethod @objTextStream, 'Write', Null, @String--Fechando o aruqivoif @hr=0 Select @objErrorObject = @objTextStream, @strErrorMessage = 'Fechando e finalizando o arquivo "'+@FileAndPath+'"'if @hr=0 execute @hr = sp_OAMethod @objTextStream, 'Close'--Se o ponteiro for diferente de 0 (houve errosif @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='Erro ' +coalesce(@strErrorMessage,'...') +', '+coalesce(@Description,'') raiserror (@strErrorMessage,16,1) end--Finaliza a ProcedureEXECUTE sp_OADestroy @objTextStreamEXECUTE sp_OADestroy @objTextStream |
Referências:
– Simple Talk
– Stack Overflow
Bom pessoal, por hoje é só.
Abraços e até a próxima.
Atilio, muito bom o artigo. Mas você poderia detalhar melhor ou até mesmo demonstrar se é possivel exportar tabelas ou campos para esse arquivo texto? Obrigado.
Grande Betinho, tudo bem?
Então jovem, vou fazer um post futuramente de como utilizar a ferramenta bcp do SQL Server, e nele irei detalhar como exportar e importar tabelas.
Um grande abraço.
Olá bom dia, Estou usando o seu exemplo acima, porem ao gerar o arquivo ele usa uma codificação UCS-2, como poderia alterar para ANSI?
Bom dia Adair.
Eu nunca precisei fazer essa modificação, mas segue uma discussão que encontrei de algumas pessoas no exterior comentando sobre o assunto – https://www.sqlservercentral.com/Forums/PrintTopic1849293.aspx
Um grande abraço.
Olá Dan,
Estou usando sua procedure para emitir um relatório de volumetria, mas ao executar aparece a msg que ocorreu erro ao criar o arquivo [nome do arquivo], mas não dá detalhes. Seria algo de permissão?
Estou usando uma base local para alguns testes.
Bom dia Denise, tudo bem?
Eu já vi um erro parecido, quando a pasta estava com algum acento ou espaço, tipo um caracter especial.
Tente gerar na raiz do D: ou E: da sua máquina (geralmente o C: é bloqueado a raiz para criação de arquivo, ai você tem que liberar via Windows).
Um grande abraço.
Bom dia Dan, estava justamente procurando algo neste sentido, de gerar um arquivo, pois preciso fazer uma auditoria em um scritp. Porem ao copiar seu exemplo e rodar no sql acusou a mensagem: Could not find stored procedure ‘spWriteStringToFile’.
utilizo O SQL SERVER MANAGMENT STUDIO VERSAO 15.0.18386.0
Voce teria alguma dica?
Obrigado
–Declara as variáveis
DECLARE @Texto Varchar(800)
DECLARE @Enter Varchar(2)
–Seta os conteúdos
SET @Enter = Char(13)+Char(10)
SET @Texto = ‘Linha 1, teste de escrita’+@Enter
SET @Texto += ‘Linha 2, Daniel Atilio’+@Enter
SET @Texto += ‘Linha 3, Terminal de Informação’+@Enter
–Cria o arquivo texto
exec spWriteStringToFile @texto, ‘E:\’, ‘teste.txt’
Bom dia Handrei.
Então o código completo da Procedure é o segundo trecho disponibilizado no artigo, logo após o parágrafo: “Abaixo o código fonte completo da procedure.”
Um grande abraço.
Criamos o procedure. Deu certo !!!!!
Opa, entendi.
Um grande abraço.
Bom dia Dan,
Existe como fazer um append sobre um arquivo já existente ?
Obrigado
Bom dia Handrei.
Eu nunca precisei, mas pesquisando no Google sobre “increment text in file using sql server”, encontrei um link onde o pessoal falou para utilizar comandos via MS-DOS com xp_cmdshell.
Segue o link que encontrei: https://dba.stackexchange.com/questions/125475/how-to-read-and-increment-a-number-in-text-file-using-tsql
Um grande abraço.
Muitissimo obrigado