March 25, 2008 at 12:46 pm
hi guys can someone give the detailed steps of how to Enable OLE Automation on each SQL Server 2005 instance please :blush:
March 26, 2008 at 7:04 am
is this correct?
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE
GO
March 26, 2008 at 8:17 am
You only need to set the advanced options once.
Change your reconfigure to RECONFIGURE WITH OVERRIDE;
Your friendly High-Tech Janitor... 🙂
March 26, 2008 at 8:22 am
thanks so like this:
sp_configure 'show advanced options', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE WITH OVERRIDE
GO
after i ran it i get this :
Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.
Configuration option 'Ole Automation Procedures' changed from 0 to 1. Run the RECONFIGURE statement to install.
Configuration option 'xp_cmdshell' changed from 0 to 1. Run the RECONFIGURE statement to install.
have you gotten any complications by enable the ole automation?
March 26, 2008 at 8:32 am
You're fine with that output. You want to rerun sp_configure and check the run_value column to ensure it's set to 1 for the options you just turned on. Some are dynamic and will take affect immediately (hence the OVERRIDE), some require a restart....
I still enable OLE on my 2005 servers as I have management code that utilizes sp_OA*.
The only issue's I've run into are that 2005 is pickier about disconnecting from the server when using the SQLServer object. Just make sure that your sp_OA based routines call the Disconnect method if using the SQLServer object. In the DMO/SMO Programming forum I posted a snippet of the Disconnect method in one of the current posts... here
Your friendly High-Tech Janitor... 🙂
March 26, 2008 at 8:54 am
thank you!, i just needed for to this http://qa.sqlservercentral.com/articles/Integration+Services/61774/
I am not sure what you mean with disconnecting from sql objects eventhough i am doing it first in my development server to test, i would like to use the ssis package in the article to get also information from the production server but i see when i run the sp_configure that the advance options are set to 1, however i don't see the ole automation in production, that is something i may have to change in production, but before i run it through the Senior DBA's i would like to know if changing the OLE Automation would create any security issues, etc..
March 26, 2008 at 9:05 am
Yes, there are security issues to consider with allowing use of the OA procedures. If your users have means to execute code at an administrator (sa) level, then they can execute the OA procedures and construct thier own objects (for nefarious reasons, or not) and do whatever they wish...keep that in mind.
FWIW, the code on the link you referenced, doesn't need the OA procedures to be enabled....
Your friendly High-Tech Janitor... 🙂
March 26, 2008 at 9:55 am
humm... ok let me see if i can follow the article without doing it then. thank you for all your help!!! i really apreciate it.
March 26, 2008 at 10:12 am
Apologies, I missed the enable in step 9...I'd just skimmed the article. None of the code in the article references the OA objects...maybe one of the other articles objects requires it...
Your friendly High-Tech Janitor... 🙂
March 26, 2008 at 3:17 pm
thank you again
😀
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply