[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

¿What's wrong with the following Stored Procedure?

Posted on 2011-09-24
2
Medium Priority
?
375 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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …

834 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