USE master
GO

IF OBJECT_ID(N'dbo.Usp_SQLErrorLogReportServer', N'P') IS NOT NULL
  DROP PROCEDURE dbo.Usp_SQLErrorLogReportServer
GO

CREATE PROCEDURE dbo.Usp_SQLErrorLogReportServer
AS

/**********************************************************************************
**                                                                               **
**   Copyright © Richard Ding, 2008                                              **
**   Object:        stored procedure "Usp_SQLErrorLogReportServer"               **
**   Created on:    2/14/2008                                                    **
**   Modified on:   2/18/2008                                                    **
**   Usage:         to automate SQL Error Log reporting, runs on server          **
**   Requirement:   1. Ensure odbcping.exe exists at C:\ root and each client    **
**                     server has a valid login                                  **
**                  2. Linked servers connecting all client servers are working  **
**                  3. Must be created on SQL 2005 server                        **
**                  4. Make sure SQL Server Database Mail is working             **
**                  5. Need to update sp_send_dbmail parameters                  **
**                                                                               **
**********************************************************************************/

SET NOCOUNT ON

IF OBJECT_ID (N'tempdb.dbo.Tbl_SQLErrorLogReport') IS NULL
  CREATE TABLE tempdb.dbo.Tbl_SQLErrorLogReport 
  (
    Id int identity, 
    ServerName nvarchar(255), 
    LogDate datetime, 
    ProcessInfo nvarchar(25), 
    LogText nvarchar(3984), 
    ContinuationRow int
  )
ELSE
  TRUNCATE TABLE tempdb.dbo.Tbl_SQLErrorLogReport

IF OBJECT_ID(N'tempdb.dbo.#tmp') IS NULL
  CREATE TABLE dbo.#tmp (Cmd nvarchar(2000))
ELSE 
  TRUNCATE TABLE dbo.#tmp

DECLARE @ServerName nvarchar(255), 
        @Version nchar(2), 
        @VersionString nvarchar(300), 
        @Cmd nvarchar(800), 
        @Status nvarchar(10)
SELECT @ServerName = N'', 
       @Version = N'', 
       @VersionString = N'', 
       @Cmd = N'', 
       @Status = N''

WHILE 1 = 1
  BEGIN
    SELECT TOP 1 @ServerName = [name]
    FROM sys.servers
    WHERE is_linked = 1 
    AND [name] > @ServerName 
    ORDER BY [name] ASC

    IF @@rowcount = 0
      BREAK

    SELECT @Cmd = N'master.dbo.xp_Cmdshell ''C:\odbcping /S'
                   + @ServerName + N' /UOdbcpingLogin /PPswd2008'''

    TRUNCATE TABLE #tmp
    INSERT INTO #tmp (Cmd) EXEC (@Cmd)

    IF EXISTS (SELECT 0 FROM #tmp WHERE Cmd LIKE N'%CONNECTED TO SQL SERVER%')
      BEGIN
        SELECT @Status = N'UP', @VersionString = Cmd FROM #tmp WHERE Cmd LIKE N'%SQL Server Version:%'
        SELECT @Version = REPLACE(SUBSTRING(REPLACE(STUFF(@versionstring, 1, CHARINDEX(N'-', @versionstring), N''), N' ', N''), 1, 2), N'.', N'')
      END

    IF @Version = N'8'
      INSERT INTO tempdb.dbo.Tbl_SQLErrorLogReport (ServerName, LogText)
        EXEC (N'SELECT ''' + @ServerName + N''', LogText FROM [' + @ServerName + N'].tempdb.dbo.Tbl_SQLErrorLogContents ORDER BY CONVERT(datetime, SUBSTRING(LogText, 1, 19)) ASC')
    ELSE IF @Version = N'9'
      INSERT INTO tempdb.dbo.Tbl_SQLErrorLogReport (ServerName, LogDate, ProcessInfo, LogText)
        EXEC (N'SELECT ''' + @ServerName + N''', LogDate, ProcessInfo, LogText FROM [' + @ServerName + N'].tempdb.dbo.Tbl_SQLErrorLogContents ORDER BY CONVERT(datetime, LogDate) ASC')
  END

IF EXISTS (SELECT 0 FROM tempdb.dbo.Tbl_SQLErrorLogReport )
  BEGIN
    EXEC msdb.dbo.sp_send_dbmail   
      @profile_name = N'SQLServerProfile',
      @recipients = N'DBA_team@Mycompany.com', 
      @subject = N'SQL Error Log Report (for article) ', 
      @execute_query_database = N'tempdb',
      @query = N'
SET NOCOUNT ON
SET ANSI_WARNINGS OFF
DECLARE @ServerName nvarchar(255), 
        @LogDate datetime
SELECT @ServerName = N''''
WHILE 1=1
  BEGIN
    SELECT TOP 1 @ServerName = ServerName, 
                 @LogDate = MAX(LogDate) 
            FROM tempdb.dbo.Tbl_SQLErrorLogReport
           WHERE ServerName > @ServerName 
        GROUP BY ServerName 
        ORDER BY ServerName ASC
    IF @@ROWCOUNT = 0
      BREAK
    IF @LogDate IS NULL
      BEGIN
        EXEC (N''SELECT CONVERT(varchar(138), LogText) AS N''''***  Error log report for '' 
              + @ServerName + N''  ***''''
              FROM tempdb.dbo.Tbl_SQLErrorLogReport 
              WHERE ServerName = N'''''' + @ServerName + 
              N'''''' ORDER BY Id ASC '')
      END
    ELSE 
      BEGIN
        EXEC (N''PRINT ''''***  Error log report for '' + @ServerName + N''  ***''''
              SELECT LogDate, CONVERT(varchar(11), ProcessInfo) AS ''''ProcessInfo'''', 
              CONVERT(varchar(102), LogText) AS ''''Text'''' 
              FROM tempdb.dbo.Tbl_SQLErrorLogReport 
              WHERE ServerName = '''''' + @ServerName + 
              '''''' ORDER BY CONVERT(datetime, LogDate) ASC '')
      END
    PRINT ''''
    PRINT ''''
  END
'
  END

RETURN (0)
GO

GRANT EXECUTE ON master.dbo.Usp_SQLErrorLogReportServer TO public
GO