Error executing the 'bcp' command within a procedure

2

When I run the command:

DECLARE @cmd varchar(1000);
SET @cmd = 'bcp "select [colunas] FROM [usuario].dbo.[tabela] " queryout "\***\SQLServer\text.txt" -c -UTF8 -T -S***';
EXEC xp_cmdshell @cmd;

It works. But when I run the same command inside a procedure I get an output and nothing else, what should I do to does this command work correctly in the procedure?

PROCEDURE:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[SP_EXPORTAR_ARQUIVO] 
        @p_funcionalidade nvarchar(50), 
        @ano nvarchar(4) = NULL
AS
BEGIN
    DECLARE
    @sqlParaArquivo nvarchar(4000),
    @localArquivo varchar(1000),
    @parametros varchar(1000),
    @nomeArquivo varchar(1000),
    @SERVIDOR varchar(1000),
    @BANCO varchar(1000),
    @comando varchar(1000),
    @cmd varchar(4000);
SELECT @localArquivo = LOCAL_ARQUIVO, @sqlParaArquivo = CONSULTA, @SERVIDOR  = SERVIDOR, @BANCO = BANCO FROM usuario_des.dbo.PARAMETROS_EXPORTAR_ARQUIVO where FUNCIONALIDADE = @p_funcionalidade;
    set @nomeArquivo = COALESCE(@p_funcionalidade,'') + COALESCE(@ano,'') + '.txt';
    set @comando = 'bcp ';
    set @localArquivo = '"'+ COALESCE(@localArquivo,'')+'\' + COALESCE(@nomeArquivo,'') + '"';
    set @parametros = 'queryout ' + COALESCE(@localArquivo,'')  +' -c -UTF8 -T -S'+@SERVIDOR +' -d'+@BANCO;
    set @cmd = @comando + ' "'+COALESCE(@sqlParaArquivo,'') + '" '+ COALESCE(@parametros,'');
    SELECT @cmd ;
    BEGIN TRY
        EXEC master..xp_cmdshell @cmd;
    END TRY
    BEGIN CATCH
        SELECT 
            ERROR_NUMBER() AS ErrorNumber
            ,ERROR_MESSAGE() AS ErrorMessage;
    END CATCH;  
END

Return:

usage: bcp {dbtable | query} {in | out | queryout | format} datafile
  [-m maxerrors]            [-f formatfile]          [-e errfile]
  [-F firstrow]             [-L lastrow]             [-b batchsize]
  [-n native type]          [-c character type]      [-w wide character type]
  [-N keep non-text native] [-V file format version] [-q quoted identifier]
  [-C code page specifier]  [-t field terminator]    [-r row terminator]
  [-i inputfile]            [-o outfile]             [-a packetsize]
  [-S server name]          [-U username]            [-P password]
  [-T trusted connection]   [-v version]             [-R regional enable]
  [-k keep null values]     [-E keep identity values]
  [-h "load hints"]         [-x generate xml format file]
  [-d database name]
NULL
    
asked by anonymous 28.05.2014 / 20:35

1 answer

1

I found the problem .. When I search the select from the table that I'm going to run, in it I formatted the query.

that is, there were some \ n in the command, so that it did not run. So he introduced me to the parameters of the command

    
28.05.2014 / 22:25