• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 387
  • Last Modified:

¿What's wrong with the following Stored Procedure?

When I execute the following SP I got this error:

Msg 22049, Level 15, State 0, Line 0
Error executing extended stored procedure: Invalid Parameter
Processed 1816968 pages for database 'menafn', file 'menafn_Data' on file 1.
Processed 1574 pages for database 'menafn', file 'menafn_Log' on file 1.
BACKUP DATABASE successfully processed 1818542 pages in 661.304 seconds (21.483 MB/sec).

Here is the SP:

DECLARE @backupDate nvarchar(8)
declare @deletedate datetime
declare @statement nvarchar(200)
set @backupDate =cast(day(getdate())as nvarchar)
				+cast (month(getdate())as nvarchar)
				+cast (year(getdate())as nvarchar)
				
 select @deletedate= DATEADD(dd,-7,GETDATE())				
execute xp_delete_file 0, 'Menafn_Sql_backup_weekly', 'bak', @deletedate

set @statement ='BACKUP DATABASE menafn 
		TO DISK = ''C:\Menafn_Sql_backup_weekly\menafn_weekly_'
		+@backupdate+
		'.bak'''
 
execute (@statement)

Open in new window


What Im doing wrong?
0
Sindibad_123
Asked:
Sindibad_123
1 Solution
 
AlokJain0412Commented:
Hi  Sindibad_123,

Use your statment like that  and
Use it wirh  sa authority

execute master.dbo.xp_delete_file 0,N 'Menafn_Sql_backup_weekly', N'bak', @deletedate
0
 
TempDBACommented:
My first question would be why to delete the files. When you will do backup, it will overwrite the existing file. So, why an extra step?

The question to your answer is run the command with the full path. To call the procedure you need to provide the dabatabase name and to delete the file , the location path should be completed

execute master.dbo.xp_delete_file 0,N 'C:\Menafn_Sql_backup_weekly', N'bak', @deletedate
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now