Error 27506

Introduction 

One of my friend want a solution from me related to scripts compilation error. He is going to compile a batch job of some SQL scripts, Procedure etc and found the error mentioned bellow.

Error 27506. Error executing SQL script test.sql. Line 76. During undo of a logged operation in database 'Test_DB', an error occurred at log record ID(38256:32:22). The row was not found. Restore the database from a full backup, or repair the…

The Reason of this Error

The reason from this error is Data Corruption in Database

Possible Solutions

Follow the Tasks given bellow

1.    Backup the Database

2.    Run the following query

ALTER DATABASE  test_DB  SET SINGLE_USER

GO

DBCC CHECKDB (' test_DB ', REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS, NO_INFOMSGS

GO

ALTER DATABASE test_DB  SET MULTI_USER

GO

Hope it will solve the problem.

Posted by: MR. JOYDEEP DAS

Custom Error Messages

In my previous article I am trying to describe the error handling process of Microsoft SQL Server 2000/2005/2008/2012.

You can read it from…

Error handling

http://sqlknowledgebank.blogspot.in/2012/06/error-handling.html

 In this article I am trying to illustrate the point related to custom error messages.

Custom error messages allow us to design more business specific error messages. Each custom error message has a severity assignment, which determines how important the error is and identifies how it should be handled. Some error messages are simply informational and are not even captured by error handling. Other error messages are very severe and immediately kill the process on which the statement was executed.

Defining custom error messages

To define a custom error message in SQL Server 2005, we can use the stored procedure named sp_addmessage, which adds a record to the sys.messages system view.

The syntax is mentioned bellow

sp_addmessage [ @msgnum = ] msg_id , 

              [ @severity = ] severity ,

              [ @msgtext = ] 'msg'

              [ , [ @lang = ] 'language' ]

              [ , [ @with_log = ] { 'TRUE' | 'FALSE' } ]

              [ , [ @replace = ] 'replace' ]

Arguments

[ @msgnum = ] msg_id

Is the ID of the message. msg_id is int with a default of NULL. msg_id for user-defined error messages can be an integer between 50,001 and 2,147,483,647.

[ @severity = ] severity

Is the severity level of the error. severity is smallint with a default of NULL. Valid levels are from 1 through 25.

[ @msgtext = ] 'msg'

Is the text of the error message. msg is nvarchar(255) with a default of NULL.

[ @lang = ] 'language'

Is the language for this message. language is sysname with a default of NULL. Because multiple languages can be installed on the same server, language specifies the language in which each message is written. When language is omitted, the language is the default language for the session.

[ @with_log = ] { 'TRUE' | 'FALSE' }

Is whether the message is to be written to the Windows application log when it occurs. @with_log is varchar(5) with a default of FALSE. If TRUE, the error is always written to the Windows application log. If FALSE, the error is not always written to the Windows application log but can be written, depending on how the error was raised. Only members of the sysadmin server role can use this option.

[ @replace = ] 'replace'

If specified as the string replace, an existing error message is overwritten with new message text and severity level. replace is varchar(7) with a default of NULL. This option must be specified if msg_id already exists. If you replace a U.S. English message, the severity level is replaced for all messages in all other languages that have the same msg_id.

To execute this stored procedure, we need to provide an error number which will start from 50,001 to 2,147,483,647 for user-defined messages.

Example

Sample-1

USE master

GO

EXEC sp_addmessage

     50001,

     1,

     N'This message is is the Sample Error Message';

Sample-2

EXEC sp_addmessage

     50002,

     16,

     N'This actually causes an error, and is caught by error-handling';

Sample-3

EXEC sp_addmessage

     50003,

     20,

     N'This causes an error, and stops any further processing.  This is not caught by error handling.';

Calling Custom Error Messages

Now that my custom error messages are defined, we can use them inside our database engine. To invoke these errors, we’ll use the RAISERROR T-SQL construct.

RAISERROR accepts an error number, a severity level, and a state number.

Example

Sample-1

BEGIN

      BEGIN TRY

            RAISERROR  (50001,1,1) WITH LOG

      END TRY

      BEGIN CATCH

            SELECT ERROR_MESSAGE(), ERROR_NUMBER ()

      END CATCH

END

Result set:

This message is is the Sample Error Message

Msg 50001, Level 1, State 1

Sample-2

This statement invokes the second custom error message I define above. This message has a defined severity of 16, which will get caught by my CATCH statement. These types of error messages are some of the more commonly seen messages inside the SQL Server database engine.

BEGIN

      BEGIN TRY

            RAISERROR  (50002,16,1) WITH LOG

      END TRY

      BEGIN CATCH

            SELECT ERROR_MESSAGE(), ERROR_NUMBER ()

      END CATCH

END

Result set:

This actually causes an error, and is caught by error-handling

50002

Sample-3

This final snippet calls the third custom message defined above. Due to the severity level defined in this custom error, the CATCH block is not invoked; in fact, the statement and connection is immediately terminated.

BEGIN

      BEGIN TRY

            RAISERROR  (50003, 20,1) WITH LOG

      END TRY

      BEGIN CATCH

            SELECT ERROR_MESSAGE(), ERROR_NUMBER ()

      END CATCH

END

Result set:

Msg 2745, Level 16, State 2, Line 3

Process ID 52 has raised user error 50003, severity 20. SQL Server is terminating this process.

Msg 50003, Level 20, State 1, Line 3

This causes an error, and stops any further processing.  This is not caught by error handling.

Msg 0, Level 20, State 0, Line 0

A severe error occurred on the current command.  The results, if any, should be discarded.

To Alter Error Message

Alters the state of user-defined messages in an instance of the SQL Server Database Engine. User-defined messages can be viewed using the sys.messages catalog view.

Syntax

sp_altermessage [ @message_id = ] message_number, 

                [ @parameter = ] 'write_to_log', 

                [ @parameter_value = ] 'value' 

Arguments

[ @message_id = ] message_number

Is the error number of the message to alter from sys.messages. message_number is int with no default value.

[ @parameter = ] 'write_to_log'

Is used with @parameter_value to indicate that the message is to be written to the Microsoft Windows application log. write_to_log is sysname with no default value.write_to_log must be set to WITH_LOG or NULL. If write_to_log is set to WITH_LOG or NULL, and the value for @parameter_value is true, the message is written to the Windows application log. If write_to_log is set to WITH_LOG or NULL and the value for @parameter_value is false, the message is not always written to the Windows application log, but may be written depending upon how the error was raised. If write_to_log is specified, the value for @parameter_value must also be specified.

[ @parameter_value = ] 'value'

Is used with @parameter to indicate that the error is to be written to the Microsoft Windows application log. value is varchar(5), with no default value. If true, the error is always written to the Windows application log. If false, the error is not always written to the Windows application log, but may be written depending upon how the error was raised. If value is specified, write_to_log for @parameter must also be specified

Returns

It returns 0 (Success) or 1 (Failure)

Example

sp_altermessage 55001, 'WITH_LOG', 'true';

GO

To DROP a specified Error Message

Drops a specified user-defined error message from an instance of the SQL Server Database Engine. User-defined messages can be viewed using the sys.messages catalog view.

Syntax:

sp_dropmessage [ @msgnum = ] message_number

               [ , [ @lang = ] 'language' ]

Arguments

[ @msgnum = ] message_number

Is the message number to drop. message_number must be a user-defined message that has a message number greater than 50000. message_number is int, with a default of NULL.

[ @lang = ] 'language'

Is the language of the message to drop. If all is specified, all language versions of message_number are dropped. language is sysname, with a default of NULL.

Example:

USE master;

GO

EXEC sp_dropmessage 50001;

Hope you like it.

Posted by: MR. JOYDEEP DAS

Error handling

Error handling is a very important part of T-SQL development life cycle. Before SQL Server 2005 the error handling is quite difficult to maintain.
From SQL Server 2005 Microsoft provide some easier process to handle our T-SQL error properly. In this article we are discussing about the Error handling process of SQL Server.


The error information that SQL Server passes to the client consists of several components, and the client is responsible for the final interpretation of the message. These are the components that SQL Server passes to the client.

Message number 

Each error message has a number. We can find most of the message numbers in the table sysmessages in the master database. But there some special numbers like 0 and 50000 that do not appear there.

Severity level 

A number range from 0 to 25. This is that if the severity level is in the range 0-10, the message is informational or a warning, and not an error. Errors resulting from programming errors in our SQL code have a severity level in the range 11-16. Severity levels 17-25 indicate resource problems, hardware problems or internal problems in SQL Server, and if the severity is 20 or higher, the connection is terminated. For system messages we can find the severity level in master ..sysmessages.

State 

A number range from 0 to 127. The meaning of this item is specific to the error message, but Microsoft has not documented these values.

Procedure 

In which stored procedure, trigger or user-defined function the error occurred. Blank if the error occurred in a plain batch of SQL statements (including dynamic SQL).

Line 

The line number within the procedure/function/trigger/batch the error occurred. A line number of 0 indicates that the problem occurred when the procedure was invoked.

Message text

This is the actual text of the message that tells us what went wrong. We can find this text in master..sysmessages, or rather a template for it, with placeholders for names of databases, tables etc.

Error handling process in Microsoft SQL Server 2000


As our main concern is to demonstrate the error handling process at MS SQL Server 2005 and above, we are not going to depth of MS SQL Server 2000 error handling process.


@@error


Microsoft SQL Server sets the global variable @@error to 0, unless an error occurs, in which case @@error is set to the number of that error. @@error will hold the number of that message.
Now we are trying to generate error and display the error message by @@error global variable.

CREATE TABLE notnull(a int NOT NULL)

DECLARE @err int,

        @value int

       

INSERT notnull VALUES (@value)

SELECT @err = @@error

IF @err <> 0

   PRINT '@err is ' + ltrim(str(@err)) + '.'

Msg 515, Level 16, State 2, Line 5

Cannot insert the value NULL into column 'a', table 'MAHAGUN.dbo.notnull'; column does not allow nulls. INSERT fails.

The statement has been terminated.

@err is 515.

@@rowcount 

This is a global variable reports the number of affected rows in the most recently executed statement. Just like @@error we need to save it in a local variable if we want to use the value later, since @@rowcount is set after each statement. Since with SET we can only assign variable at a time, we must use the SELECT if you need to save both @@error and @@rowcount into local variables:

SELECT @err = @@error, @rowc = @@rowcount


@@trancount


This is a global variable which reflects the level of nested transactions. Each BEGIN TRANSACTION increases @@trancount by 1, and each COMMIT TRANSACTION decreases @@trancount by 1. Nothing is actually committed until @@trancount reaches 0. ROLLBACK TRANSACTION rolls back everything to the outermost BEGIN TRANSACTION (unless we have used the fairly exotic SAVE TRANSACTION), and forces @@trancount to 0, regards of the previous value.


Error handling process in Microsoft SQL Server 2005


The Microsoft SQL Server 2005 provides TRY… CATCH mechanism to handle with error. It is so effective and easy to use for T-SQL developer.


The syntax of the blogs is mentioned bellow.

BEGIN

     BEGIN TRY

         BEGIN TRANSACTION

         -- TSQL Starements

         COMMIT TRANSACTION

     END TRY

    

     BEGIN CATCH

         -- Error Handling Code

         ROLLBACK TRANSACTION

     END CATCH

END

Here in this block of BEGIN TRY and END TRY if any kind of error occurs it directly jump to the BEGIN CATCH and END CATCH block and handle the error.

The values that can be retrieved from the error are also much more detailed, then what you could get with previous versions of SQL Server.  Below is a list of the data that can be retrieved when an error occurs.

• ERROR_NUMBER() - returns the number of the error.
• ERROR_SEVERITY() - returns the severity.
• ERROR_STATE() - returns the error state number.
• ERROR_PROCEDURE() - returns the name of the stored procedure or trigger where the error occurred.
• ERROR_LINE() - returns the line number inside the routine that caused the error.
• ERROR_MESSAGE() - returns the complete text of the error message.

A sample code of calling stored procedure using error handling technique is mentioned bellow.

BEGIN TRY

     BEGIN TRY

          EXECUTE up_MyProc

     END TRY

     BEGIN CATCH

          SELECT

               ERROR_NUMBER() AS ErrorNumber,

               ERROR_SEVERITY() AS ErrorSeverity;

     END CATCH;

     EXECUTE up_MyProc

END TRY

BEGIN CATCH

     SELECT

          ERROR_NUMBER() AS ErrorNumber,

          ERROR_SEVERITY() AS ErrorSeverity;

END CATCH;


RAISERROR() statement


Generates an error message and initiates error processing for the session. RAISERROR can either reference a user-defined message stored in the sys.messages catalog view or build a message dynamically.

RAISERROR can be used to:

1.    Assign a specific error number, severity and state.
 

2.    Record the error in Windows Application Log.
 

3.    Return messages that contain variable text.

Example:

DECLARE @DBID INT;

SET @DBID = DB_ID();

DECLARE @DBNAME NVARCHAR(128);

SET @DBNAME = DB_NAME();

RAISERROR

    (N'The current database ID is:%d, the database name is: %s.',

    10, -- Severity.

    1, -- State.

    @DBID, -- First substitution argument.

    @DBNAME); -- Second substitution argument.

GO


Error handling process in Microsoft SQL Server 2012


In Microsoft SQL server 2012 code named denali in traduced HROW statement.
The statement before the THROW statement must be followed by the semicolon (;) statement terminator.

If a TRY…CATCH construct is not available, the session is ended. The line number and procedure where the exception is raised are set. The severity is set to 16.

If the THROW statement is specified without parameters, it must appear inside a CATCH block. This causes the caught exception to be raised. Any error that occurs in a THROW statement causes the statement batch to be ended.

RAISERROR statement	THROW statement
If a msg_id is passed to RAISERROR, the ID must be defined in sys.messages.	The error_number parameter does not have to be defined in sys.messages.
The msg_str parameter can contain printf formatting styles.	The message parameter does not accept printf style formatting.
The severity parameter specifies the severity of the exception.	There is no severity parameter. The exception severity is always set to 16.

Example:

-- IN SQL Server 2005

 BEGIN TRY

         DECLARE @VarToTest INT

         SET @VarToTest = 'C'

 END TRY

 BEGIN CATCH

         DECLARE  @ErrorMessage NVARCHAR(4000),

                @ERRORSEVERITY INT

         SET @ERRORMESSAGE = ERROR_MESSAGE()

       SET @ERRORSEVERITY = ERROR_SEVERITY()

       RAISERROR (@ErrorMessage, @ErrorSeverity, 1 )

 END CATCH

-- IN SQL Server 2012

 BEGIN TRY

      DECLARE @VarToTest INT

     

      SET @VarToTest = 'C'

 END TRY

 BEGIN CATCH

      THROW

 END CATCH

Hope you like it.





Posted by: MR. JOYDEEP DAS