HEELPBOOK - OLE Automation Procedures (SQL Server 2005) #######################

The system-stored procedures in SQL Server are very useful. You can find a system-stored procedure to do almost any task. However, in order to use these stored procedures, you need to enable OLE Automation Procedures.

In SQL Server 2005, the option is disabled by default. You might recieve an error similar to the following:

SQL Server blocked access to procedure 'sys.sp_OACreate' of component Ole Automation Procedures
If you are trying to use the OLE Automation Procedures and the option is turned off you will need to enable advanced options before enabling OLE Automation Procedures.

Use the following code to show the current status of advance option and OLE Automation procedure status.


-- Show Advance Options, 0 = disabled, 1 = enabled
EXEC sp_configure 'show advanced options';
GO

-- Show Ole Automation Procedures, 0 = disabled, 1 = enabled
EXEC sp_configure 'Ole Automation Procedures';
GO


Use the following code to enable the advanced option and OLE Automation Procedures.


-- Enable the Advanced options if they are not already turned on
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO

-- Enable the Ole Automation Procedures
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO


Use the following code to disable the advanced option and OLE Automation Procedures.


-- Disable the Advanced options
sp_configure 'show advanced options', 0;
GO
RECONFIGURE;
GO


-- Disable the Ole Automation Procedures
sp_configure 'Ole Automation Procedures', 0;
GO
RECONFIGURE;
GO



############ ARTICLE INFO #############

Article Month: October
Article Date: 30/10/2012
Permalink: http://heelpbook.altervista.org/2012/ole-automation-procedures-sql-server-2005/
Source: http://voices.yahoo.com/ole-automation-procedures-sql-server-2005-enabling-2856727.html?cat=15
Language: English

View more articles on: http://www.heelpbook.net/
Follow us on Facebook: http://it-it.facebook.com/pages/HeelpBook/100790870008832
Follow us on Twitter: https://twitter.com/#!/HeelpBook
Follow us on RSS Feed: http://feeds.feedburner.com/Heelpbook
Follow us on Delicious: http://delicious.com/heelpbook
Linkedin: http://it.linkedin.com/pub/stefano-maggi/27/73a/b20
Google+ : https://plus.google.com/116990277568167008289/posts