Introducción
El xp_cmdshell es un procedimiento extendido muy potente que se lo utiliza para ejecutar la línea de comandos de Windows (cmd). Este comando es bastante útil para ejecutar tareas en el sistema operativo tales como copiar archivos, crear carpetas, compartir carpetas, etc. mediante T-SQL.
En este nuevo artículo, mostraremos algunos ejemplos útiles sobre cómo usarlo.
En esta ocasión, mostraremos cómo realizar las siguientes tareas:
- Cómo habilitar el procedimiento extendido xp_cmdshell
- Cómo realizar copiar copias de seguridad de una carpeta de origen a otra de destino utilizando xp_cmdshell
- Cómo guardar la ruta de acceso completa de archivos de una carpeta en una tabla de SQL Server
- Cómo ejecutar cmdlets de PowerShell utilizando T-SQL y xp_cmdshell
- Cómo conectarse a Azure en SSMS utilizando xp_cmdshell y sqlcmd
Requisitos
- SQL Server instalado. En este ejemplo, estamos utilizando la versión de SQL Server 2016, pero puede trabajar con SQL Server 2005 o versiones superiores.
Iniciando los ejemplos
Cómo habilitar el procedimiento extendido xp_cmdshell
Primeramente, habilitaremos el procedimiento extendido xp_cmdshell. Necesitaremos verificar si las opciones avanzadas de SQL Server están habilitadas. Para ello, vamos a utilizar el siguiente procedimiento en SQL Server Management Studio (SSMS):
|
1 2 3 |
USE master; GO EXEC sp_configure 'show advanced option' |
Cuando se ejecuta el comando, si el valor de configuración al ejecutar el procedimiento es igual a 0, significa que las Opciones avanzadas no están habilitadas.
Para activar las opciones avanzadas, configure esta opción avanzada a 1. El siguiente ejemplo muestra cómo hacerlo:
|
1 2 3 4 |
USE master; GO EXEC sp_configure 'show advanced option', '1'; RECONFIGURE WITH OVERRIDE; |
Xp_cmdshell es una de las opciones avanzadas, que ahora puede habilitar cuando la opción avanzada está en 1. El siguiente ejemplo muestra como habilitarlo:
|
1 2 3 |
EXEC sp_configure 'xp_cmdshell', 1; GO RECONFIGURE; |
Cómo realizar copiar copias de seguridad de una carpeta de origen a otra de destino utilizando xp_cmdshell
En el siguiente ejemplo, se muestra cómo copiar varias copias de seguridad de una carpeta de origen a otra de destino. Para este ejemplo, tenemos varias copias de seguridad en la ruta c:\backup:
Las siguientes instrucciones T-SQL realizarán las copias los archivos de la carpeta c:\Backup a la carpeta c:\Shared:
|
1 |
xp_cmdshell 'copy c:\backup c:\shared'; |
La salida del comando será la siguiente:
Como puede ver, el comando copiar cmd copia los archivos a la carpeta compartida:
Cómo almacenar ruta de acceso completa de archivos de una carpeta a una tabla de SQL Server
El ejemplo siguiente, almacenará en una tabla de SQL Server todas las rutas de las imágenes almacenadas en un archivo especificado.
Por ejemplo, tengo las siguientes imágenes en la carpeta c: \ image:
Lo que deseamos hacer, es almacenar en una tabla SQL las rutas completas de las imágenes como sigue:
C:\images\1.jpg
C:\images\2.jpg
…etc.
Primeramente, almacenaremos todas las rutas de la carpeta en un archivo txt llamado path:
|
1 |
xp_cmdshell 'dir "c:\images" /s/b>c:\sql\path.txt'; |
Path.txt ahora incluirá todas las rutas completas de la imagen:
Es necesario tener una tabla para almacenar las rutas:
|
1 |
create table images(path varchar(80)) |
Para poder importar la información del archivo txt de la figura 6 en la tabla recién creada utilizaremos las siguientes frases:
|
1 |
xp_cmdshell 'bcp AdventureWorKs2016CTP3.dbo.images IN c:\sql\path.txt -c -T' |
Bcp es el comando utilizado para importar desde la ruta.txt a la tabla dbo.images en la base de datos AdventureWorks2016CTP3. -T se utiliza para conectarse mediante una conexión de confianza (la autenticación de Windows actual) y -c se utiliza para realizar operaciones con el tipo de caracteres.
Si realiza una selección en la tabla de imágenes, puede verificar que podemos importar los datos correctamente a SQL Server haciendo una selección:
|
1 |
select * from images |
La sentencia de SQL mostrará los siguientes resultados:
Vamos a crear una tabla con ID y una columna con las columnas path y image name:
|
1 2 3 4 5 6 7 8 9 10 11 |
CREATE TABLE [dbo].[pictures]( [id] [smallint] IDENTITY(1,1) NOT NULL, [path] [varchar](max) NULL, [imagename] [nchar](40) NULL, CONSTRAINT [PK_pictures] PRIMARY KEY CLUSTERED ( [id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO |
Finalmente, importaremos los datos de las imágenes de la tabla a las imágenes de la tabla:
|
1 2 3 |
insert into pictures (imagename,path) select replace(path,'c:/images/','') as imagename,path from images |
Ahora podrá visualizar el nombre de la ruta y la imagen en una tabla:
|
1 |
select * from pictures |
Cómo ejecutar PowerShell en T-SQL usando xp_cmdshell
PowerShell puede utilizarse para crear scripts y automatizar tareas en SQL Server, SharePoint, Window Server, Azure y varias otras tecnologías de Microsoft. PowerShell actualmente está disponible en también en Linux. Se pueden utilizar bucles, operadores para crear scripts potentes para automatizar las tareas con PowerShell.
Por ejemplo, estos cmdlets le permiten realizar copias de seguridad de la fuente c:\sq \ en la carpeta de destino de PowerShell:
copy-item “C:\sql\source” -Destination “C:\sql\destination” -Recurse
La carpeta de destino tendrá ahora la carpeta de origen con las copias de seguridad:
Si se desea ejecutar el mismo cmdlet de PowerShell con xp_cmdshell en SSMS, se deben ejecutar las siguientes sentencias T-SQL:
xp_cmdshell ‘powershell -command “copy-item “C:\sql\source” -Destination “C:\sql\destination” -Recurse’
En la sentencia anterior, se está invocando a PowerShell y ejecutando el comando para copiar todos los archivos y carpetas de origen a destino.
El ejemplo mostrado a continuación, demuestra como ejecutar una secuencia de comandos de PowerShell utilizando xp_cmdshell. En primer lugar, crearemos un script llamado script.ps1 con el siguiente contenido:
File_name: script.ps1
copy-item “C:\sql\source” -Destination “C:\sql\destination” –Recurse
Para invocar a un script de PowerShell en xp_cmdshell, utilizaremos las siguientes frases:
xp_cmdshell ‘powershell -command “C:\\sql\\script.ps1″‘
xp_cmdshell and SQL Server PowerShell
A veces es necesario conectarnos a SQL PowerShell (SQLPS). En el siguiente ejemplo se mostrará cómo mostrar la información de la base de datos en un archivo html utilizando xp_cmdshell. El siguiente ejemplo muestra cómo hacerlo.
Primero, cree una secuencia de comandos llamada scriptsql.ps1 con el siguiente contenido:
sqlserver:
cd sql\InstanceName\default\databases
get-childitem | ConvertTo-HTML | Out-File C:\sql\databases.htm
El script abre SQL Server entrando al nombre de instancia (cambiar al nombre de la instancia de SQL Server), predeterminado y bases de datos.
Get-childitem mostrará todas las bases de datos y convert-html out-file convertirá los resultados al formato HTML. El resultado se almacenará en un archivo denominado databases.htm:
Cómo conectarse a Azure en SSMS utilizando xp_cmdshell y sqlcmd
En muchas ocasiones es necesario invocar a Azure desde una base de datos local. Normalmente para conectarse a Azure, necesita otra conexión. Xp_cmdshell y sqlcmd son alternativas para crear procedimientos almacenados complejos y scripts que se conectan a bases de datos Azure y locales.
Primero crearemos un servidor SQL en el Portal de Azure. En el portal, vaya a More Services>SQL Servers:
Pulse Añadir para agregar un nuevo SQL Server:
Agregue un nuevo SQL Server, un inicio de sesión y una contraseña, cree un nuevo grupo de recursos y seleccione una ubicación y pulse Crear:
Una vez creado, espere unos minutos y actualice la lista de servidores SQL. En este ejemplo, el nombre de SQL Server es sqlshack:
Para habilitar la máquina local con SSMS, en Portal de Azure, haga clic en SQL Server, vaya a Firewall y presione Add client IP. Esto agregará la máquina local con SSMS. Una vez agregado, pulse Guardar:
Seleccione el servidor SQL creado en Azure Portal y seleccione Add client IP. En este sector, se mostrará el nombre de Azure SQL Server:
En su SQL Server local, cree un script de sql llamado sqlscript.sql con el siguiente contenido:
CREATE DATABASE SQLSHACKDB
El script, será utilizado para crear una base de datos denominada SQLSHACKDB en Azure.
Las siguientes sentencias T-SQL se utilizarán para ejecutar un script en Azure para crear una base de datos Azure:
|
1 |
xp_cmdshell 'sqlcmd -U daniel -S sqlshack.database.windows.net -P "myPWD" -d master -i "c:\sql\sqlscript.sql"' |
En la sentencia de comandos daniel y myPWD son el usuario y la contraseña creados en la Figura 13. Sqlshack.database.windows.net es el nombre del servidor que se muestra en la Figura 16. Estamos invocando a sqlcmd, que es la línea de comandos. Luego nos conectamos a la base de datos master en Azure y recibimos como entrada el script sqlscript.sql.
Si todo marcha bien, en el Portal de Azure, en SQL Databases, podrá ver la base de datos SQLSHACKDB creada:
Conclusión
En este artículo, explicamos cómo copiar datos de una carpeta de origen a otra de destino, también mostramos cómo copiar la ruta completa de archivos a una tabla de SQL. Finalmente, aprendimos cómo ejecutar PowerShell y cómo conectarnos a Azure usando xp_cmdshell.
Referencias
Para obtener más información, consulte estos enlaces:
Realizó consultorias para empresas gubernamentales, empresas petroleras, sitios web, revistas y universidades alrededor del mundo. Daniel participa regularmente en conferencias de SQL Server y blogs. Escribe material sobre SQL Server y exámenes de certificación.
También realiza la traducción de artículos de SQLShack al español
Ver todas las publicaciones de Daniel Calbimonte
- Cómo construir un cubo desde cero usando SQL Server Analysis Services (SSAS) - December 16, 2019
- Fecha de conversión de SQL - December 11, 2019
- Funciones frente a los procedimientos almacenados en SQL Server - October 14, 2019