2
When I execute 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 a output and nothing else, what should I do to make this command work properly in the past?
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
Is there any way to put the code of the previous one? And it seems that you have replaced some secret elements with an asterisk. Could you use another symbol? Gets a little fuzzy since the asterisk is used in select and for block comments.
– utluiz