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

using the SET ARITHABORT AND SET ANSI_WARNINGS


Terminates a query when an overflow or divide-by-zero error occurs during query execution.

SET ARITHABORT  [ON/OFF]

SET ANSI_WARN [ON/OFF]

i will explain very simple example to know what is the benefits from make this option is ON or OFF

— 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

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 .

 

 

Trigger to Prevent any user to Create Table on Database


Event Can prevent happend on your database
CREATE_TABLE
CREATE_INDEX
CREATE_FUNCTION

Example

USE
DBNAME;
GO
CREATE TRIGGER Prevent_Create

ON
DATABASE FOR CREATE_TABLE
AS
PRINT ‘CREATE TABLE Issued.’
SELECT EVENTDATA().value
(‘(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]’,’nvarchar(max)’)
RAISERROR (‘New tables cannot be created in this database.’, 16, 1)
ROLLBACK;
GO
–Test the trigger.
CREATE TABLE T2 (C1 int);
GO
–Drop the trigger.
DROP TRIGGER Prevent_Create_Table ON DATABASE;
GO

troubleshoot The Connectivity Issues


1. Check your SQL Server Error log to make sure that it enables TCP/IP protocol and establish the listening on a specific TCP port.Normally, if SQL Server is configured to enable TCP/IP protocol, when it starts it will log a message similar to the following error log: 2010-01-04 12:30:40.47 Server Server is listening on [ ‘any’ <ipv4> 1433].

This means that the SQL Server instance is using TCP/IP protocol and listening on the TCP port 1433.

2. Enable TCP/IP protocol if you could not find the message in the Error Log.

You can enable protocols via SQL Server Configuration Manager. Under SQL Server Network Configuration->Protocols for <instance name>, enable TCP/IP protocol.

3. Check Network issue after TCP/IP enabled.

To check if your network is healthy, you can use ping and telnet orders to check.

· Run “ping -t” for 1-2 minutes to check if there any timeout response

ping -t <the remote server IP address>

If there are timeout responses, contact your network administrator to check the network issue.

· Run “telnet” command directly to the TCP port on the server to check if you can access the TCP port.

Telnet <servername> <listening tcp port>

4. Check firewall if you cannot telnet to the specific TCP port.

Ensure that the TCP port on which SQL Server is listening has been put into the exceptions of your firewall. If you connect to a named instance, it is also recommended that you put the UDP port 1434 for SQL Server Browser service into your firewall exception.

The connection issues may be due to other causes like client drivers, application configuration, authentication and ect.

Reference

http://windows.microsoft.com/en-us/windows-vista/Telnet-frequently-asked-questions 

http://blogs.msdn.com/sql_protocols/archive/2008/04/30/steps-to-troubleshoot-connectivity-issues.aspx

 

How do i rename my SQL Server instance?


 

 

SQL Server doesn’t support renaming the instance name directly. To work around the issue, you could use one of the following workarounds:

a) Uninstall the existed instance and install it with the new name.

 

b) Change the computer name and rename the part of the instance name that corresponds to the computer name.

For example, you can change a computer named MB1 that hosts an instance of SQL Server named Instance1 to another name, such as MB2. However, the instance part of the name, Instance1, will remain unchanged. In this example, the \\ComputerName\InstanceName would be changed from \\MB1\Instance1 to \\MB2\Instance1.

For a renamed default instance, run the following procedures:
sp_dropserver <old_name>;
GO
sp_addserver <new_name>, local;
GO For a renamed named instance, run the following procedures:
sp_dropserver <old_name\instancename>
GO
sp_addserver <new_name\instancename>, local

 GO

 

References

 

How to: Rename a Computer that Hosts a Stand-Alone Instance of SQL Server

http://technet.microsoft.com/en-us/library/ms143799.aspx