?
Solved

¿What's wrong with the following Stored Procedure?

Posted on 2011-09-24
2
Medium Priority
?
382 Views
Last Modified: 2012-05-12
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
Comment
Question by:Sindibad_123
2 Comments
 
LVL 5

Accepted Solution

by:
AlokJain0412 earned 2000 total points
ID: 36594779
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
 
LVL 25

Expert Comment

by:TempDBA
ID: 36594895
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

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

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

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

601 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question