RSS

Category Archives: Errors

Errors In sql server 2008 and 2005 and How to slove this errors

Cannot add new node in SQL Server 2014

Problem 

Hi my followers today i have task to build two new SQL Server Clusters when i am working i go to for adding new Node at this time i revived very new issue for me Rule “SQL Server Database Services feature state” failed

—————————
Rule Check Result
—————————
Rule “SQL Server Database Services feature state” failed. The SQL Server Database Services feature failed when it was initially installed. The feature must be removed before the current scenario can proceed.
—————————
OK
—————————

This error meaning When you are adding the first node the cluster installed with some issues and this relay what is happened with me when i installed the First node i faced some issues so based on that adding second node is blocked

How i can fix this issue :

  • To fix this issue at the first you should check the Cluster error log in the first node and try to do prepare for this node

If this step not success with you to fix this issue you can try the second solution :

  1. Go for the Run
  2. Write Regedit
  3. Go for this path (HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQLServer\MSSQL12.MSSQLSERVER2014\ConfigurationState )
  4. Check all the value for all the Component if you found the value is 2 this meaning you have failures in this Component change it to 1

Component name :

MPT_AGENT_CORE_CNI, SQL_Engine_Core_Inst, SQL_FullText_Adv, SQL_Replication_Core_Inst

Note : to find the correct place in the registry you  after this path (HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQLServer\) we have to know Our SQL Server Version and SQL Server instance name

  • SQL Server 2008     : MSSQL10.      (InstanceName)
  • SQL Server 2008R2 : MSSQL10_50. (InstanceName)
  • SQL Server 2012     : MSSQL11 .     (InstanceName)
  • SQL Server 2014     : MSSQL12.      (InstanceName)

I hope the issue and How to fix the issue is clear

Follow Us :

LinkedIn Slideshare ,Youtube Channel.MSDN POSTS , WHO WE ARE

 
Leave a comment

Posted by on September 16, 2015 in Errors

 

Tags: , , , , , , , , , , ,

SQL Server Configuration Manger Error

Today i face error when i need to check my SQL Server Configuration manger for SQL Server 2008R2 Sp1 When the SQL Server Services “Remote Procedure Call Failed”

Error :

SQLConfig Error

 

How to Fix this Error ?

I try more salutation but the best one of them is “Upgrade SQL Server Services from SP1 to SP2” after i run this update this error is go out to download this Update go to this link

http://www.microsoft.com/en-us/download/details.aspx?id=30437

 

 

 
Leave a comment

Posted by on May 16, 2013 in Errors

 

How to install Adventure Work Database

Problem :

When I download Database Adventure Work  I see it MDF file only ok but now how can I attach this database without LDF file . SQL Server give me ERROR

AdventureWork Without LDF File

 

 

ERROR

 

Solution : 

to fix this error and install the database on the production we will create the LDF file by this script .

 

Create Database AdventureWorks2008R2

ON (FILENAME=‘E:\AdventureWorks2008R2_Data.mdf’)

FOR ATTACH_REBUILD_LOG

by this Script I Create Database AdventureWorks2008R2 and put the File name for the MDF file the ( Full path ) then I make REBUILD for the LOG by this Command  (ATTACH_REBUILD_LOG)

Now after you EXCE this Query Check the path of the MDF file you will see the new File LDF for the database adventure work also you check the Databases on SQL Server management studio you will find the Database ADVENTUREWORK2008R2

Regards ,

Eng. Mustafa Elmasry

Database Administrator

 

 

 
1 Comment

Posted by on March 4, 2013 in Errors

 

TRY…CATCH and ERROR Handling

CREATE TABLE TPA
(ID  INT , [NAME]  NVARCHAR(50))
GO
INSERT TPA VALUES (1,’MOSTAFA’)
INSERT TPA VALUES (2,’ELMASRY’)

BEGIN TRY
— Generate a divide-by-zero error.
SELECT ID+[NAME] FROM TPA
END TRY
BEGIN CATCH
SELECT * FROM TPA
END CATCH;
GO

BEGIN TRY
— Generate a divide-by-zero error.
SELECT ID+[NAME] FROM TPA
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage;
END CATCH;
GO

 
Leave a comment

Posted by on February 15, 2012 in Administration, Errors

 

Repairing Suspect SQL Database

Before you run this script

  • Kill all connections and stop SQL Server
  • Perform a Checkdisk with repair option
  • Perform a Defragmentation
  • Now copy the files necessary (MDF, LDF and Backups)
  • Perform a backup
  • Restore the backup
  • Repair the Database (DBCC CHECKDB)
  • Backup the repaired DB

USE [master]
GO
ALTER DATABASE <dbname> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE <dbname> SET SINGLE_USER
GO
DBCC CHECKDB <dbname>, REPAIR_REBUILD) WITH ALL_ERRORMSGS, NO_INFOMSGS
GO
ALTER DATABASE <dbname> SET MULTI_USER
GO

Or

Take Care From this Script becouse it will make repaire and may be some Data will loss

EXEC sp_resetstatus ‘yourDBname’;
ALTER DATABASE yourDBname SET EMERGENCY
DBCC checkdb(‘yourDBname’)
ALTER DATABASE yourDBname SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB (‘yourDBname’, REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE yourDBname SET MULTI_USER

 
2 Comments

Posted by on February 14, 2012 in Administration, Errors

 

Error 22022 SQL Server Agent not running

When you run a SQL Server Agent job in Microsoft SQL Server 2000, you may receive the following error message:
Error 22022: SQLServerAgent is not currently running so it cannot be notified of this action.”
If you receive this error message, first make sure that the SQL Server Agent service is running. To do this, follow these steps:

1.Click Start, click Run, type Services.msc, and then click OK.
2.In the Services window, locate the SQL Server Agent service.
3.Make sure that the value of the Status column of the SQL Server Agent service is Running.
then
EXEC sp_configure ‘show advanced’, 1;
RECONFIGURE;
EXEC sp_configure ‘allow updates’, 0;
RECONFIGURE;
EXEC sp_configure ‘Agent XPs’, 1;
RECONFIGURE;
GO

 

 
10 Comments

Posted by on October 14, 2011 in Errors

 

Enable Remote Connection on SQL Server 2008

“Cannot connect to SQL-Server-Instance-Name
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)”

 

 

 

 

 

“Cannot connect to SQL-Server-Instance-Name
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: 26 – Error
Locating Server/Instance Specified) (Microsoft SQL Server)”

 

 

 

 

 

“Cannot connect to SQL-Server-Instance-Name
Login failed for user ‘username‘. (Microsoft SQL Server, Error: 18456)”

 

 

 

 

To enable remote connection on SQL Server

  1. Start SQL Server Browser service if it’s not started yet. SQL Server Browser listens for incoming requests for Microsoft SQL Server resources and provides information about SQL Server instances installed on the computer.
  2. Enable TCP/IP protocol for SQL Server 2008 to accept remote connection.
  3. (Optional)
    Change Server Authentication to SQL Server and Windows Authentication. By default, SQL Server 2008 allows only Windows Authentication mode so you can connect to the SQL Server with current user log-on credential. If you want to specify user for connect to the SQL Server, you have to change Server Authentication to SQL Server and Windows Authentication.

Note: In SQL Server 2008 , there isn’t SQL Server Surface Area Configuration so you have to configure from SQL Server Configuration Manager instead.
Step-by-step

1- Open SQL Server Configuration Manager. Click Start -> Programs-> Microsoft SQL Server 2008 -> Configuration Tools -> SQL Server Configuration Manager.

 

 

 

 

 

 

 

 

2- On SQL Server Configuration Manager, select SQL Server Services on the left window. If the state on SQL Server Browser is not running, you have to configure and start the service

 

 

 

 

 

 

 

 

 

 

3- Double-click on SQL Server Browser, the Properties window will show up. Set the account for start SQL Server Browser Service. In this example, I set to Local Service account.

 

 

 

 

 

 

 

 

 

 

 

 

4- On SQL Server Browser Properties, move to Service tab and change Start Mode to Automatic. Therefore, the service will be start automatically when the computer starts. Click OK to apply changes.

 

 

 

 

 

 

 

 

 

 

 

5- Back to SQL Server Configuration Manager, right-click on SQL Server Bowser on the right window and select Start to start the service.

 

 

 

 

 

 

 

 

 

 

 

6-On the left window, expand SQL Server Network Configuration -> Protocols for SQLEXPRESS. You see that TCP/IP protocol status is disabled.

 

 

 

 

 

 

 

 

 

 

 

 

7- Right-click on TCP/IP and select Enable to enable the protocol.

 

 

 

 

 

 

 

 

 

 

 

 

8- On the left window, select SQL Server Services. Select SQL Server (INSTANCE NAME) on the right window -> click Restart. The SQL Server service will be restarted.

 

 

 

 

 

 

 

 

 

 

 

 

9-Open Microsoft SQL Server Management Studio and connect to the SQL Server 2008 Express.

10- Right-click on the SQL Server Instance and select Properties.

 

 

 

 

 

 

 

 

 

 

 

 

 

11- On Server Properties, select Security on the left window. Then, select SQL Server and Windows Authentication mode.

12 -Right-click on the SQL Server Instance and select Restart.