Sql server needs to be enable the configuration in order to execute some of the statement. Some of the them can be activated through the sql server configuration surface area.
The list of configurations in the sql server can be queried using the following select statement.
SELECT * FROM SYS.CONFIGURATIONS
|
The execution of the above query will list out all the configurations in the sql server.
The configuration has to be made on the value column. Basically it is bit type column where it accepts 1 and 0.
The 1(true) indicates the configuration enabled and 0(false) indicates the configuration has disabled.
The SP_CONFIGURE is system stored procedure in sql server which is used to do configuration enabling and disabling.
The SP_CONFIGURE stored procedure accepts two parameters. First argument should be name of the configuration and other one is bit field which accepts 1 or 0.
Lets see some example configuration enable/disable.
SP_CONFIGURE 'Database Mail XPs',1
SP_CONFIGURE 'xp_cmdshell',0
SP_CONFIGURE 'clr enabled',1