SQL Server proporciona una característica llamada xp_cmdshell que te permite ejecutar comandos de línea de comandos desde dentro de SQL Server. Esta característica puede ser útil cuando necesitas realizar tareas que no son compatibles directamente con T-SQL.

En una publicación de blog anterior, discutí cómo utilizar xp_cmdshell para ejecutar un script de PowerShell en SQL Server. Sin embargo, con la introducción de PowerShell V2, hay una forma más limpia y eficiente de lograr esto.

La versión mejorada utiliza el cmdlet ConvertTo-XML incorporado de PowerShell V2 con el parámetro –AsString. Dado que SQL Server entiende XML, podemos analizar el XML utilizando XQuery. Es importante tener en cuenta que aunque este enfoque funciona, aún se considera poco convencional y se recomienda ejecutar T-SQL en PowerShell en lugar de utilizar xp_cmdshell.

Aquí tienes un ejemplo de la versión mejorada:

-- Para permitir que se cambien las opciones avanzadas.
EXEC SP_CONFIGURE 'show advanced options', 1
GO

-- Para actualizar el valor configurado actualmente para las opciones avanzadas.
RECONFIGURE
GO

-- Para habilitar la característica.
EXEC SP_CONFIGURE 'xp_cmdshell', 1
GO

-- Para actualizar el valor configurado actualmente para esta característica.
RECONFIGURE
GO

/*
#COPIAR disk.ps1 a la carpeta Public
param ([string]$ComputerName = ".")

Get-WmiObject -computername "$ComputerName" Win32_LogicalDisk -filter "DriveType=3" | 
foreach { 
    add-member -in $_ -membertype noteproperty UsageDT $((Get-Date).ToString("yyyy-MM-dd"))
    add-member -in $_ -membertype noteproperty SizeGB $([math]::round(($_.Size/1GB),2))
    add-member -in $_ -membertype noteproperty FreeGB $([math]::round(($_.FreeSpace/1GB),2))
    add-member -in $_ -membertype noteproperty PercentFree $([math]::round((([float]$_.FreeSpace/[float]$_.Size) * 100),2)) -passThru 
} |
Select UsageDT, SystemName, DeviceID, VolumeName, SizeGB, FreeGB, PercentFree
*/

CREATE TABLE #output (line VARCHAR(255))
INSERT #output EXEC xp_cmdshell 'powershell -Command "C:\Users\Public\disk.ps1 | ConvertTo-Xml -NoTypeInformation -AsString"'
DELETE #output WHERE line IS NULL

DECLARE @doc VARCHAR(MAX)
SET @doc = ''

DECLARE @line VARCHAR(255)
DECLARE xml_cursor CURSOR FOR SELECT line FROM #output
OPEN xml_cursor
FETCH NEXT FROM xml_cursor INTO @line

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @doc = @doc + @line
    FETCH NEXT FROM xml_cursor INTO @line
END

CLOSE xml_cursor
DEALLOCATE xml_cursor

DROP TABLE #output

SELECT 
    item.REF.VALUE('(Property/text())[1]', 'datetime') AS UsageDT,
    item.REF.VALUE('(Property/text())[2]', 'nvarchar(128)') AS SystemName,
    item.REF.VALUE('(Property/text())[3]', 'nvarchar(128)') AS DeviceID,
    item.REF.VALUE('(Property/text())[4]', 'nvarchar(128)') AS VolumeName,
    item.REF.VALUE('(Property/text())[5]', 'nvarchar(128)') AS SizeGB,
    item.REF.VALUE('(Property/text())[6]', 'nvarchar(128)') AS FreeGB,
    item.REF.VALUE('(Property/text())[7]', 'nvarchar(128)') AS PercentFree
FROM 
    (SELECT CAST(@doc AS XML) AS feedXml) feeds(feedXml)
CROSS APPLY 
    feedXml.nodes('/Objects/Object') AS item(REF)

En este ejemplo, primero habilitamos la característica xp_cmdshell configurando las opciones avanzadas. Luego, creamos una tabla temporal para almacenar la salida de la ejecución del script de PowerShell. Utilizamos xp_cmdshell para ejecutar el script de PowerShell y almacenar el resultado en la tabla temporal.

A continuación, recuperamos la salida de la tabla temporal y la concatenamos en una variable de cadena única. Luego, analizamos el XML utilizando XQuery para extraer los valores deseados.

Finalmente, seleccionamos las columnas requeridas del XML analizado y mostramos el conjunto de resultados.

Aunque este enfoque funciona, es importante tener en cuenta que el uso de xp_cmdshell debe hacerse con precaución, ya que puede representar riesgos de seguridad si no se administra correctamente. Se recomienda ejecutar T-SQL en PowerShell siempre que sea posible.

Eso es todo por la publicación de blog de hoy. Espero que hayas encontrado útil esta información para ejecutar scripts de PowerShell en SQL Server utilizando xp_cmdshell. ¡Mantente atento para más consejos y trucos de SQL Server!