--------------------------------------------------------------------------------
--  File        :   Demo 04_Configure error 1205 for logging,2005+.sql
--
--  Purpose     :   This script configures SQL Server to write the deadlock 
--                  error message (1205) to the SQL Server error log and the 
--                  Windows application event log each time that it is raised.
--
--                  See "sp_altermessage (Transact-SQL)" <http://msdn.microsoft.com/en-us/library/ms175094(SQL.90).aspx>
--                  for more information.
--
--                  NOTE: To undo the change, run the following T-SQL:
--
--                      EXECUTE [master].[sys].[sp_altermessage]
--                          @message_id = 1205,
--                          @parameter = 'WITH_LOG',
--                          @parameter_value = 'false';
--
--  SQL Server  :   SQL Server 2005 (9.0)
--                  SQL Server 2008 (10.0)
--                  SQL Server 2008 R2 (10.5)
--
--  Version     :   3.0006.0090.0019 (2010-05-31)
--------------------------------------------------------------------------------

USE [tempdb];

GO

--  NOTE: In SQL Server 2000 and prior, the [sp_altermessage] system stored 
--  procedure was routinely used to modify error messages, system and user 
--  alike, to change whether they would be logged to the SQL Server error log 
--  and Windows application event log.  However, this functionality was 
--  removed in SQL Server 2005:
--      "sp_altermessage (Transact-SQL)" <http://msdn.microsoft.com/en-us/library/ms175094(SQL.90).aspx>
--          "System messages cannot be modified by using sp_altermessage."
--      "FIX: Lots of messages that have message ID 19030 and message ID 19031 are logged in the SQL Server 2005 Errorlog file when you use SQL Server Profile in SQL Server 2005" <http://support.microsoft.com/default.aspx?scid=kb;en-us;922578>
--          "In earlier versions of SQL Server, the sp_altermessage stored 
--          procedure can suppress system messages that have message IDs that 
--          are less than 50000. When these messages are suppressed, the 
--          messages are not written to the SQL Server Errorlog file. However, 
--          this behavior is not supported in SQL Server 2005."
--  The SQL Server community made numerous requests to have that functionality 
--  restored:
--      "sp_altermessage no longer able to enable/disable errors" <http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=125436>
--      "Alert does not fire" <http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=267531>
--      "Bug reported fixed in SP2 is not fixed. affects administration, replication, etc" <http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=270385>
--      "Deadlock alert logging enabled" <http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=294122>
--  Microsoft returned this valuable functionality in the latest service packs 
--  (as of this presentation) for SQL Server 2005 (SP3) and SQL Server 2008 
--  (SP1):
--      "List of the bugs that are fixed in SQL Server 2005 Service Pack 3" <http://support.microsoft.com/default.aspx?scid=kb;en-us;955706>
--          "The sp_altermessage stored procedure can now be used to enable or 
--          disable system messages to the Windows event log in SQL Server 
--          2005"
--      "List of the bugs that are fixed in SQL Server 2008 Service Pack 1" <http://support.microsoft.com/default.aspx?scid=kb;en-us;968369>
--          "Resolutions to the following issues are also included in SQL 
--          Server 2008 SP1.
--          [...]
--          You cannot use the sp_altermessage stored procedure to enable or 
--          disable system messages to the Windows event log in SQL Server 
--          2008."

--  Values used when checking the version of SQL Server running on the 
--  instance.
DECLARE @InstanceVersionNumberString VarChar(20);
DECLARE @InstanceVersionNumber_Major Int;
DECLARE @InstanceVersionNumber_Minor Int;
DECLARE @InstanceVersionNumber_Build Int;
DECLARE @Delimiter1_Position TinyInt;
DECLARE @Delimiter2_Position TinyInt;
DECLARE @Delimiter3_Position TinyInt;

--  Store the version number for the SQL Server instance.
SET @InstanceVersionNumberString = Cast(ServerProperty('ProductVersion') AS VarChar);

--  Determine the locations of the periods that delimit the parts of the 
--  version number.
SET @Delimiter1_Position = CharIndex('.', @InstanceVersionNumberString);
SET @Delimiter2_Position = CharIndex('.', @InstanceVersionNumberString, @Delimiter1_Position + 1);
SET @Delimiter3_Position = CharIndex('.', @InstanceVersionNumberString, @Delimiter2_Position + 1);

--  Parse the version number into its components.
SET @InstanceVersionNumber_Major = Cast(Left(@InstanceVersionNumberString, (@Delimiter1_Position - 1)) AS Int);
SET @InstanceVersionNumber_Minor = Cast(SubString(@InstanceVersionNumberString, (@Delimiter1_Position + 1), ((@Delimiter2_Position - @Delimiter1_Position) - 1)) AS Int);
SET @InstanceVersionNumber_Build = Cast(SubString(@InstanceVersionNumberString, (@Delimiter2_Position + 1), ((@Delimiter3_Position - @Delimiter2_Position) - 1)) AS Int);

--  Verify that the appropriate service pack has been applied to the version.
IF
(
    (
        (@InstanceVersionNumber_Major = 9)
        AND
        (@InstanceVersionNumber_Build >= 4035)
    )
    OR
    (
        (@InstanceVersionNumber_Major = 10)
        AND
        (
			(
				(@InstanceVersionNumber_Minor = 0)
				AND
				(@InstanceVersionNumber_Build >= 2531)
			)
			OR
			(@InstanceVersionNumber_Minor >= 50)
		)
    )
)
BEGIN
    --  First, display the 1205 error message record before any changes have 
    --  been made.
    SELECT [message_id],
        [language_id],
        [severity],
        [is_event_logged],
        [text]
    FROM [master].[sys].[messages]
    WHERE
    (
        ([message_id] = 1205)
        AND
        ([language_id] = 1033)
    );

    --  Change the error message so that it is always logged.
    EXECUTE [master].[sys].[sp_altermessage]
        @message_id = 1205,
        @parameter = 'WITH_LOG',
        @parameter_value = 'true';

    --  Finally, display the 1205 error message record after the change has 
    --  been made.
    SELECT [message_id],
        [language_id],
        [severity],
        [is_event_logged],
        [text]
    FROM [master].[sys].[messages]
    WHERE
    (
        ([message_id] = 1205)
        AND
        ([language_id] = 1033)
    );
END
ELSE
BEGIN
    DECLARE @ErrorMessage NVarChar(255);

    --  Tailor the error message to the version of SQL Server running on the 
    --  instance.
    IF (@InstanceVersionNumber_Major = 9)
    BEGIN
        SET @ErrorMessage = N'Service Pack 3 (or higher), for SQL Server 2005, is required to use the [sp_altermessage] system stored procedure to modify system error messages.';
    END
    ELSE
    BEGIN
        SET @ErrorMessage = N'Service Pack 1 (or higher), for SQL Server 2008, is required to use the [sp_altermessage] system stored procedure to modify system error messages.';
    END;

    --  Display the error message.
    RAISERROR
    (
        @ErrorMessage,
        16,
        1
    );
END;

GO
