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.
