March 1, 2012 at 9:29 am
Hails,
I'm having a problem with the procedure 'master.dbo.xp_delete_file' and I'm receiving the following message when I try to execute a stored procedure from a different sql instance. I get the following error:
Msg 911, Level 16, State 1, Procedure BackupDatabase1, Line 31
Could not locate entry in sysdatabases for database 'sys'. No entry found with that name. Make sure that the name is entered correctly.
I have to mention that I'm executing the stored procedure from an SQL Server 2008 R2 instance to a SQL Server 2000 of which the procedure is located at. I have a linked server called 'MASTER' with the rpc settings on to be able to do the call. The stored procedure in question is:
USE [master]
GO
/****** Object: StoredProcedure [dbo].[BackupDatabase] Script Date: 02/28/2012 15:39:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:<Author,,Name>
-- Create date: <Create Date,,>
-- Description:<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[BackupDatabase1]
@dbname nvarchar(50),
@backupfolder nvarchar(255),
@backupfilename nvarchar(255),
@backupheader nvarchar(50),
@retaindays int
AS
BEGIN
DECLARE @olddate varchar(19)
DECLARE @deletefiles varchar(512)
DECLARE @backuppath varchar(512)
if(@dbname LIKE 'Syncrun%')
begin
SET @backuppath = @backupfolder + '\' + @backupfilename
BACKUP DATABASE @dbname
TO DISK = @backuppath
WITH
NAME = @backupheader,
RETAINDAYS = @retaindays;
-- Clean backup files that haven't been modified for 30 days.
set @olddate = CONVERT(VARCHAR, DateAdd(DD, -30, GetDate()))
set @deletefiles = 'master.sys.xp_delete_file 0,N''' + @backupfolder + ''',N''' + '.bak' + ''',N''' + @olddate + ''',1'
exec @deletefiles
end
ELSE
begin
RAISERROR ('Can''t backup a none compatible Syncrun database.',10,1)
end
END
And the call made to the stored procedure is:
exec dbo.BackupDatabase1 'Syncrun_NZAS', '\\OSIRIS\Temp\Kevin\SQLServer Backup', 'Syncrun_NZAS - Week of 2012-02-28.bak', 'Test', 10
March 1, 2012 at 9:39 am
I haven't got a 2000 instance to hand, but shouldn't it be master.dbo.xp_delete_file?
March 1, 2012 at 9:41 am
Forgot to mention that I've tried both. I get the same error message.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply