Criando arquivo txt via SQL Server

Olá pessoal…

Hoje vou mostrar uma procedure criada no SQL Server para criar arquivos txt via SQL Server, a procedure é a spWriteStringToFile.

SQL Server

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.

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

Abaixo um print do arquivo gerado.

Arquivo gerado

Arquivo gerado

Abaixo o código fonte completo da procedure.

--Exclui procedure
IF OBJECT_ID('dbo.spWriteStringToFile', 'P') IS NOT NULL
    DROP PROCEDURE [dbo].[spWriteStringToFile]
GO

--Recria a procedure
CREATE PROCEDURE spWriteStringToFile (
	@String Varchar(max),
	@Path VARCHAR(255),
	@FileName VARCHAR(100)
)
AS

--Declara as variáveis
DECLARE 
	@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 Server
EXEC sp_configure 'show advanced options', 1
RECONFIGURE
EXEC sp_configure 'Ole Automation Procedures', 1
RECONFIGURE

--Abrindo o Objeto
select @strErrorMessage='Abrindo File System Object'
EXECUTE @hr = sp_OACreate  'Scripting.FileSystemObject' , @objFileSystem OUT

--Tratamento para string nula
SET @String = Iif(@String = NULL, '', @String)

--Define o diretório e a pasta
Select @FileAndPath = @Path+'\'+@FileName

--Criando o arquivo
if @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 arquivo
if @hr=0 Select @objErrorObject = @objTextStream, @strErrorMessage = 'Escrevendo no arquivo "'+@FileAndPath+'"'
if @hr=0 execute @hr = sp_OAMethod  @objTextStream, 'Write', Null, @String

--Fechando o aruqivo
if @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 erros
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='Erro '
            +coalesce(@strErrorMessage,'...')
            +', '+coalesce(@Description,'')
    raiserror (@strErrorMessage,16,1)
    end

--Finaliza a Procedure
EXECUTE sp_OADestroy @objTextStream
EXECUTE sp_OADestroy @objTextStream

Referências:
Simple Talk
Stack Overflow

Bom pessoal, por hoje é só.
Abraços e até a próxima.

Dan (Daniel Atilio)
Cristão de ramificação protestante. Especialista em Engenharia de Software pela FIB, graduado em Banco de Dados pela FATEC Bauru e técnico em informática pelo CTI da Unesp. Entusiasta de soluções Open Source e blogueiro nas horas vagas. Autor e mantenedor do portal Terminal de Informação.

13 Responses

  1. 1berto disse:

    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.

    • Dan_Atilio disse:

      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.

  2. Adair disse:

    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?

  3. Denise Muniz disse:

    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.

    • Dan_Atilio disse:

      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.

  4. handrei petersen disse:

    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’

  5. handrei petersen disse:

    Criamos o procedure. Deu certo !!!!!

  6. handrei petersen disse:

    Bom dia Dan,
    Existe como fazer um append sobre um arquivo já existente ?

    Obrigado

Deixe uma resposta para 1bertoCancelar resposta

Terminal de Informação