asked on

Uses OLE in a sp run by SQL Server Agent.

Hello,

I want to run a stored procedure using SQL Server Agent.

This stored procedure uses OLE and calls sp_AOCreate.

When testing I used the following:

sp_configure ‘show advanced options’, 1;
 GO
 RECONFIGURE;
 GO
 sp_configure ‘Ole Automation Procedures’, 1;
 GO
 RECONFIGURE;
 GO

Open in new window

to enable OLE.

How should i run this job under SQL Server Agent?

Does running the above script only work for the current session, or does it apply to all uses in the database?

Vitor Montalvão

It applies to instance so if you already ran those commands to enable the OLE automation now create the job to run the sp_AOCreate as you would for any stored procedure:

DECLARE @object int;
EXEC sp_OACreate 'YourOLEObjHere', @object OUT;

Open in new window

soozh

ASKER

Hello,

I may not have been clear. I already have a working SP, that i have tested a few times. So i know that the code is ok.

What i need to know is how enable the OLE option for SQL Server Agent and this job in particular.

/r

ASKER CERTIFIED SOLUTION

Vitor Montalvão

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

soozh

ASKER

ok... thanks for that.. but will the job have to be run as an administrator?

Vitor Montalvão

Yes, you need to provide a user with sysadmin role so it can enable the OLE automation procedure.