Microsoft SQL Server 2005 is an impeccable database server to manage and store official data. Yet, while using Microsoft SQL Server 2005 it is not necessary that you will not come across any performance error. There could be a situation when you might have to go for SQL repair to resolve SQL error. For an instance, you may receive an access violation error message when the following conditions are true:

1.You create an index that uses the IGNORE_DUP_KEY option for a table.

2.You run an INSERT statement that inserts a new row into this index.

3.The plan for the INSERT statement is parallel.

4.The value for one of the key columns of the IGNORE_DUP_KEY option is provided by using a parameter.

Other than that, you may receive an error message that is similar to the following:

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

When the condition is really critical, you need to go for SQL database repair. But, you can surely implement a method to work around this problem until you find a complete SQL database repair solution. Follow these steps:

1.Determine the problematic INSERT statement that causes the access violation.

2.Force the query optimizer to generate a serial plan for the problematic INSERT statement. Therefore, the statement can be executed on one thread.

Here, before you step ahead to force the query optimizer to generate a serial plan for the problematic INSERT statement, there are certain cases that have to be considered. Below are the three methods given, depending on various possible conditions.

Method 1

Condition: You are able to access the problematic INSERT statement.

Add the OPTION (MAXDOP 1) expression to the problematic INSERT statement. The following is an example of the new INSERT statement.

INSERT INTO MyTable (Col1, Col2)

SELECT Col1, Col2 FROM OtherTable

OPTION (MAXDOP 1)

Method 2

Condition: You are unable to access the problematic INSERT statement.

Use the sp_create_plan_guide stored procedure against the problematic INSERT statement. By doing this, you force the OPTION (MAXDOP 1) expression on the problematic INSERT statement. The following is an example of how to use the sp_create_plan_guide stored procedure.

sp_create_plan_guide @name = N'planguide_for_MyFailingProc', -- Arbitrary name

@stmt = N'INSERT INTO MyOneFailingStatementTable VALUES (@a,@b,@c)', -- The problematic INSERT statement

@type = N'OBJECT', @module_or_batch = N'dbo.MyFailingProc', -- The name of the stored procedure that contains the problematic INSERT statement

@params = NULL, @hints = N'OPTION (MAXDOP 1)'-- Query hint to prevent parallelism

Method 3

Condition: When following conditions are true:

1.The problematic INSERT statement is dynamically generated.

2.You cannot determine the INSERT statement that causes the access violation.

Under above conditions run the following Transact-SQL statements.

sp_configure 'show advanced options', 1;

GO

RECONFIGURE WITH OVERRIDE;

GO

sp_configure 'max degree of parallelism', 1;

GO

RECONFIGURE WITH OVERRIDE;

GO

Although, the given methods should help you out to resolve your trouble, but following these SQL database repair methods will not necessarily resolve your trouble. If the trouble is too critical, you may find these methods ineffective and in that case, you must go for SQL database repair. Kernel Recovery for SQL is a professional help that takes you out from any troublesome situation in no matter of time. Using Kernel Recovery for SQL, you can achieve quick and successful recovery of inaccessible SQL data.