SQL SERVER – FIX : Error: Msg 15123, Level 16 – The configuration option ‘advance option’ does not exist, or it may be an advanced option.

I received another email describing error received due to my executing script from my previous article .

Error :
Msg 15123, Level 16, State 1, Procedure sp_configure, Line 51
The configuration option ‘optimize for ad hoc workloads’ does not exist, or it may be an advanced option.

Let us quickly see the reproduction of this error in following image.

Fix/Workaround/Solution:
The reason this error is happening because of not enabling advance option. Run complete following script and it should fix the problem.

sp_CONFIGURE 'show advanced options',1
RECONFIGURE
GO

sp_CONFIGURE ‘optimize for ad hoc workloads’,1
RECONFIGURE
GO

Reference : Pinal Dave (http://blog.sqlauthority.com)

About these ads

SQL SERVER – Change Default Fill Factor For Index

SQL Server has default value for fill factor is Zero (0). The fill factor is implemented only when the index is created; it is not maintained after the index is created as data is added, deleted, or updated in the table. When creating an index, you can specify a fill factor to leave extra gaps and reserve a percentage of free space on each leaf level page of the index to accommodate future expansion in the storage of the table’s data and reduce the potential for page splits.

I like my fill factor to 90 (Why? I like it!) I use sp_configure to change the default fill factor for the SQL Server.
sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
sp_configure 'fill factor', 90
GO
RECONFIGURE
GO

Reference : Pinal Dave (http://blog.SQLAuthority.com) , BOL

SQL SERVER – Fix : Error : Msg 6263, Level 16, State 1, Line 2 Enabling SQL Server 2005 for CLR Support

Error:

Fix : Error : Msg 6263, Level 16, State 1, Line 2 Enabling SQL Server 2005 for CLR Support

Fix/Workaround/Solution:

1) Enable Server for CLR Support.

2) Run following query in Query Analyzer

sp_CONFIGURE 'clr_enabled',1
GO
RECONFIGURE
GO

If CLR procedure is used without enabling CLR, it will show error message as .NET Framework is not enabled.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – 2005 Enable CLR using T-SQL script

Before doing any .Net coding in SQL Server you must enable the CLR. In SQL Server 2005, the CLR is OFF by default.
This is done in an effort to limit security vulnerabilities. Following is the script which will enable CLR.

EXEC sp_CONFIGURE 'show advanced options' , '1';
GO
RECONFIGURE;
GO
EXEC sp_CONFIGURE 'clr enabled' , '1'
GO
RECONFIGURE;
GO

Reference : Pinal Dave (http://blog.SQLAuthority.com) , BOL

SQL SERVER – Enable xp_cmdshell using sp_configure

The xp_cmdshell option is a server configuration option that enables system administrators to control whether the xp_cmdshell extended stored procedure can be executed on a system.
---- 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

Reference : Pinal Dave (http://blog.SQLAuthority.com)