SQL Server common errors and their solutions.

Hi All,

I am just sharing my solutions for the errors which might come to you while dealing with SQL 2005. I come across these issues and solved them by referring forums, some blogs, MSDN. I realised it is also better if we get all our solution at one place instead just getting frustrated when things doen’t work.

Error 1: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. (Microsoft SQL Server, Error: -2)

Solution: This error occurs when firewall on the server refuses the connection. You should be careful and should configure the firewall on the server to accept connections.

Error 2: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 28 - Server doesn't support requested protocol) (Microsoft SQL Server, Error: -1

Solution: There could be many possible reason of the above error.

The firewall on the server if refuses the connection.

If the SQL Server instance name given is not valid.

If the sqlbrower service is not started,

To fix the error you need to appaoch the following actions as part of your solution.

Configure the firewall on the server instance of SQL server to open SQL Server browser port.

Run the server Sql Server browser

Check the Sql Server instance name which you have specified in your connection string.

Enable the Sql Server remote conection using Sql Server Surface Area configuration

Error 3: Case expressions may only be nested to level %d.

Solution: SQL Server 2005 allows only 10 levels of nesting in CASE expressions, So you should be careful while writing a Sql script. You have to reduce the case statements to 10 or less.

Error 4: A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.) (Microsoft SQL Server, Error: 233

Solution: This is very common error usually appears for each developer once in a lifetime. You just need to use Sql Server area configuration to accept remote connections.

Error 5: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: TCP Provider, error: 0 - No connection could be made because the target machine actively refused it.) (Microsoft SQL Server, Error: 10061)

Solution: Dear you fogot to start your server, start it now and you are done.

Error 6: Could not obtain exclusive lock on database '%.*ls'. Retry the operation later.


Solution: Sometime a database operation may require exclusive access to it. In that case you need to make sure that the connections to the database is not shared., Make sure you disconnect all the existing connectiosn to that database and trye again, I hope you are done it.

Error 7: Cannot open user default database. Login failed.

Solution: The SQL Server login was unable to connect because of a problem with its default database. Either the database itself is invalid or the login lacks CONNECT permission on the database, Use ALTER LOGIN to change the login's default database. Grant CONNECT permission to the login.

Error 8: Attempting to restore this backup may encounter storage space problems. Subsequent messages will provide details.

Solution: Starting with SQL Server 2005, The RESTORE VERIFYONLY statement checks the available storage space on the disk to which the database is to be restored.

The available disk space may be insufficient to restore the backup being verified. Restore the backup to a location with sufficient disk space, or provide more space on the disk.

Error 9: The repair statement was not processed. The database cannot be in read-only mode.

Solution: This error happenes when one try to repair the database and the database is in readonly mode. You should make sure that the database also have write access to. To do it set the databasse to read-write by using Alter Database and then return the DBCC command