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 SQL Server 2012 Download and T-SQL Tutorials
Development resources, articles, tutorials, samples, codes and tools for .Net, SQL Server, Windows, Windows Phone, SAP and ABAP, like SAP UI5, Screen Personas, etc.




download SQL Server 2016
download SQL Server 2014
download SQL Server 2012



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
go
reconfigure
go
exec sp_configure 'Ole Automation Procedures', 1 -- Enable
-- exec sp_configure 'Ole Automation Procedures', 0 -- Disable
go
reconfigure
go
exec sp_configure 'show advanced options', 0
go
reconfigure
go

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.







Related SQL Resources

SQL Server Articles

SQL Server 2012

SQL Server Tools

SQL Blog

SQL Server 2008 Blog

Certification Exams Blog

Reporting Services Blog

Analysis Services Blog

MS SQL Server Forums







Copyright © 2004 - 2017 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems