July 2, 2005 at 2:21 am
Hi,
I had created a script to delete 2 days old backup but this script run under master database .but when i create the procedure and uses like this
exec master..procedurename it gives error
Server: Msg 10019, Level 16, State 1, Line 0
sp_OACreate has not yet been called successfully for this command batch.
Server: Msg 10019, Level 16, State 1, Line 0
sp_OACreate has not yet been called successfully for this command batch.
below is the script i am using in procedure to delete the backup file
if exists ( select name from sysobjects where name='sp_delbackup_db' and type='P')
drop procedure sp_delbackup_db
go
create procedure sp_delbackup_db
as
declare @tr varchar(10)
declare @ds varchar(10)
declare @sd varchar(200)
declare @hr int
declare @ole_FileSystem int
declare @dbpath varchar(200)
select @ds=max(backup_set_id) from msdb.dbo.backupfile
set @tr= @ds-2
set @dbpath='D:\Database Backup\pubs'+ @tr + '.bkp'
if @tr>@ds
use master
EXEC @hr = master..sp_OACreate 'Scripting.FileSystemObject',@ole_FileSystem OUT
EXEC @hr = master..sp_OAMethod @ole_FileSystem, 'DeleteFile',
NULL,@dbpath
EXEC @hr = master..sp_OADestroy @ole_FileSystem
set @sd='pubs' +@ds+'.bkp'
--- Print @sd +'........................Deleted'
exec master..sp_delbackup_db
The way i am unsing the procedure
declare @tr varchar(10)
declare @ds varchar(10)
declare @sd varchar(200)
declare @hr int
declare @ole_FileSystem int
declare @dbpath varchar(200)
select @ds=max(backup_set_id) from msdb.dbo.backupfile
set @tr= @ds-2
set @dbpath='D:\Database Backup\pubs'+ @tr + '.bkp'
if @tr>@ds
use master
EXEC @hr = master..sp_OACreate 'Scripting.FileSystemObject',@ole_FileSystem OUT
EXEC @hr = master..sp_OAMethod @ole_FileSystem, 'DeleteFile',
NULL,@dbpath
EXEC @hr = master..sp_OADestroy @ole_FileSystem
set @sd='pubs' +@ds+'.bkp'
Print @sd +'........................Deleted'
this run fine outside the procedure.
can anybody help me how to remove this error or suggest me some better way.
from
sufian
July 4, 2005 at 1:02 pm
Why r u creating a procedure for this.When you can do the same from the enterprise manager.When you have scheduled the backup then there only you can specify the criteria that files older than so many days should be deleted.So go for that instead of creating a procedure.
July 5, 2005 at 8:52 am
Hi Mr.Arun,
thanx mr.arun but i like to tell u that try to do something diffrent from others. will DMP will send u the email regarding the deleted backup no.So i delete it from my script and when the delete is complete it will send me a mail about the status.
anyways i had solved it.
thanx
sufian
January 30, 2006 at 12:52 pm
Hello, I was wondering what you did to resolve your issue I am having the same problem an I have not been able to find a solution. Your assistance would be appreciated. Thanks.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply