SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software
Development resources, articles, tutorials, code samples, tools and downloads for ASP.Net, SQL Server, Reporting Services, T-SQL, Windows, AWS, SAP HANA and ABAP

Enable Ole Automation Procedures in SQL Server 2012

SQL Server database administrator must enable Ole Automation Procedures using sp_configure for SQL developers to use sp_OACreate or sp_OAMethod like Ole Automation Procedures in their SQL scripts. By default SQL Server Ole Automation Procedures is disabled at installation for security reasons. It is easy for a database server administrator to enable Ole Automation Procedures using sp_configure procedure

Msg 15281, Level 16, State 1, Line 1
SQL Server blocked access to procedure 'sys.sp_OACreate' of component 'Ole Automation Procedures' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ole Automation Procedures' by using sp_configure. For more information about enabling 'Ole Automation Procedures', search for 'Ole Automation Procedures' in SQL Server Books Online.

Here is a list of SQL Ole Automation Procedures that programmers can use in their SQL codes:
sp_OACreate, sp_OAMethod, sp_OADestroy, sp_OAGetErrorInfo, etc.

Of course before using these procedures in your scripts this option should be activated. Otherwise, an error shown in below screenshot will be thrown by SQL Server database engine.

enable Ole Automation procedures in SQL Server

To use sp_configure Ole Automation procedures can be enabled after 'show advanced options' SQL Server configuration option is set to 1. Otherwise, if a DBA executes sp_configure command with "Ole Automation procedures" option, it will return following error.

Msg 15123, Level 16, State 1, Procedure sp_configure, Line 62
The configuration option 'Ole Automation Procedures' does not exist, or it may be an advanced option.

To prevent SQL Server to throw an exception, it is better to check first if advanced options is enabled at SQL Server configuration by calling sp_configure system procedure.

If you see "show advanced options" run value as 0, then first you have to enable it. And then call reconfigure to activate this change on the SQL Server database server.

After advanced configuration options is enabled, developers or administrators are able to configure and enable Ole Automation Procedures on SQL Server instance

Here is the all steps to enable Ole Automation Procedures on SQL Server 2012 instance

exec sp_configure 'show advanced options', 1
exec sp_configure 'Ole Automation Procedures', 1 -- Enable
-- exec sp_configure 'Ole Automation Procedures', 0 -- Disable
exec sp_configure 'show advanced options', 0

After all configuration is completed, it is good to set show advanced options back to 0

Now developers can create and run SQL scripts that use Ole Automation procedures like sp_OACreate and sp_OAMethod procedures.

SQL Server

SQL Server 2019 Installation
download SQL Server 2019
download SQL Server 2017
download SQL Server 2016
download SQL Server 2014
download SQL Server 2012
MacOS ve SQL Server 2019

Copyright © 2004 - 2021 Eralper YILMAZ. All rights reserved.