Vorster's questions

执行下面的查询时，如果我使用PRINT它会正确打印。我可以复制并粘贴打印的代码并执行它。但是，如果我使用 EXEC，则会收到以下错误：

有没有办法简化我正在做的事情？为什么 SQLPRINT和 SQLEXEC会提供这两个截然不同的结果集？

DECLARE @TableName as NVARCHAR(250), @SQL as VARCHAR(MAX);

DECLARE @TableCursor as CURSOR;
SET @TableCursor = CURSOR FOR
SELECT sobjects.name
FROM sysobjects sobjects
WHERE sobjects.xtype = 'U'
AND name like 'HISTORY_MasterList_%'
ORDER BY sobjects.name

OPEN @TableCursor;
FETCH NEXT FROM @TableCursor INTO @TableName;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL ='select '''+ @TableName +''', 0
 Union All
select All ''Server Count'',count(1) from ['+ @TableName +'] 
 Union All
select All ''Server Cores'',sum(convert(decimal(18,0),cores)) from ['+ @TableName +'] 
 Union All
select ''Production Servers'',count(1) from ['+ @TableName +'] where Classification in (''Prod'',''Production'',''Prd'',''Unknown'')
Union All
select ''Production Cores'', sum(convert(decimal(18,0),cores)) from ['+ @TableName +']
 where Classification in (''Prod'',''Production'',''Prd'',''Unknown'')
 Union All
select ''Production Server Count after filtering out passive/failover servers'', count(1) from 
(select  distinct m.ServerName
       from ['+ @TableName +']  m
       inner join [SQLEnv].[dbo].[vwManView] v on m.ServerName = v.ServerName
       where Classification in (''Prod'',''Production'',''Prd'',''Unknown'')
       and unit <> 0) aa
 Union All
select ''Production Server Cores after filtering out passive/failover servers'', sum(convert(decimal(18,0),cores)) from(
       select  distinct m.ServerName, m.Cores
       from ['+ @TableName +']  m
       inner join [SQLEnv].[dbo].[vwManView] v on m.ServerName = v.ServerName
       where Classification in (''Prod'',''Production'',''Prd'',''Unknown'')
       and unit <> 0) aa
 Union All
select ''Non-Prod SQL Instances downgraded to Developer Edition'',count(1) from ['+ @TableName +'] where ''InstanceStatus'' like ''Downgrade%''
 Union All
select ''Non-Prod SQL Instance Core Count downgraded to Developer Edition'',sum(convert(decimal(18,0),cores)) from ['+ @TableName +']  where ''InstanceStatus'' like ''Downgrade%''
 Union All
select ''Non-Prod VMs moved from Prod Environments'', count(1) from ['+ @TableName +']  where ServerStatus like ''VM Moved/right sized from Prod Env to NonProd''
 Union All
select ''Non-Prod VMs TO BE moved from Prod Environments'', count(1) from ['+ @TableName +']  where ServerStatus like ''Sent for V2V - non prod split'' 
'

EXEC @SQL

FETCH NEXT FROM @TableCursor INTO @TableName;
END
CLOSE @TableCursor;
DEALLOCATE @TableCursor;

下面的查询用于创建一个包含服务器名称列表的文本文件。

我遇到的问题是文本文件的创建方式如下

Server, Server, Server etc.

但我需要它如下

Server
Server
Server

查询：

BEGIN
    CREATE TABLE [TempPSWmiObjectQuery]
                (QueryText nvarchar(max))
END

INSERT INTO [TempPSWmiObjectQuery]

SELECT STUFF((SELECT char(13)+char(10), 
             SUBSTRING(ServerName, 1, 
             CASE WHEN CHARINDEX(',', ServerName) > 0 
                  THEN CHARINDEX(',', ServerName)-1
                  ELSE LEN(ServerName)
             END)
FROM MasterList
FOR XML PATH('')), 1, 1, '')

EXEC sp_configure 'xp_cmdshell', 1;  
RECONFIGURE

EXEC xp_cmdshell 'bcp SQLEnv.dbo.TempPSWmiObjectQuery out "\\ServerName\Data\temp\testlist.txt" -c -T -S "ServerName\Instance"'

EXEC sp_configure 'xp_cmdshell', 0;  
RECONFIGURE

DROP TABLE TempPSWmiObjectQuery

PowerShell 脚本：

        $StagingServer = "ServerName\InstanceName"
        $StagingServerDatabase = "SQLEnv" 

    foreach($line in 
    [System.IO.File]::ReadLines("\\ServerName\Data\temp\testlist.txt"))
    {
        $line
        Get-WmiObject win32_Service -Computer $line  | where {$_.DisplayName 
    -match "SQL Server"} | select SystemName, DisplayName, Name, State, 
    Status, StartMode, StartName | ConvertTo-DbaDataTable | Write- 
    DbaDataTable -SqlInstance $StagingServer -Database 
    $StagingServerDatabase -Table _SQLServicesInfo -AutoCreateTable -Confirm

    }

我目前正在运行以下程序来监视复制并在超过 10000 个 pendingcmds 的阈值时发送邮件，但是无论阈值如何，警报邮件都会每 5 分钟生成一次。我需要帮助来重新编码阈值部分，因为这没有提供预期的结果。

        DECLARE @RESULT nvarchar(100)
        DECLARE @cmd varchar(100)

        SET @RESULT = ('SELECT pendingcmdcount FROM #pendingcmds') 
        EXEC sys.sp_executesql @RESULT
        PRINT @RESULT
        IF @RESULT > '10000'

用于生成警报的代码

        USE [TestDB]

        create table #pendingcmds
        (pendingcmdcount int,
        estimatedprocesstime int)

        Insert INTO #pendingcmds (pendingcmdcount, estimatedprocesstime)
        select * FROM OPENROWSET('SQLOLEDB', 
        'Server=Server\Instance;Trusted_Connection=yes;', 
        'set fmtonly off; 
        exec distribution..sp_replmonitorsubscriptionpendingcmds 
        @publisher=''Server\Instance'', 
        @publisher_db=''testproduction'', 
        @publication=''Summary_Inventory_TestProduction'', 
        @subscriber=''Server'', 
        @subscriber_db=''TestReplAzure'', 
        @subscription_type=0')

        SELECT * FROM #pendingcmds

        --DECLARE @RESULT nvarchar(100)
        ----DECLARE @cmd varchar(100)
        --
        --SET @RESULT = ('SELECT pendingcmdcount FROM #pendingcmds') 
        --EXEC sys.sp_executesql @RESULT
        --PRINT @RESULT
        --IF @RESULT > '10000'
        --
        --BEGIN

         /*************************************************************/  
         /****************** HTML Preparation *************************/  
         /*************************************************************/

        DECLARE @HTML  VARCHAR(MAX),
                @table VARCHAR(MAX)

        SET @HTML = --HTML layout--
                        '<html><head>' +
                        '<H1 style="color: #000000">Publisher to Subscriber has exceeded threshold</H1>' +
                        '<style>' +
                        'td {border: solid black 1px;padding-left:5px;padding-right:5px;padding-top:1px;padding-bottom:1px;font-size:9pt;color:Black;} ' +
                        '</style>' +
                        '</head>' +
                        '<body><table cellpadding=0 cellspacing=0 border=0>' +
                        '<tr bgcolor=#FF0000>'+  
                        '<td align=center>Pending CMD Count</b></td>' + 
                        '<td align=center>Estimated Process Time</b></td></tr>';  

        SELECT @table = CONVERT(nvarchar(max) ,
            (SELECT
             td = CAST(pendingcmdcount as varchar(30)), '',
             td = CAST(estimatedprocesstime as varchar(30)), '' 
             FROM #pendingcmds
             FOR XML PATH(N'tr'), TYPE));

        SET @HTML = @HTML + @table + CHAR(10) + 
                     N'</table></body></html>';

        SET @HTML = @HTML + '<TR></TR><B>End of Report</B></TABLE></BODY></HTML>';

        PRINT @HTML

        Declare @MailSubject varchar(100)
        SET @MailSubject = 'Alert - Publisher to Subscriber has exceeded threshold!!! - ' + DATENAME(weekday, getdate())

        IF LEN(@HTML) > 10 

            EXEC msdb.dbo.sp_send_dbmail
            @recipients = '[email protected]',
            @body_format =  'HTML',
            @body= @HTML,
            @subject = @MailSubject,
            @profile_name = 'MailRelay'

        DROP TABLE #pendingcmds
        --END

        --ELSE 
        --DROP TABLE #pendingcmds
        --
        --END

我想创建一个可以清理 master 和 msdb 的维护数据库，我正在尝试从 master 和 msdb 收集所有不是 Microsoft 表的表信息，请记住，由于不同的要求，某些实例可能具有不同的结构。我收到代码错误

消息 102，级别 15，状态 1，第 27 行“@ServerName”附近的语法不正确

有没有更简单的方法来做到这一点？

DECLARE servers_cursor CURSOR
    FOR
    -- select * from environment
    -- select * from DatabaseInfo
        select  distinct LinkedserverName,Environment,ServerUse
        from SQLMonitor.dbo.Environment
        join master..sysservers on srvname COLLATE DATABASE_DEFAULT = LinkedserverName COLLATE DATABASE_DEFAULT
        where ServerUse = 'DEV'
        --and IncludeBackupsStats = 1
        order by LinkedServerName

    OPEN servers_cursor
    DECLARE @Servername varchar(255)
    DECLARE @sql varchar(8000)
    DECLARE @Environment char(4)
    DECLARE @ServerUse varchar(15)

    FETCH NEXT FROM servers_cursor INTO @ServerName,@Environment,@ServerUse
    WHILE (@@FETCH_STATUS = 0)
    BEGIN
        PRINT @ServerName
        CREATE TABLE [_TempDetails_]
        (DatabaseName sysname
        ,TableName sysname
        ,ActualCommand nvarchar(max))
        INSERT INTO [TempTableScripts].dbo._TempDetails_
        SELECT * FROM OPENQUERY(@ServerName, '
    declare @TableNames table
    (
        DatabaseName sysname
        , TableName sysname
    )
        insert @TableNames
        EXEC sp_msforeachdb @command1 = ''use ?;
        IF ''''?'''' IN (''''master'''',''''msdb'''')
        BEGIN
        SELECT ''''?'''', name from sys.tables T WHERE is_ms_shipped = 0
        END'+'

    select *, ''''SELECT TOP 1 * INTO ['''' + @ServerName + ''''_-_'''' + Databasename +''''_-_'''' + TableName +''''] FROM ['''' + @ServerName+''''].[''''+ Databasename + ''''].dbo.['''' + TableName + '''']'''' 
    from @TableNames
    ')
            --exec (@sql)
      FETCH NEXT FROM servers_cursor INTO @ServerName,@Environment,@ServerUse
      --PRINT @ServerName
    END
    CLOSE servers_cursor
    DEALLOCATE servers_cursor 

我正在尝试基于要备份到的服务器名\实例创建一个文件夹，我希望该文件夹创建为服务器名_实例。你能指出我哪里出错了吗，因为它创建为服务器名\实例

IF OBJECT_ID('TempPSCreateFolder') IS NULL
BEGIN
CREATE TABLE [TempPSCreateFolder]
(QueryText nvarchar(max))
END

INSERT INTO [TempPSCreateFolder]

SELECT '$fso = new-object -ComObject scripting.filesystemobject

$fso.CreateFolder("C:\'+ STUFF((SELECT ',' + SUBSTRING(@@SERVERNAME, 1, CASE 
WHEN CHARINDEX(',', @@SERVERNAME) > 0 THEN 
CHARINDEX(',', @@ServerName)-1
                       ELSE LEN(@@ServerName)
                    END)

FOR XML PATH('')), 1, 1, '')+'")'

EXEC sp_configure 'xp_cmdshell', 1;  
RECONFIGURE

EXEC xp_cmdshell 'bcp master.dbo.TempPSCreateFolder out 
"C:\SQLDBA\CreateFolder.ps1" -c -T -SServer\Instance'

EXEC sp_configure 'xp_cmdshell', 0;  
RECONFIGURE

DROP TABLE TempPSCreateFolder

我正忙着通过将所有代理作业全部编写到文件来备份所有代理作业。`我将如何重写以下内容以改用 BCP

        BEGIN
        SET @EchoCommand = 'ECHO ' + @EchoField + ' >> ' + @Path
        + @JobName
        + REPLACE(REPLACE(CONVERT(VARCHAR(200), GETDATE()), ' ',
        ''), ':', '') + '.sql'

类似于下面的东西

        SET @EchoCommand = 'bcp 
        "exec['+DB_NAME()+'].dbo.CreateBackUpFromJob' +
        @JobName + '" queryout "\\ServerName\SQL_Backups\' + @JobName + 
        '.sql" -c -UTF8 -T -SLOCALSERVER'

我知道 CONCAT 函数在2012之前的 SQL Server 中不存在。我将如何优化我的代码以不使用 CONCAT 函数？

         SELECT  dbname = DB_NAME(),
                  file_name = name, 
                  TotalMB = CONVERT(decimal(12,1),size/128.0),
                  UsedMB = CONVERT(decimal(12,1),FILEPROPERTY(name,'SpaceUsed')/128.0),
                  FreeMB = CONVERT(decimal(12,1),(size - FILEPROPERTY(name,'SpaceUsed'))/128.0),
                  Command = CONCAT('USE MASTER; ALTER DATABASE ', DB_NAME(), '; MODIFY FILE (name = ',
                        [name], ', size = ', 
                        convert(int,round(1.15 * FILEPROPERTY(name,'SpaceUsed')/128,-1)), 'MB)')
           FROM sys.database_files WITH (NOLOCK)
           WHERE type_desc = 'ROWS'
           ORDER BY file_id;

我们最近从生产数据库中清除了旧数据。该数据库为 3 TB，其中 1.4 TB 为空，但这给开发和 QA 实例带来了问题，因为我们没有利用空间，因为我们有 6 - 8 个数据库和 1.4 TB 的空空间，尤其是在我们在开发中存在空间限制的情况下。我想设置一个工作来使用下面的代码缩小开发数据库

EXEC sp_MSForEachDB ' USE [?]; DBCC SHRINKFILE  (''?'' , 10)' 

EXEC sp_MSForEachDB ' USE [?]; DBCC SHRINKFILE (''?'' , 0, TRUNCATEONLY)' 

我收到以下错误，“无法在 sys.database_files 中找到数据库 'Test' 的文件 'Test'。该文件不存在或已被删除。” 数据库有多个数据文件。我如何改进我的代码以适应多个数据文件。

USE [TEST]
GO

CREATE PROCEDURE [dbo].[SQLDBA_DatabaseSpaceReport]
AS
BEGIN
DECLARE @dMaxLogDate Datetime = (SELECT TOP 1 LogDate FROM 
SQLDBA_PS_Get_DbaDatabaseSpace ORDER BY LogDate DESC)

DECLARE 
@html NVARCHAR(MAX), 
@table VARCHAR(MAX)

SET @html = --HTML layout--
            '<html><head>' +
            '<H1 style="color: #000000">Prod Databases with less than 10% 
            space available</H1>' +
            '<style>' +
            'td {border: solid black 1px;padding-left:5px;padding-
            right:5px;padding-top:1px;padding-bottom:1px;font-
            size:9pt;color:Black;} ' +
            '</style>' +
            '</head>' +
            '<body><table cellpadding=0 cellspacing=0 border=0>' +
            '<tr bgcolor=33ffff>'+
            '<td align=center>SqlInstance</b></td>' + 
            '<td align=center>Database</b></td>' + 
            '<td align=center>File Name</b></td>' + 
            '<td align=center>FileType</b></td>' + 
            '<td align=center>Used Space MB</b></td>' + 
            '<td align=center>Free Space MB</b></td>' + 
            '<td align=center>File Size MB</b></td>' +  
            '<td align=center>Percent Used</b></td>' +  
            '<td align=center>Auto Growth</b></td>' + 
            '<td align=center>Auto Grow Type</b></td></tr>';

   SELECT @table = CONVERT(nvarchar(max) ,
(SELECT 
td = [SqlInstance], 
'',
td = [Database],
'',
td = [FileName],
'',
td = [FileType],     
'',
td = CAST([UsedSpaceMB]as varchar(30)), 
'',
td = CAST([FreeSpaceMB]as varchar(30)),  
'',
td = CAST([FileSizeMB]as varchar(30)), 
'',
td = CAST([PercentUsed]as varchar(30)), 
'',
td = CAST([AutoGrowth]as varchar(30)), 
'',
td = [AutoGrowType]

FROM [TEST].[dbo].SQLDBA_PS_Get_DbaDatabaseSpace D
INNER JOIN [TEST].[dbo].[Environment] E on D.ComputerName = 
E.VirtualName AND E.ServerUse IN ('PROD','DR')
WHERE ( FileName NOT LIKE '%temp%' and FileName NOT LIKE 'SP%' and FileName 
NOT LIKE 'Share%' )
AND LogDate = @dMaxLogDate and AutoGrowth > 0
ORDER BY [PercentUsed] desc

FOR XML PATH(N'tr'), TYPE));

SET @html = @html + @table + CHAR(10) + 
      N'</table></body></html>';

IF LEN(@HTML) > 10

BEGIN

--print @HTML

EXEC msdb.dbo.sp_send_dbmail
@recipients = '[email protected]',
@body_format =  'HTML',
@body= @HTML,
@subject = 'Alert!! Prod Databases With Less Than 10% Space Available!!',
@profile_name = 'SQL_DBA'

END

END

像这样的东西：（不是 100% 确定下面的代码或在哪里添加它）

CASE WHEN PercentUsed > 95 THEN 
'<td align="Center"><font face="Arial" size="1" color="#FF0000"><b>' +  
CAST([PercentUsed]as varchar(30)  +'</font></td>' 
ELSE 
'<td align="Center"><font face="Arial" size="1" color="#40C211"><b>' +  
CAST([PercentUsed]as varchar(30)  +'</font></td>'  
END +
'</tr>'                                  
FROM [TEST].[dbo].SQLDBA_PS_Get_DbaDatabaseSpace   

我想更改我的查询，以便它只显示已更改的值。我正在比较当前表和历史表之间的值。查询显示所有值，我必须追踪邮件以找到差异。我如何编写查询以仅显示具有更改值的行？

DECLARE @Date Datetime = (SELECT TOP 1 CollectDate FROM 
SQLMonitor.dbo.tbl_ServerLevelSecurity_Current_HIST ORDER BY CollectDate 
DESC)

   /*select * FROM SQLMonitor.dbo.tbl_ServerLevelSecurity_Current NEW
   INNER JOIN SQLMonitor.dbo.tbl_ServerLevelSecurity_Current_HIST OLD ON 
   OLD.ServerName = NEW.ServerName
   WHERE OLD.CollectDate = @Date
   */
--Changed or new
IF EXISTS(SELECT * FROM SQLMonitor.dbo.tbl_ServerLevelSecurity_Current WHERE 

ISNULL (ServerName, '_')
+ISNULL (name, '_')
+ISNULL (Type_Desc, '_')
+ISNULL ([Public], '_')
+ISNULL (SysAdmin, '_')
+ISNULL (SecurityAdmin, '_')
+ISNULL (ServerAdmin, '_')
+ISNULL (SetupAdmin, '_')
+ISNULL (ProcessAdmin, '_')
+ISNULL (DiskAdmin, '_')
+ISNULL (DBCreator, '_')
+ISNULL (BulkAdmin, '_')
+ISNULL (ControlServer, '_')


NOT IN (SELECT ISNULL (ServerName, '_')
+ISNULL (name, '_')
+ISNULL (Type_Desc, '_')
+ISNULL ([Public], '_')
+ISNULL (SysAdmin, '_')
+ISNULL (SecurityAdmin, '_')
+ISNULL (ServerAdmin, '_')
+ISNULL (SetupAdmin, '_')
+ISNULL (ProcessAdmin, '_')
+ISNULL (DiskAdmin, '_')
+ISNULL (DBCreator, '_')
+ISNULL (BulkAdmin, '_')
+ISNULL (ControlServer, '_')
FROM SQLMonitor.dbo.tbl_ServerLevelSecurity_Current_HIST WHERE CollectDate = 
@Date) )

BEGIN

   DECLARE 
   @html NVARCHAR(MAX), 
   @table NVARCHAR(MAX)

   SET @html = N'<html><head><title>Server Level Security Audit 
   Differences</title></head>' + CHAR(10) +
                       N'<body style="font-family: Arial" 
                       bgcolor="#FFFFCC">' +
                       N'<h2>Added or Changed</h2>' +
                       N'<table border="1">' +
                       N'<tr>' +
                       N'<th width="120">Server Name</th>' +
                       N'<th width="120">Name</th>' +
                       N'<th width="500">Type Desc</th>' +
                       N'<th width="500">Public</th>' +
                       N'<th width="500">SysAdmin</th>' +
                       N'<th width="500">SecurityAdmin</th>' +
                       N'<th width="500">ServerAdmin</th>' +
                       N'<th width="500">SetupAdmin</th>' +
                       N'<th width="500">ProcessAdmin</th>' +
                       N'<th width="500">DiskAdmin</th>' +
                       N'<th width="500">DBCreator</th>' +
                       N'<th width="500">BulkAdmin</th>' +
                       N'<th width="500">ControlServer</th>' +
                       N'</tr>' + CHAR(10);


   SELECT @table = CONVERT(varchar(max),
 (SELECT 
 td = NEW.ServerName,
 '',
 td = NEW.Name,
 '',
 td = NEW.Type_Desc,
 '',
 td = 'Public was ' + ISNULL(OLD.[Public], 'NULL') + ' and Public is now ' + ISNULL(NEW.[Public], 'NULL'),
 '',
 td = 'SysAdmin was ' + ISNULL(OLD.SysAdmin, 'NULL') + ' and SysAdmin is now ' + ISNULL(NEW.SysAdmin, 'NULL'),
    '',
 td = 'SecurityAdmin was ' + ISNULL(OLD.SecurityAdmin, 'NULL') + ' and SecurityAdmin is now ' + ISNULL(NEW.SecurityAdmin, 'NULL'),
 '',
 td = 'ServerAdmin was ' + ISNULL(OLD.ServerAdmin, 'NULL') + ' and ServerAdmin is now ' + ISNULL(NEW.ServerAdmin, 'NULL'),
 '',       
 td = 'SetupAdmin was ' + ISNULL(OLD.SetupAdmin, 'NULL') + ' and SetupAdmin is now ' + ISNULL(NEW.SetupAdmin, 'NULL'),
 '',
 td = 'ProcessAdmin was ' + ISNULL(OLD.ProcessAdmin, 'NULL') + ' and ProcessAdmin is now ' + ISNULL(NEW.ProcessAdmin, 'NULL'),
 '',
 td = 'DiskAdmin was ' + ISNULL(OLD.DiskAdmin, 'NULL') + ' and DiskAdmin is now ' + ISNULL(NEW.DiskAdmin, 'NULL'),
 '',
 td = 'DBCreator was ' + ISNULL(OLD.DBCreator, 'NULL') + ' and DBCreator is now ' + ISNULL(NEW.DBCreator, 'NULL'),
 '',
 td = 'BulkAdmin was ' + ISNULL(OLD.BulkAdmin, 'NULL') + ' and BulkAdmin is now ' + ISNULL(NEW.BulkAdmin, 'NULL'),
 '',
 td = 'ControlServer was ' + ISNULL(OLD.ControlServer, 'NULL') + ' and ControlServer is now ' + ISNULL(NEW.ControlServer, 'NULL')

FROM SQLMonitor.dbo.tbl_ServerLevelSecurity_Current NEW
   LEFT JOIN SQLMonitor.dbo.tbl_ServerLevelSecurity_Current_HIST OLD 
   ON OLD.ServerName = NEW.ServerName
   WHERE OLD.CollectDate = @Date and
ISNULL (new.ServerName, '_') +ISNULL (new.name, '_') +ISNULL (new.Type_Desc, '_') +ISNULL (new.[Public], '_') +ISNULL (new.SysAdmin, '_') +ISNULL (new.SecurityAdmin, '_') +ISNULL (new.ServerAdmin, '_') +ISNULL (new.SetupAdmin, '_') +ISNULL (new.ProcessAdmin, '_') +ISNULL (new.DiskAdmin, '_') +ISNULL (new.DBCreator, '_') +ISNULL (new.BulkAdmin, '_') +ISNULL (new.ControlServer, '_') <> ISNULL  (old.ServerName, '_') +ISNULL (old.name, '_') +ISNULL (old.Type_Desc, '_') +ISNULL (old.[Public], '_') +ISNULL (old.SysAdmin, '_') +ISNULL (old.SecurityAdmin, '_') +ISNULL (old.ServerAdmin, '_') +ISNULL (old.SetupAdmin, '_') +ISNULL (old.ProcessAdmin, '_') +ISNULL (old.DiskAdmin, '_') +ISNULL (old.DBCreator, '_') +ISNULL (old.BulkAdmin, '_') +ISNULL (old.ControlServer, '_')


   FOR XML PATH(N'tr'), TYPE));

SET @html = @html + @table + CHAR(10) + 
        N'</table></body></html>';

   EXEC msdb.dbo.sp_send_dbmail
   @recipients = '[email protected]',
   @body_format =  'HTML',
   @body= @HTML,
   @subject = 'Server Level Security Audit Difference - Added or Changed',
   @profile_name = 'DBA'

  END

  --SendMail

我想更改我的存储过程，以便它在无法连接到链接服务器后继续执行。我怎样才能更好地编写这个存储过程？

我正在使用 TRY...CATCH 来查询我正在从列表中创建的服务器，以跟踪事务日志备份历史记录。

ALTER PROC [dbo].[spLogBackup]

AS

TRUNCATE TABLE dbo.tLogBackup

DECLARE servers_cursor CURSOR
FOR
select  distinct LinkedserverName
from dbo.Environment
join master..sysservers on srvname COLLATE DATABASE_DEFAULT  = 
LinkedserverName COLLATE DATABASE_DEFAULT
where LinkedServerName not in ('TestDB')

and ServerUse in ('PROD', 'DR')
and IncludeInstats = 1
order by LinkedServerName

OPEN servers_cursor
DECLARE @Servername varchar(250)

DECLARE @sql varchar(8000)

FETCH NEXT FROM servers_cursor INTO @ServerName
WHILE (@@FETCH_STATUS = 0)
BEGIN TRY
--Looping throught the servers.
set @sql = 'insert into dbo.tLogBackup (servername, rundate, runtime, 
jobname, Status, CurrentStatus, enabled) select ''' + @ServerName + 
''',last_run_date, last_run_time, name, last_run_outcome, 
 current_execution_status, enabled
 from openquery([' + @ServerName + '],''exec 
 msdb.dbo.sp_get_composite_job_info'' )'
 print @ServerName
exec (@sql)
--SELECT (@sql)
FETCH NEXT FROM servers_cursor INTO @ServerName
END TRY

BEGIN CATCH  
SELECT  
    ERROR_NUMBER() AS ErrorNumber  
    ,ERROR_SEVERITY() AS ErrorSeverity  
    ,ERROR_STATE() AS ErrorState  
    ,ERROR_PROCEDURE() AS ErrorProcedure  
    ,ERROR_MESSAGE() AS ErrorMessage;  
END CATCH; 

CLOSE servers_cursor
DEALLOCATE servers_cursor

我想更改我的存储过程，使其不再使用游标来运行。我怎样才能更好地编写这个存储过程？

我正在使用游标来编译动态 SQL 以查询我从列表创建的服务器以跟踪事务日志备份历史记录。

ALTER PROC [dbo].[spLogBackup]

AS

TRUNCATE TABLE dbo.tLogBackup

DECLARE servers_cursor CURSOR
FOR
select  distinct LinkedserverName
from dbo.Environment
join master..sysservers on srvname COLLATE DATABASE_DEFAULT  = LinkedserverName COLLATE DATABASE_DEFAULT
where LinkedServerName not in ('TestDB')

and ServerUse in ('PROD', 'DR')
and IncludeInstats = 1
order by LinkedServerName

OPEN servers_cursor
DECLARE @Servername varchar(250)

DECLARE @sql varchar(8000)

FETCH NEXT FROM servers_cursor INTO @ServerName
WHILE (@@FETCH_STATUS = 0)
BEGIN
    --Looping throught the servers.
    set @sql = 'insert into dbo.tLogBackup (servername, rundate, runtime, jobname, Status, CurrentStatus, enabled) select ''' + @ServerName + ''',last_run_date, last_run_time, name, last_run_outcome, current_execution_status, enabled
     from openquery([' + @ServerName + '],''exec msdb.dbo.sp_get_composite_job_info'' )'
    print @ServerName
    exec (@sql)
    --SELECT (@sql)
  FETCH NEXT FROM servers_cursor INTO @ServerName
END
CLOSE servers_cursor
DEALLOCATE servers_cursor