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.
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.