sp_CreateOLE: sp_OACreate failed SQL 2005

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

sp_CreateOLE: sp_OACreate failed SQL 2005

Author

Topic

jcornell
Starting Member

11 Posts

Posted - 2010-07-13 : 03:53:31

Hi, I have been trying to get an extract to work for Images@Work for two days now. Northgate want me to install applications onto the SQL server which is not best practice and we have never has client software installed onto any of service servers.

Below is the error code (red) and the stored procedure (green)

p.s. HBBC\SVC_SQL01 is our SQL service account which is a local administrator

Date 12/07/2010 22:00:00
Log Job History (Download - Revs & Bens & Planning)

Step ID 2
Server HDC-PR-SQL01
Job Name Download - Revs & Bens & Planning
Step Name CT_Props
Duration 00:00:00
Sql Severity 16
Sql Message ID 50000
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0

Message
Executed as user: HBBC\SVC_SQL01. sp_CreateOLE: sp_OACreate failed [SQLSTATE 42000] (Error 50000). The step failed.

5
Data Import/Download
Revs and Bens Downloads
UID=******;PWD=*******;server=HDC-PR-SQL01;DATABASE=images;driver={sql server}
3
13/07/2010 08:27:33
NULL

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-07-13 : 04:23:22

I wonder if anyone knows what we should do with the given information.

No, you're never too old to Yak'n'Roll if you're too young to die.

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2010-07-13 : 04:33:11

Is it possible to post the part of the stored procedure that is attempting to do the sp_OACreate?

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

jcornell
Starting Member

11 Posts

Posted - 2010-07-13 : 05:10:59

Hi, sorry about that I was in a rush and forgot the SP code

USE [Images]
GO
/****** Object: StoredProcedure [dbo].[sp_RunScheduledJob] Script Date: 07/13/2010 10:10:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

/****** Object: dbo.sp_RunScheduledJob ******/
ALTER PROCEDURE [dbo].[sp_RunScheduledJob]
@JobNumber int
AS
SET NOCOUNT ON

-- CMC: 29/11/2000 Change '= null' to 'is Null' (CRF 279)
-- DWB 24Oct2000 new Scheduler thing - based on Mr Conboys object creation stuff
Declare @str Varchar(255)
DECLARE @object int
DECLARE @hr int
declare @JobType varchar(25),
@StringParam varchar(255),
@LongParam int,
@MethodName varchar(20)
select @jobtype = jobtype, @stringparam = stringparam, @longparam = longparam
from dbo.ScheduledJobs
where JobID = @jobNumber
if isnull(@jobtype,'') = ''
begin
-- There is no record so we cannot run it!
return
end
-- Choose the correct method for the job
if @jobtype = 'COLD Import'
begin
select @MethodName = 'COLDImport'
end
else if @jobtype = 'COLD Import and Index'
begin
select @MethodName = 'COLDImportAndIndex'
end
else if @jobtype = 'COLD Index'
begin
select @MethodName = 'COLDIndex'
end
else if @jobtype = 'Create Test File'
begin
select @MethodName = 'CreateTestFile'
end
else if @jobtype = 'Data Import/Download'
begin
select @MethodName = 'DataDownload'
end
-- Create an object
EXEC @hr = sp_OACreate 'SQLScheduleObject.ScheduleJob', @object OUT
IF @hr <> 0
BEGIN
-- print 'Error in Create'
-- Update the date and status so we know what happened
UPDATE dbo.ScheduledJobs SET LastRun = getdate(), Status = 'Error Creating Object' where JobID = @jobnumber
RAISERROR('sp_CreateOLE: sp_OACreate failed',16,-1)
RETURN
END
-- Call a method that returns a value
EXEC
@hr = sp_OAMethod @object, @MethodName ,@Str out , @longparam ,@stringparam
IF @hr <> 0
BEGIN
-- Print 'Error In Method'
-- Update the date and status so we know what happened
UPDATE dbo.ScheduledJobs SET LastRun = getdate(), Status = 'Error Executing Method (' + @MethodName + ')' where JobID = @jobnumber
RAISERROR('sp_CreateOLE: sp_OAMethod failed',16,-1)
RETURN
END

-- Destroy the object
EXEC @hr = sp_OADestroy @object
IF @hr <> 0
BEGIN
-- Update the date and status so we know what happened
UPDATE dbo.ScheduledJobs SET LastRun = getdate(), Status = 'Error Destorying Object' where JobID = @jobnumber
RAISERROR('sp_CreateOLE: sp_OADestroy failed',16,-1)
RETURN
END
-- Update the date and status so we know what happened
UPDATE dbo.ScheduledJobs SET LastRun = getdate(), Status = @str where JobID = @jobnumber

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources