Início / dba / Perguntas / 204084
Accepted
SQL_Hacker
Asked: 2018-04-17 06:26:25 +0800 CST

Ferramentas de manipulação de string para preencher uma variável para duas "funções"

  • 772

Versões : SQL Server 2008 R2 - 2014

Estou usando uma auditoria de servidor para localizar alterações nos logons em minhas instâncias do SQL Server e gravá-las no caminho do diretório ERRORLOG. Infelizmente, nem todas as instâncias usam o mesmo caminho para o diretório ERRORLOG. Eu posso pegar o caminho usando SELECT SERVERPROPERTY('ErrorLogFileName'), mas preciso "inserir" o valor de retorno dessa declaração em dois outros lugares. A primeira é quando estou criando a Auditoria do Servidor:

CREATE SERVER AUDIT [ServerAuditName]
TO FILE
(   FILEPATH = N'C:\Program Files\Microsoft SQL Server\MSSQL\Log'
    ,MAXSIZE = 0 MB
    ,MAX_ROLLOVER_FILES = 2147483647
    ,RESERVE_DISK_SPACE = OFF
)

Então, eu preciso "inserir" os resultados da consulta SERVERPROPERTY em uma variável ou... algo, para que eu possa usá-lo no valor FILEPATH acima. Secundariamente, eu também preciso encontrar o caminho do diretório ERRORLOG ao ler os arquivos *.sqlaudit que o Server Audit cria. Posso usar facilmente o seguinte para obter arquivos, mas aqui também preciso "inserir" o valor do caminho do arquivo com o resultado da consulta SERVERPROPERTY:

SELECT *
FROM sys.fn_get_audit_file('Filepath\*.sqlaudit',DEFAULT, DEFAULT)

Eu tentei alguns "google-fu" e olhei para algumas funções de string, como substituir e "trim-family" ... o que não era muito relevante. Se alguém puder me ajudar ou me indicar um bom artigo ou dois sobre como conseguir isso, eu agradeceria muito.

sql-server t-sql

2 respostas

  1. Best Answer

    A primeira coisa que você precisa fazer é cortar o nome do arquivo desse caminho completo, senão você receberá apenas um erro. O seguinte faz isso usando um CTE para aplicar a expressão duas vezes para que eu não precise copiar/colar o CONVERT:

    ;WITH cte AS (SELECT CONVERT(NVARCHAR(259), SERVERPROPERTY('ErrorLogFileName')) AS [Log])
SELECT LEFT(cte.[Log], CHARINDEX(N'\error', cte.[Log] COLLATE Latin1_General_100_CI_AS))
FROM   cte;

    Na consulta acima, usei COLLATE Latin1_General_100_CI_ASpara forçar uma comparação que não diferencia maiúsculas de minúsculas, pois notei diferenças entre maiúsculas e minúsculas entre o SQL Server "regular" (ou seja, executando por meio de um serviço) e o SQL Server Express LocalDB em termos de saída da SERVERPROPERTY('ErrorLogFileName')função:

    • "regular":

      C:\Arquivos de Programas\Microsoft SQL Server\MSSQL14.SQL2017EXPRESS\MSSQL\Log\ERRORLOG

    • LocalDB:

      C:\Users\Solomon\AppData\Local\Microsoft\Microsoft SQL Server Local DB\Instances\LostInTranslation\error.log

    Você pode usar isso com o SQL dinâmico para gerar a CREATE SERVER AUDITinstrução da seguinte maneira:

    DECLARE @SQL NVARCHAR(MAX);

;WITH cte AS (SELECT CONVERT(NVARCHAR(259), SERVERPROPERTY('ErrorLogFileName')) AS [Log])
SELECT @SQL = N'CREATE SERVER AUDIT [ServerAuditName]
TO FILE
(   FILEPATH = N''' +
  LEFT(cte.[Log], CHARINDEX(N'\error', cte.[Log] COLLATE Latin1_General_100_CI_AS)) + N'''
    ,MAXSIZE = 0 MB
    ,MAX_ROLLOVER_FILES = 2147483647
    ,RESERVE_DISK_SPACE = OFF
) '    
FROM   cte;

PRINT @SQL; -- DEBUG (just to see what is being executed)

EXEC(@SQL);

    Para ler o arquivo de auditoria, você não precisa do SQL dinâmico, pois pode passar a expressão para o DMF da seguinte forma:

    ;WITH cte AS (SELECT CONVERT(NVARCHAR(259), SERVERPROPERTY('ErrorLogFileName')) AS [Log])
SELECT *
FROM   cte f
CROSS APPLY sys.fn_get_audit_file(LEFT(f.[Log], 
       CHARINDEX(N'\error', f.[Log] COLLATE Latin1_General_100_CI_AS)) + N'\*.sqlaudit',
    DEFAULT, DEFAULT);
    • 3

  2. Não tenho certeza se entendi completamente sua pergunta. Existe uma razão pela qual você não pode usar dinâmico SQLe gerar esses comandos? 

    DECLARE @Cmd VARCHAR(max)

SET @Cmd = '
CREATE SERVER AUDIT [ServerAuditName]
TO FILE
(   FILEPATH = N''' + (
        SELECT replace(convert(VARCHAR(max), SERVERPROPERTY('ErrorLogFileName')),'\ERRORLOG','\') --'
        ) + '''' + ',MAXSIZE = 0 MB
    ,MAX_ROLLOVER_FILES = 2147483647
    ,RESERVE_DISK_SPACE = OFF
)
'

PRINT @cmd

SET @Cmd = '
SELECT *
FROM sys.fn_get_audit_file(''' + (
        SELECT replace(convert(VARCHAR(max), SERVERPROPERTY('ErrorLogFileName')),'\ERRORLOG','')
        ) + '\*.sqlaudit'',DEFAULT, DEFAULT)
'

PRINT @cmd

    Na minha instância local, recebo:

    CREATE SERVER AUDIT [ServerAuditName]
TO FILE
(   FILEPATH = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Log\',MAXSIZE = 0 MB --'
    ,MAX_ROLLOVER_FILES = 2147483647
    ,RESERVE_DISK_SPACE = OFF
)

SELECT *
FROM sys.fn_get_audit_file('C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Log\*.sqlaudit',DEFAULT, DEFAULT)
    • 2

relate perguntas