Change The Server Collation after installation


After you finish installation for SQL server may be you want to change the Server collation or database Collation so leat’s see what will we do if we want to make this :

1- if you want to know what’s your database collation know

SELECT name, collation_name

FROM sys.databases WHERE name = ‘test’   — put your database name here

2- If you want to get a list of all available collations on the server using

SELECT * FROM ::fn_helpcollations()

3- Alter Database Collation

ALTER DATABASE test           — put your database name here

COLLATE Arabic_CI_AS        — replace with whatever collation you need

4- IF you want to know what’s your server collation

SELECT SERVERPROPERTY (‘Collation’)

Enable xp_cmdshell using sp_configure


—- To allow advanced options to be changed.

EXEC sp_configure ‘show advanced options’, 1

GO

—- To update the currently configured value for advanced options.

RECONFIGURE

GO

—- To enable the feature.

EXEC sp_configure ‘xp_cmdshell’, 1

GO

—- To update the currently configured value for this feature.

RECONFIGURE

GO

Configure SQL Server Agent XPs


To Enable Agent XPs

sp_configure ‘show advanced options’, 1;

GO

RECONFIGURE;

GO

sp_configure ‘Agent XPs’, 1;

GO

RECONFIGURE

GO

To disable Agent XPs

sp_configure ‘show advanced options’, 0;

GO

RECONFIGURE;

GO

sp_configure ‘Agent XPs’, 0;

GO

RECONFIGURE

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