¡Hola, chicos!

En esta publicación, me gustaría compartir con ustedes algunas formas de identificar la versión y edición de todas las instancias del servidor usando xp_regread y Powershell. Vi a un usuario enviando esta pregunta en un grupo de Whatsapp y no encontré muchas soluciones en Internet, lo que me motivó a escribir este artículo.

Cómo identificar la versión y edición de la instancia registrada

Si quieres identificar la información de la instancia a la que estás conectado es muy sencillo:

SELECT @@VERSION

Resultado:

También puede identificar esta información utilizando la función SERVERPROPERTY:

SELECT
    SERVERPROPERTY('MachineName') AS [MachineName],
    SERVERPROPERTY('ServerName') AS [ServerName],
    SERVERPROPERTY('InstanceName') AS [Instance],
    SERVERPROPERTY('IsClustered') AS [IsClustered],
    SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS [ComputerNamePhysicalNetBIOS],
    CASE LEFT(CONVERT(VARCHAR, SERVERPROPERTY('ProductVersion')), 4)
        WHEN '8.00' THEN 'SQL Server 2000'
        WHEN '9.00' THEN 'SQL Server 2005'
        WHEN '10.0' THEN 'SQL Server 2008'
        WHEN '10.5' THEN 'SQL Server 2008 R2'
        WHEN '11.0' THEN 'SQL Server 2012'
        WHEN '12.0' THEN 'SQL Server 2014'
        WHEN '13.0' THEN 'SQL Server 2016'
        WHEN '14.0' THEN 'SQL Server 2017'
        WHEN '15.0' THEN 'SQL Server 2019'
        ELSE 'SQL Server 2019+'
    END AS [SQLVersionBuild],
    SERVERPROPERTY('Edition') AS [Edition],
    SERVERPROPERTY('ProductLevel') AS [ProductLevel],
    SERVERPROPERTY('ProductUpdateLevel') AS [ProductUpdateLevel],
    SERVERPROPERTY('ProductVersion') AS [ProductVersion],
    SERVERPROPERTY('ProductMajorVersion') AS [ProductMajorVersion],
    SERVERPROPERTY('ProductMinorVersion') AS [ProductMinorVersion],
    SERVERPROPERTY('ProductBuild') AS [ProductBuild],
    SERVERPROPERTY('ProductBuildType') AS [ProductBuildType],
    SERVERPROPERTY('ProductUpdateReference') AS [ProductUpdateReference],
    SERVERPROPERTY('ProcessID') AS [ProcessID],
    SERVERPROPERTY('Collation') AS [Collation],
    SERVERPROPERTY('IsFullTextInstalled') AS [IsFullTextInstalled],
    SERVERPROPERTY('IsIntegratedSecurityOnly') AS [IsIntegratedSecurityOnly],
    SERVERPROPERTY('FilestreamConfiguredLevel') AS [FilestreamConfiguredLevel],
    SERVERPROPERTY('IsHadrEnabled') AS [IsHadrEnabled],
    SERVERPROPERTY('HadrManagerStatus') AS [HadrManagerStatus],
    SERVERPROPERTY('IsXTPSupported') AS [IsXTPSupported],
    SERVERPROPERTY('BuildClrVersion') AS [Build CLR Version];

Resultado:

Cómo identificar la versión y edición de todas las instancias

El problema en cuestión es cuando se necesita identificar información de servidores que tienen múltiples instancias, lo que puede terminar haciendo esta actividad un poco laboriosa, dependiendo de la cantidad de instancias del servidor.

Cómo identificar la versión y edición de todas las instancias usando T-SQL

Para satisfacer esta necesidad, escribí el siguiente script, que utiliza el procedimiento del sistema xp_regread para buscar el nombre de las instancias en el registro de Windows. Después de eso, recupera la información de cada una de estas instancias y aún usa el procedimiento. xp_cmdshell leer información del archivo ERRORLOG de cada instancia para identificar alguna información (prácticamente simula @@VERSION).

Ver código fuente
-------------------------------------------------------------------------
-- Declaração de variáveis
-------------------------------------------------------------------------

DECLARE
    @Contador INT,
    @Total INT,
    @ValorExiste INT,
    @Platform VARCHAR(100),
    @ChaveAtual NVARCHAR(500),
    @ValorAtual NVARCHAR(500),
    @SQL NVARCHAR(MAX),
    @Comando VARCHAR(8000);


DECLARE @PortasTCP TABLE
(
    PortType NVARCHAR(180),
    [Port] INT
);


DECLARE @Instancias TABLE
(
    [InstanceID] INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    [InstName] NVARCHAR(180),
    [Folder] NVARCHAR(50),
    [StaticPort] INT NULL,
    [DynamicPort] INT NULL,
    [Platform] INT NULL,
    [SQLDataRoot] NVARCHAR(255),
    [SQLPath] NVARCHAR(255),
    [Collation] NVARCHAR(100),
    [SQLVersion] NVARCHAR(100),
    [Edition] NVARCHAR(100),
    [Version] NVARCHAR(50),
    [PatchLevel] NVARCHAR(50),
    [FullVersion] NVARCHAR(255)
);


DECLARE @Plat TABLE
(
    Id INT,
    [Name] VARCHAR(180),
    InternalValue VARCHAR(100),
    Charactervalue VARCHAR(100)
);


DECLARE @Keyexist TABLE
(
    Keyexist INT
);


IF (OBJECT_ID('tempdb..#ErrorLog') IS NOT NULL) DROP TABLE #ErrorLog
CREATE TABLE #ErrorLog (
    linha INT IDENTITY(1,1) NOT NULL,
    conteudo NVARCHAR(MAX) NULL
);



-------------------------------------------------------------------------
-- Recupera informações da instância, que são comuns entre todas do servidor
-------------------------------------------------------------------------

INSERT INTO @Plat
EXEC xp_msver

SET @Platform = (SELECT TOP(1) 1 FROM @Plat WHERE [Name] = 'Platform' AND Charactervalue LIKE '%86%' ORDER BY 1)


-------------------------------------------------------------------------
-- Recupera o nome de todas as instâncias do servidor
-------------------------------------------------------------------------

IF (@Platform IS NULL)
BEGIN


    INSERT INTO @Instancias ( InstName, Folder )
    EXEC master.dbo.xp_regenumvalues N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL'


    UPDATE @Instancias
    SET [Platform] = 64;


END
ELSE BEGIN


    INSERT INTO @Instancias ( InstName, Folder )
    EXEC xp_regenumvalues N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL';


    UPDATE @Instancias
    SET [Platform] = 32


END


INSERT INTO @Keyexist
EXEC xp_regread 'HKEY_LOCAL_MACHINE', N'SOFTWARE\Wow6432Node\Microsoft\Microsoft SQL Server\Instance Names\SQL';


SET @ValorExiste = (SELECT TOP(1) Keyexist FROM @Keyexist ORDER BY 1)

IF (@ValorExiste = 1)
BEGIN

    INSERT INTO @Instancias ( InstName, Folder )
    EXEC xp_regenumvalues N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Wow6432Node\Microsoft\Microsoft SQL Server\Instance Names\SQL';

END


UPDATE
    @Instancias
SET
    [Platform] = 32
WHERE
    [Platform] IS NULL;


-------------------------------------------------------------------------
-- Recupera as portas TCP utilizadas pelas instâncias
-------------------------------------------------------------------------

SELECT
    @Total = MAX(InstanceID),
    @Contador = 1
FROM
    @Instancias;


WHILE (@Contador <= @Total)
BEGIN


    DELETE FROM @PortasTCP;


    SELECT
        @SQL = 'EXEC xp_instance_regread N''HKEY_LOCAL_MACHINE'', N''SOFTWARE\Microsoft\\Microsoft SQL Server\' + Folder + '\MSSQLServer\SuperSocketNetLib\Tcp\IPAll'', N''TCPDynamicPorts'''
    FROM
        @Instancias
    WHERE
        InstanceID = @Contador;


    INSERT INTO @PortasTCP
    EXEC sp_executesql @SQL;


    SELECT
        @SQL = 'EXEC xp_instance_regread N''HKEY_LOCAL_MACHINE'', N''SOFTWARE\Microsoft\\Microsoft SQL Server\' + Folder + '\MSSQLServer\SuperSocketNetLib\Tcp\IPAll'', N''TCPPort'''
    FROM
        @Instancias
    WHERE
        InstanceID = @Contador;


    INSERT INTO @PortasTCP
    EXEC sp_executesql @SQL;


    SELECT
        @SQL = 'EXEC xp_instance_regread N''HKEY_LOCAL_MACHINE'', N''SOFTWARE\Wow6432Node\Microsoft\\Microsoft SQL Server\' + Folder + '\MSSQLServer\SuperSocketNetLib\Tcp\IPAll'', N''TCPDynamicPorts'''
    FROM
        @Instancias
    WHERE
        InstanceID = @Contador;


    INSERT INTO @PortasTCP
    EXEC sp_executesql @SQL;


    SELECT
        @SQL = 'EXEC xp_instance_regread N''HKEY_LOCAL_MACHINE'', N''SOFTWARE\Wow6432Node\Microsoft\\Microsoft SQL Server\' + Folder + '\MSSQLServer\SuperSocketNetLib\Tcp\IPAll'', N''TCPPort'''
    FROM
        @Instancias
    WHERE
        InstanceID = @Contador;


    INSERT INTO @PortasTCP
    EXEC sp_executesql @SQL;


    UPDATE
        A
    SET
        StaticPort = C.[Port],
        DynamicPort = B.[Port]
    FROM
        @Instancias A
        INNER JOIN @PortasTCP B ON B.PortType = 'TCPDynamicPorts'
        INNER JOIN @PortasTCP C ON C.PortType = 'TCPPort'
    WHERE
        A.InstanceID = @Contador;


    SET @Contador = @Contador + 1;


END;



-------------------------------------------------------------------------
-- Recupera as outras informações da instância
-------------------------------------------------------------------------

SET @Contador = 1


WHILE (@Contador <= @Total)
BEGIN
    

    SELECT 
        @ChaveAtual = 'SOFTWARE\Microsoft\Microsoft SQL Server\' + Folder + '\Setup'
    FROM 
        @Instancias
    WHERE 
        InstanceID = @Contador

    
    -- SQLDataRoot
    EXEC master.dbo.xp_regread 
        @rootkey = 'HKEY_LOCAL_MACHINE',
        @key = @ChaveAtual,
        @value_name = 'SQLDataRoot',
        @value = @ValorAtual OUTPUT


    UPDATE @Instancias
    SET [SQLDataRoot] = @ValorAtual
    WHERE InstanceID = @Contador


    -- SQLPath
    EXEC master.dbo.xp_regread 
        @rootkey = 'HKEY_LOCAL_MACHINE',
        @key = @ChaveAtual,
        @value_name = 'SQLPath',
        @value = @ValorAtual OUTPUT


    UPDATE @Instancias
    SET [SQLPath] = @ValorAtual
    WHERE InstanceID = @Contador


    -- PatchLevel
    EXEC master.dbo.xp_regread 
        @rootkey = 'HKEY_LOCAL_MACHINE',
        @key = @ChaveAtual,
        @value_name = 'PatchLevel',
        @value = @ValorAtual OUTPUT


    UPDATE @Instancias
    SET 
        [PatchLevel] = @ValorAtual,
        [SQLVersion] = (
        CASE 
            WHEN LEFT(@ValorAtual, 1) = '8' THEN 'SQL Server 2000'
            WHEN LEFT(@ValorAtual, 1) = '9' THEN 'SQL Server 2005'
            WHEN LEFT(@ValorAtual, 4) = '10.5' THEN 'SQL Server 2008 R2'
            WHEN LEFT(@ValorAtual, 2) = '10' THEN 'SQL Server 2008'
            WHEN LEFT(@ValorAtual, 2) = '11' THEN 'SQL Server 2012'
            WHEN LEFT(@ValorAtual, 2) = '12' THEN 'SQL Server 2014'
            WHEN LEFT(@ValorAtual, 2) = '13' THEN 'SQL Server 2016'
            WHEN LEFT(@ValorAtual, 2) = '14' THEN 'SQL Server 2017'
            WHEN LEFT(@ValorAtual, 2) = '15' THEN 'SQL Server 2019'
        END)
    WHERE 
        InstanceID = @Contador


    -- Version
    EXEC master.dbo.xp_regread 
        @rootkey = 'HKEY_LOCAL_MACHINE',
        @key = @ChaveAtual,
        @value_name = 'Version',
        @value = @ValorAtual OUTPUT


    UPDATE @Instancias
    SET [Version] = @ValorAtual
    WHERE InstanceID = @Contador


    -- Edition
    EXEC master.dbo.xp_regread 
        @rootkey = 'HKEY_LOCAL_MACHINE',
        @key = @ChaveAtual,
        @value_name = 'Edition',
        @value = @ValorAtual OUTPUT


    UPDATE @Instancias
    SET [Edition] = @ValorAtual
    WHERE InstanceID = @Contador


    -- Collation
    EXEC master.dbo.xp_regread 
        @rootkey = 'HKEY_LOCAL_MACHINE',
        @key = @ChaveAtual,
        @value_name = 'Collation',
        @value = @ValorAtual OUTPUT


    UPDATE @Instancias
    SET [Collation] = @ValorAtual
    WHERE InstanceID = @Contador


    SET @Contador = @Contador + 1;


END



-------------------------------------------------------------------------
-- Recupera as informações da instância a partir do errorlog
-------------------------------------------------------------------------


DECLARE @Fl_Xp_CmdShell_Ativado BIT = (SELECT (CASE WHEN CAST([value] AS VARCHAR(MAX)) = '1' THEN 1 ELSE 0 END) FROM sys.configurations WHERE [name] = 'xp_cmdshell')
 
IF (@Fl_Xp_CmdShell_Ativado = 0)
BEGIN
 
    EXECUTE SP_CONFIGURE 'show advanced options', 1;
    RECONFIGURE WITH OVERRIDE;
    
    EXEC sp_configure 'xp_cmdshell', 1;
    RECONFIGURE WITH OVERRIDE;
    
END


SET @Contador = 1


WHILE (@Contador <= @Total)
BEGIN
    

    SELECT 
        @Comando = 'type "' + SQLPath + '\Log\ERRORLOG"'
    FROM 
        @Instancias
    WHERE 
        InstanceID = @Contador

    
    TRUNCATE TABLE #ErrorLog


    INSERT INTO #ErrorLog
    EXEC master.dbo.xp_cmdshell @Comando

    
    SET @ValorAtual = (SELECT LTRIM(RTRIM(SUBSTRING(conteudo, 35, 100))) FROM #ErrorLog WHERE linha = 1)
    SET @ValorAtual += ' ' + (SELECT LTRIM(RTRIM(SUBSTRING(conteudo, 2, 100))) FROM #ErrorLog WHERE linha = 4)


    UPDATE @Instancias
    SET [FullVersion] = @ValorAtual
    WHERE InstanceID = @Contador


    SET @Contador = @Contador + 1;


END



IF (@Fl_Xp_CmdShell_Ativado = 0)
BEGIN
 
    EXEC sp_configure 'xp_cmdshell', 0;
    RECONFIGURE WITH OVERRIDE;
 
    EXECUTE SP_CONFIGURE 'show advanced options', 0;
    RECONFIGURE WITH OVERRIDE;
 
END



-------------------------------------------------------------------------
-- Exibe as informações finais
-------------------------------------------------------------------------

SELECT
    SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS ServerName,
    A.InstName,
    A.[Folder],
    A.StaticPort,
    A.DynamicPort,
    A.[Platform],
    A.SQLPath,
    A.SQLVersion,
    A.Edition,
    A.[Version],
    A.PatchLevel,
    A.FullVersion
FROM
    @Instancias A

Resultado:

Cómo identificar la versión y edición de todas las instancias usando Powershell

Otra forma de capturar la información básica de todas las instancias de una lista de servidores es mediante el siguiente script de Powershell:

Ver el código fuente (solo usando PowerShell)
# Recupera o nome de todos os servidores do arquivo "servidores.txt"
$servers = get-content c:\temp\servidores.txt;

"servidor,instância,edição,versão" > "C:\temp\versao.csv"
 
# Iteração entre os servidores
foreach ($server in $servers) 
{
 
    $out = $null;
 
    # Verifica se o computador está online
    if (test-connection -computername $server -count 1 -ea 0) 
    {
 
        try 
        {
            
            $type = [Microsoft.Win32.RegistryHive]::LocalMachine;
            $regconnection = [Microsoft.Win32.RegistryKey]::OpenRemoteBaseKey($type, $server) ;
            $instancekey = "SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL";
 
            try 
            {
                $openinstancekey = $regconnection.opensubkey($instancekey);
            }
            catch { 
                $out = $server + ", Nenhuma chave de registro do SQL Server encontrada";
            }
 
            # Recupera o nome das instâncias
            $instances = $openinstancekey.getvaluenames();
            
            # Iteração entre as instâncias do servidor
            foreach ($instance in $instances) 
            {
 
                $instancename = $openinstancekey.getvalue($instance);
                $instancesetupkey = "SOFTWARE\Microsoft\Microsoft SQL Server\" + $instancename + "\Setup"; 
 
                $openinstancesetupkey = $regconnection.opensubkey($instancesetupkey);
                $edition = $openinstancesetupkey.getvalue("Edition")
                $version = $openinstancesetupkey.getvalue("Version");
 
                switch -wildcard ($version) 
                {
                    "15*" {$versionname = "SQL Server 2019";}
                    "14*" {$versionname = "SQL Server 2017";}
                    "13*" {$versionname = "SQL Server 2016";}
                    "12*" {$versionname = "SQL Server 2014";}
                    "11*" {$versionname = "SQL Server 2012";}
                    "10.5*" {$versionname = "SQL Server 2008 R2";}
                    "10.4*" {$versionname = "SQL Server 2008";}
                    "10.3*" {$versionname = "SQL Server 2008";}
                    "10.2*" {$versionname = "SQL Server 2008";}
                    "10.1*" {$versionname = "SQL Server 2008";}
                    "10.0*" {$versionname = "SQL Server 2008";}
                    "9*" {$versionname = "SQL Server 2005";}
                    "8*" {$versionname = "SQL Server 2000";}
                    default {$versionname = $version;}
                }
 
                # Armazena os resultados na variável que será grava no arquivo CSV
                $out += $server + "," + $instancename + "," + $edition + "," + $versionname + "`n";
                
            }
 
        }
        catch 
        { 
            $out = $server + ", Não foi possível abrir o registro";
        }
 
    }
    else 
    {
        $out = $server + ", Servidor offline"
    }
 
    $out >> "C:\temp\versao.csv";
    
}
Ver código fuente (usando T-SQL, xp_cmdshell y PowerShell)
------------------------------------------------------
-- Gera o Script Powershell
------------------------------------------------------

DECLARE @Script VARCHAR(MAX) = '
# Recupera o nome de todos os servidores do arquivo "servidores.txt"
$servers = get-content c:\temp\servidores.txt;

"servidor,instância,edição,versão" > "C:\temp\versao.csv"
 
# Iteração entre os servidores
foreach ($server in $servers) 
{
 
    $out = $null;
 
    # Verifica se o computador está online
    if (test-connection -computername $server -count 1 -ea 0) 
    {
 
        try 
        {
            
            $type = [Microsoft.Win32.RegistryHive]::LocalMachine;
            $regconnection = [Microsoft.Win32.RegistryKey]::OpenRemoteBaseKey($type, $server) ;
            $instancekey = "SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL";
 
            try 
            {
                $openinstancekey = $regconnection.opensubkey($instancekey);
            }
            catch { 
                $out = $server + ", Nenhuma chave de registro do SQL Server encontrada";
            }
 
            # Recupera o nome das instâncias
            $instances = $openinstancekey.getvaluenames();
            
            # Iteração entre as instâncias do servidor
            foreach ($instance in $instances) 
            {
 
                $instancename = $openinstancekey.getvalue($instance);
                $instancesetupkey = "SOFTWARE\Microsoft\Microsoft SQL Server\" + $instancename + "\Setup"; 
 
                $openinstancesetupkey = $regconnection.opensubkey($instancesetupkey);
                $edition = $openinstancesetupkey.getvalue("Edition")
                $version = $openinstancesetupkey.getvalue("Version");
 
                switch -wildcard ($version) 
                {
                    "15*" {$versionname = "SQL Server 2019";}
                    "14*" {$versionname = "SQL Server 2017";}
                    "13*" {$versionname = "SQL Server 2016";}
                    "12*" {$versionname = "SQL Server 2014";}
                    "11*" {$versionname = "SQL Server 2012";}
                    "10.5*" {$versionname = "SQL Server 2008 R2";}
                    "10.4*" {$versionname = "SQL Server 2008";}
                    "10.3*" {$versionname = "SQL Server 2008";}
                    "10.2*" {$versionname = "SQL Server 2008";}
                    "10.1*" {$versionname = "SQL Server 2008";}
                    "10.0*" {$versionname = "SQL Server 2008";}
                    "9*" {$versionname = "SQL Server 2005";}
                    "8*" {$versionname = "SQL Server 2000";}
                    default {$versionname = $version;}
                }
 
                # Armazena os resultados na variável que será grava no arquivo CSV
                $out += $server + "," + $instancename + "," + $edition + "," + $versionname + "`n";
                
            }
 
        }
        catch 
        { 
            $out = $server + ", Não foi possível abrir o registro";
        }
 
    }
    else 
    {
        $out = $server + ", Servidor offline"
    }
 
    $out >> "C:\temp\versao.csv";
    
}'



------------------------------------------------------
-- Cria um arquivo físico com o código do Script Posh
------------------------------------------------------


DECLARE @Fl_Ole_Automation_Ativado BIT = (SELECT (CASE WHEN CAST([value] AS VARCHAR(MAX)) = '1' THEN 1 ELSE 0 END) FROM sys.configurations WHERE [name] = 'Ole Automation Procedures')
 
IF (@Fl_Ole_Automation_Ativado = 0)
BEGIN
 
    EXECUTE SP_CONFIGURE 'show advanced options', 1;
    RECONFIGURE WITH OVERRIDE;
    
    EXEC sp_configure 'Ole Automation Procedures', 1;
    RECONFIGURE WITH OVERRIDE;
    
END


DECLARE
    @objFileSystem INT,
    @objTextStream INT,
    @objErrorObject INT,
    @strErrorMessage VARCHAR(1000),
    @hr INT,
    @Ds_Arquivo VARCHAR(500) = 'C:\Temp\Script.ps1'

 
SELECT @strErrorMessage = 'opening the File System Object'
    
EXECUTE @hr = sp_OACreate
    'Scripting.FileSystemObject',
    @objFileSystem OUT
 
    
IF @HR = 0
    SELECT
        @objErrorObject = @objFileSystem,
        @strErrorMessage = 'Creating file "' + @Ds_Arquivo + '"'
    
    
IF @HR = 0
    EXECUTE @hr = sp_OAMethod
        @objFileSystem,
        'CreateTextFile',
        @objTextStream OUT,
        @Ds_Arquivo,
        2,
        True
 
IF @HR = 0
    SELECT
        @objErrorObject = @objTextStream,
        @strErrorMessage = 'writing to the file "' + @Ds_Arquivo + '"'
    
    
IF @HR = 0
    EXECUTE @hr = sp_OAMethod
        @objTextStream,
        'Write',
        NULL,
        @Script
 
    
IF @HR = 0
    SELECT
        @objErrorObject = @objTextStream,
        @strErrorMessage = 'closing the file "' + @Ds_Arquivo + '"'
    
    
IF @HR = 0
    EXECUTE @hr = sp_OAMethod
        @objTextStream,
        'Close'
 
    
IF @hr <> 0
BEGIN
    
    DECLARE
        @Source VARCHAR(255),
        @Description VARCHAR(255),
        @Helpfile VARCHAR(255),
        @HelpID INT
    
    EXECUTE sp_OAGetErrorInfo
        @objErrorObject,
        @source OUTPUT,
        @Description OUTPUT,
        @Helpfile OUTPUT,
        @HelpID OUTPUT
        
        
    SELECT
        @strErrorMessage = 'Error whilst ' + COALESCE(@strErrorMessage, 'doing something') + ', ' + COALESCE(@Description, '')
        
        
    RAISERROR (@strErrorMessage,16,1)
        
END
    
    
EXECUTE sp_OADestroy @objTextStream
EXECUTE sp_OADestroy @objTextStream


--------------------------------------------------------------------------------
-- Desativando o OLE Automation (Se não estava habilitado antes)
--------------------------------------------------------------------------------
 
IF (@Fl_Ole_Automation_Ativado = 0)
BEGIN
 
    EXEC sp_configure 'Ole Automation Procedures', 0;
    RECONFIGURE WITH OVERRIDE;
 
    EXECUTE SP_CONFIGURE 'show advanced options', 0;
    RECONFIGURE WITH OVERRIDE;
 
END


------------------------------------------------------
-- Executa o Script Powershell
------------------------------------------------------

DECLARE @Fl_Xp_CmdShell_Ativado BIT = (SELECT (CASE WHEN CAST([value] AS VARCHAR(MAX)) = '1' THEN 1 ELSE 0 END) FROM sys.configurations WHERE [name] = 'xp_cmdshell')
 
IF (@Fl_Xp_CmdShell_Ativado = 0)
BEGIN
 
    EXECUTE SP_CONFIGURE 'show advanced options', 1;
    RECONFIGURE WITH OVERRIDE;
    
    EXEC sp_configure 'xp_cmdshell', 1;
    RECONFIGURE WITH OVERRIDE;
    
END


EXEC master.dbo.xp_cmdshell 'powershell.exe -ExecutionPolicy ByPass -File "C:\Temp\Script.ps1"'


IF (@Fl_Xp_CmdShell_Ativado = 0)
BEGIN
 
    EXEC sp_configure 'xp_cmdshell', 0;
    RECONFIGURE WITH OVERRIDE;
 
    EXECUTE SP_CONFIGURE 'show advanced options', 0;
    RECONFIGURE WITH OVERRIDE;
 
END

En este ejemplo, completará el archivo de entrada (C:\Temp\servidores.txt) con el nombre de host de los servidores que desea analizar las ediciones y versiones de SQL Server.

Después de ejecutar el script de Powershell, se generará un archivo CSV (C:\Temp\versao.csv) con la información recopilada:

¡Eso es todo, amigos!
Espero que hayas disfrutado de esta publicación y nos vemos la próxima.