xp_cmdshell

[eriva_br 7]

Caracteristicas:

Nome: XP_CMDSHELL

Localização: Banco de dados Master

Tipo: Extended Procedure

Descrição: Esta procedure executa uma sequencia de comandos em string do tipo shell no computador onde o sql server está instalado.

Utilidades: listar diretórios; criar, copiar, apagar arquivos; enviar mensagens, etc

 

Parâmetros:

'command_string', obrigatório, é o comando a ser executado

no_output, opcional, se colocado indica que não será retornado mensagem de saída na execução

 

Retornos:

retona 0 ou 1: 0-sucesso ou 1-Falha

 

Observações:

Obs1.: esta proc trabalha de forma sincrona, os comandos colocados abaixo dela só serão executados após o término de sua execução, ao contrário do comando shell do vb6 que trabalha de forma assincrona, ao ser executado, mesmo sem ter finalizado o comando já é liberado para a linha subsequente.

Obs2.: verificar segurança, tomar cuidados com SQL Injection, dica do nosso amigo Micox

http://www.macoratti.net/sql_inj.htm

http://www.google.com.br/search?hl=pt-BR&a...ta=lr%3Dlang_pt

 

Exemplos de uso:

exemplo 1: copiando um arquivo

declare @retorno INT  EXEC @retorno = MASTER..xp_cmdshell 'copy C:\Lixo\teste.txt C:\Lixo\testeBKP.txt'  select CASE WHEN @retorno = 0 THEN 'COPIADO COM SUCESSO' ELSE 'FALHA NO COMANDO' END as Retorno

exemplo 2: listando os arquivos de uma pasta

declare @retorno int  EXEC @retorno = master..xp_cmdshell 'dir C:\lixo\'  if @retorno = 0	  print 'comando executado com EXITO'  else	  print 'FALHA no comando'

para testar esse exemplo verificando uma execução com sucesso e uma com falha, teste passando o caminho correto do arquivo e depois teste definindo o caminho incorretamente

 

 

exemplo 3: deletando um arquivo

EXEC MASTER..XP_CMDSHELL 'del C:\Lixo\teste.txt'

exemplo 4: enviando mensagens

EXEC master..xp_cmdshell 'net send /domain:SQL_USERS ''SQL Server será desligado em em 1 minuto.'

exemplo 5: escrevendo em um arquivo texto

DECLARE @arqTexto VARCHAR(8000),	  @caminho VARCHAR(255),		  @retEcho INT --variavel para verificar se o comando foi executado com exito ou ocorreu alguma falha  	  SET @caminho = 'C:\Lixo\teste.txt'  	  SET @arqTexto = 'ECHO Registro;CAMPO1;CAMPO2;CAMPO3 ' + CONVERT(VARCHAR(30),GETDATE()) +  ' >> ' + @caminho  	  --remove caracteres não aceitos pelo comando ECHO, veja no final do post comentário	  select @arqTexto = dbo.FU_CONVERTE_TXT(@arqTexto)  	  --escreve no arquivo txt, se ele naum existir será criado, se existir será adicionado (incrementado) a partir da última linha	  EXEC @retEcho = MASTER..XP_CMDSHELL @arqTexto, NO_OUTPUT --NO_OUTPUT indica que o comando não irá retornar alguma possível mensagem na sua execução	  IF (@retEcho <> 0)	  BEGIN		  --caso ocorrer algum erro podemos retornar uma mensagem		  SELECT 'ocorreu erro, verifique se o diretório existe' as Falha, @retEcho as retEcho	  END

exemplo 6: escrevendo arquivo texto, vamos escrever um arquivo texto com alguns campos da tabela authors do banco de dados pubs, através de um loop

--definindo o banco pubs  use pubs;  --definindo variaveis  declare @arqTexto varchar(8000),	  @au_fname varchar(50),	  @au_lname varchar(50),	  @caminho varchar(255),	  @au_id varchar(20),	  @retEcho INT --variavel para verificar se o comando foi executado com exito ou ocorreu alguma falha    --definindo o caminho e arquivo que será escrito, OBS: este caminho deve ser no servidor onde estiver instalado o SQL Server  set @caminho = 'C:\Lixo\teste.txt'  --setando o menor id para verificação do loop  select @au_id = min(au_id) from authors  --while da tabela authors  while @au_id is not null  begin	  --buscando os valores dos campos e adicionando nas variaveis	  select @au_id = au_id, @au_lname = au_lname, @au_fname = au_fname from authors	  where au_id = @au_id	  --setando o menor id para verificação do loop	  select @au_id = min(au_id) from authors	  where au_id > @au_id	  --definindo a linha que será escrita no arquivo txt	  SET @arqTexto = 'ECHO Registro;' + @au_id + ';' + @au_lname + ';' + @au_fname +  ' >> ' + @caminho	  --escreve no arquivo txt, se ele naum existir será criado, se existir será adicionado (incrementado) a partir da última linha	  exec @retEcho = MASTER..XP_CMDSHELL @arqTexto	  IF (@retEcho <> 0)	  BEGIN		  --caso ocorrer algum erro podemos retornar uma mensagem		  select 'ocorreu erro, verifique se o diretório existe' as Falha, @retEcho as retEcho		  --parar o loop em caso de erro		  break	  END  end

 

 

Atenção:

Para escrever arquivo texto, cuidado com os caracteres especiais, pois se for um destes caracteres no comando a linha inteira não é escrita, dai imagina um arquivo texto de exportação faltando uma linha lá no meio do arquivo... como diria o Silvio Luiz, "o que que eu vou dizer lá no 'escritório'!"

Os caracteres que verifiquei até agora que não são aceitos pelo comando ECHO estão na função abaixo que fiz para trocar tais caracteres, se ver mais algum por favor nos avise:

CREATE FUNCTION FU_TRANSFORMA_CARACTERES (@Texto VARCHAR(8000))  RETURNS VARCHAR(8000)  AS  BEGIN  SET @Texto = UPPER(REPLACE(@Texto,'Á','A'))  SET @Texto = UPPER(REPLACE(@Texto,'Á','A'))  SET @Texto = UPPER(REPLACE(@Texto,'Ã','A'))  SET @Texto = UPPER(REPLACE(@Texto,'Â','A'))  SET @Texto = UPPER(REPLACE(@Texto,'Ó','O'))  SET @Texto = UPPER(REPLACE(@Texto,'Ô','O'))  SET @Texto = UPPER(REPLACE(@Texto,'Õ','O'))  SET @Texto = UPPER(REPLACE(@Texto,'Ú','U'))  SET @Texto = UPPER(REPLACE(@Texto,'Ü','U'))  SET @Texto = UPPER(REPLACE(@Texto,'Í','I'))  SET @Texto = UPPER(REPLACE(@Texto,'É','E'))  SET @Texto = UPPER(REPLACE(@Texto,'Ê','E'))  SET @Texto = UPPER(REPLACE(@Texto,'Ç','C'))  SET @Texto = REPLACE(@Texto,'&','e')  SET @Texto = REPLACE(@Texto,'"',' ')    RETURN @Texto  END

AUTOR: "eriva_br"

 

Dúvidas, criticas, contribuições, correções e adições seram bem vindas.

[vlacerda 0]

exportação de arquivos.

 

EXEC master.DBO.xp_cmdshell

 

'bcp "SELECT Columns FROM Table" QUERYOUT C:\teste.TXT -S DataSource -U User -P Password -t ; -r \n -w'

 

 

 

Espero ter ajudado.