OLE Automation Procedures (SQL Server 2005)

Send Us a Sign! (Contact Us!)
--> (Word) --> (PDF) --> (Epub) --> (Text)
--> (XML) --> (OpenOffice) --> (XPS) --> (MHT)

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.

[tweet]

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 [gs 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 advance option and Ole Automation Procedures.

-- Enable the Advance 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 advance option and Ole Automation Procedures.

-- Disable the Advance options 
sp_configure 'show advanced options', 0; 
GO 
RECONFIGURE; 
GO
-- Disable the Ole Automation Procedures 
sp_configure 'Ole Automation Procedures', 0; 
GO 
RECONFIGURE; 
GO

SOURCE

LINK (yahoo.com)

LANGUAGE
ENGLISH