RSS

Category Archives: Errors

Errors In sql server 2008 and 2005 and How to slove this 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.

 
 

Agent XPs Component is turend off

Cannot show requested dialog. Additional information: unable to execute requested command. ‘Agent XPs’ component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘Agent XPs’ by using sp_configure.

 

 

 

 

 

 

 

 

 

 

 

 

 

Actions Required to resolve this Error

 

sp_configure ‘show advanced options’, 1;

GO

RECONFIGURE;

GO

sp_configure ‘Agent XPs’, 1;

GO

RECONFIGURE

GO

 
Leave a comment

Posted by on October 10, 2011 in Errors

 

divide by zero during and tinyint overflow

— SET ARITHABORT AND SET ANSI_WARNINGS
——————————————————————————-
— Create tables t1 and t2 and insert data values.
CREATE TABLE t1 (
   a TINYINT,
   b TINYINT
);
CREATE TABLE t2 (
   a TINYINT
);
GO
INSERT INTO t1
VALUES (1, 0);
INSERT INTO t1
VALUES (255, 1);
GO

PRINT ‘*** SET ARITHABORT ON; SET ANSI_WARNINGS ON’
GO
— SET ARITHABORT ON and  AND SET ANSI_WARN ON AND testing.
SET ARITHABORT ON
SET ANSI_WARNINGS ON
GO
PRINT ‘*** Testing divide by zero during SELECT’;
GO

SELECT a / b AS ab
FROM t1;
GO

PRINT ‘*** Testing divide by zero during INSERT’;
GO
INSERT INTO t2
SELECT a / b AS ab 
FROM t1;
GO

PRINT ‘*** Testing tinyint overflow’;
GO
INSERT INTO t2
SELECT a + b AS ab
FROM t1;
GO

PRINT ‘*** Resulting data – should be no data’;
GO
SELECT *
FROM t2;
GO

— Truncate table t2.
TRUNCATE TABLE t2;
GO

— SET ARITHABORT OFF AND SET ANSI_WARNINGS OFF and testing.
PRINT ‘*** SET ARITHABORT OFF;SET ANSI_WARNINGS OFF;’
GO
SET ARITHABORT OFF;
SET ANSI_WARNINGS OFF;
GO

— This works properly.
PRINT ‘*** Testing divide by zero during SELECT’;
GO
SELECT a / b AS ab 
FROM t1;
GO

— This works as if SET ARITHABORT was ON.
PRINT ‘*** Testing divide by zero during INSERT’;
GO
INSERT INTO t2
SELECT a / b AS ab 
FROM t1;
GO
PRINT ‘*** Testing tinyint overflow’;
GO
INSERT INTO t2
SELECT a + b AS ab
FROM t1;
GO

PRINT ‘*** Resulting data’;
GO
SELECT *
FROM t2;
GO

— Drop tables t1 and t2.
DROP TABLE t1;
DROP TABLE t2;
GO

 
Leave a comment

Posted by on October 8, 2011 in Errors

 

Property ErrorLogFile is not available for JobServer

 

 

 

 

 

 

 

 

This Error Show to me when i want to see the Properties of SQL Server Agent to my instance

 troubleshoot this Error

  1. go to Start >>>> Run >>>>> Write (Regedit)
  2. HKEY_LOCAL_MACHINE >>>>SOFTWARE>>>>MICROSOFT>>>>MICROSOFT SQL SERVER >>>> (YOUR INSTANCE NAME)>>>>SQLSERVERAGENT >>>>double click on SQLSERVERAGENT
  3. search in the right list on file name (ErrorLogFile) if you found it right click >>>>> Modfiy >>>>> Check the path in ValueData is true this path
  4. if you not found this file (ErrorLogFile) 
  5. right click on SQLSERVERAGENT >>>>>New >>>> String Value
  6. Rename the file will Create to (ErrorLogFile)
  7. thengo to C: Drive >>>> Programme File >>>>Microsoft SQL Server >>>> (Your Instance Name) >>>>MSSQL >>>>Log >>>>SQLAGENT.OUT
  8. then take this path and go to the file (ErrorLogFile) in the reigistration
  9. then right click >>>> Modify >>>> put the path in Value Data . then ok
  10. try now to open your SQL AGENT Properties .

 

 

 
1 Comment

Posted by on October 5, 2011 in Errors

 

SQL Server 2008R2 Error After intall Then Make Update To Your PC

SQL Server 2008R2 Error After intall Then Make Update To Your PC

 

Introduction

 
Yesterday I install SQL  server 2008R2 in my Laptop  Then After install I make update to my pc in this update I download update for SQL Server 2008R2
This Update  Name  is

(Security Update for SQL Server 2008 R2 (KB2494088))
Then after I intall the update I make restart to my laptop then I try to connect to my instance SQL server 2008R2 I receive this Error massage

Login failed for user ‘Elmasry-PC\Elmasry’. Reason: Server is in script upgrade mode (Only administrator can connect at this time. (Microsoft SQL Server, Error: 18401)) .


 When I see this error I can’t believe my easy  Because before 5 minutes before the update my instance was worked very good so I make search on the internet  and I can fix this error

 
The Solution

 
1. Enable trace flag 902 on the instance of SQL Server 2008 R2. To do this, follow these steps:
a. Open SQL Server Configuration Manager.
b. In SQL Server Configuration Manager, click SQL Server Services.
c. Double-click the SQL Server service.
d. In the SQL Server Properties dialog box, click the Advanced tab.
e. On click the Advanced tab, locate the Startup Parameters item.
f. Add(( ;-T902 )) to the end of the existing string value, and then click OK.
2. Right-click the SQL Server service, and then click Start.
3. If the SQL Server Agent service is running, right-click the SQL Server Agent service, and then click Stop.
4. Open SQL Server Management Studio, and then connect to the instance of SQL Server 2008 R2.
5. Run the following statements:

EXEC sp_configure ‘show advanced’, 1;
RECONFIGURE;
EXEC sp_configure ‘allow updates’, 0;
RECONFIGURE;
EXEC sp_configure ‘Agent XPs’, 1;
RECONFIGURE;
GO

Note  :

In steps No 5 I receive another Error massage this Massage is ((ad hoc update to system catalogs is not supported)) So if you Receive this error try to make this step (Fix Ad Hoc) If you don’t receive this error go to step no 6

 
Fix Ad Hoc
Run This Script then Try to make the step No 5 Again
sp_configure ‘show advanced options’,1
reconfigure
go
sp_configure ‘xp_cmdshell’,1
reconfigure
go
sp_configure ‘show advanced options’,1
reconfigure with override
go
sp_configure ‘xp_cmdshell’,1
reconfigure with override
go

—Reset the “allow updates” setting to the recommended 0
sp_configure ‘allow updates’,0
reconfigure with override
go
—Reset the environment back as the test is complete
sp_configure ‘xp_cmdshell’,0
reconfigure
go
sp_configure ‘show advanced options’,0
reconfigure
go

6. In SQL Server Configuration Manager, right-click the SQL Server service, and then click Stop.
7. Remove trace flag 902 on the instance of SQL Server 2008 R2. To do this, delete((  ;-T902 )) from the string value that you updated in step 1f.
8. Right-click the SQL Server service, and then click Start.
9. Right-click the SQL Server Agent service, and then click Start.
10. In SQL Server Management Studio, reconnect to the instance of SQL Server 2008 R2.
11. In Object Explorer, expand Management, right-click Data Collection, and then click Enable Data Collection.
Note :

 You may receive the following error message when you connect to the instance of SQL Server 2008 R2 in SQL Server Management Studio:
Error: 18401
Login failed for user ‘<login name>’. Reason: Server is in script upgrade mode. Only administrator can connect at this time.


This error message indicates that SQL Server 2008 R2 is completing the installation of cumulative update package 1. If you receive this error message, wait several minutes and then connect to the instance of SQL Server 2008 R2 again.
 Best Regards

Mostafa Elmasry

 
Leave a comment

Posted by on September 25, 2011 in Errors