主页 / dba / 问题 / 204084
Accepted
SQL_Hacker
Asked: 2018-04-17 06:26:25 +0800 CST

用于为两个“函数”填充变量的字符串操作工具

  • 772

版本：SQL Server 2008 R2 - 2014

我正在使用服务器审核来查找对我的 SQL Server 实例上的登录名的更改，并将其写入 ERRORLOG 目录路径。遗憾的是，并非所有实例都对 ERRORLOG 目录使用相同的路径。我可以使用路径，SELECT SERVERPROPERTY('ErrorLogFileName')但我需要将该语句的返回值“插入”到另外两个地方。第一个是当我创建服务器审计时：

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
)

因此，我需要将 SERVERPROPERTY 查询的结果“插入”到一个变量或...某物中，以便我可以在上面的 FILEPATH 值中使用它。其次，在读取 Server Audit 创建的 *.sqlaudit 文件时，我还需要找到 ERRORLOG 目录路径。我可以轻松地使用以下内容来获取文件，但在这里，我也需要使用 SERVERPROPERTY 查询的结果“插入”文件路径值：

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

我尝试了一些“google-fu”并查看了几个字符串函数，例如 replace 和“trim-family”……这不是很相关。如果有人可以帮助或指出一两篇关于如何实现这一目标的好文章，我将不胜感激。

sql-server t-sql

2 个回答

  1. Best Answer

    您需要做的第一件事是从完整路径中删除文件名，否则您只会收到错误消息。以下是使用 CTE 两次应用表达式，这样我就不必复制/粘贴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;

    在上面的查询中，我曾经COLLATE Latin1_General_100_CI_AS强制进行不区分大小写的比较，因为我注意到“常规”SQL Server（即通过服务运行）和 SQL Server Express LocalDB 在SERVERPROPERTY('ErrorLogFileName')函数输出方面的大小写不同：

    • “常规的”：

      C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017EXPRESS\MSSQL\Log\ERRORLOG

    • 本地数据库：

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

    您可以将其与动态 SQL 一起使用以生成CREATE SERVER AUDIT如下语句：

    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);

    要读取审计文件，您不需要动态 SQL，因为您可以将表达式传递给 DMF，如下所示：

    ;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. 我不确定我是否完全理解你的问题。有什么理由不能使用 dynamicSQL并生成这些命令吗？ 

    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

    在我的本地实例上，我得到：

    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

相关问题