Solved

¿What's wrong with the following Stored Procedure?

Posted on 2011-09-24
2
343 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 5

Accepted Solution

by:
AlokJain0412 earned 500 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

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

726 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