sql in stored procedure help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

sql in stored procedure help

Author

Topic

android.sm
Starting Member

36 Posts

Posted - 2010-04-27 : 09:25:21

I would like the following sql inside stored procedure but when I do so it does not work. Here is the code:

USE master

GO

Alter PROCEDURE sp_BackupDatabaseDelete

as

DECLARE @OD datetime

DECLARE @OD_Am varchar(10)

SELECT @OD = Getdate() - 7

SELECT @OD_Am = CONVERT(varchar(10), @OD, 1)

EXEC dbo.xp_delete_file 0,N'C:\Backup',N'bak',@OD_Am,1

GO

If I remove stored procedure it works fine. Any suggestions?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-04-27 : 09:39:17

Does not work isn't helpful for us. Any errors or what measn does not work?
If I remove stored procedure - what means that?

If you can be a bit more clear then maybe we can a bit more helpful.

No, you're never too old to Yak'n'Roll if you're too young to die.

android.sm
Starting Member

36 Posts

Posted - 2010-04-27 : 09:44:41

sorry, ok let's start again :)

the following code i found online deletes my backup that are more than 7 days old. i would like to use task scheduler to run my code let's say once each week. so first i would like to create a stored procedure and then use the sqlcmd in task scheduler to run my sp. here is the code that deletes when i run it:

USE master

GO

DECLARE @OD datetime

DECLARE @OD_Am varchar(10)

SELECT @OD = Getdate() - 7

SELECT @OD_Am = CONVERT(varchar(10), @OD, 1)

EXEC dbo.xp_delete_file 0,N'C:\Backup',N'bak',@OD_Am,1

GO

when i put this in stored procedure nothing happens, no error. and on execution no files delete from my backup folder. any ideas?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-04-27 : 09:51:06

How are you executing the procedure?

It should be

EXEC sp_BackupDatabaseDelete

Madhivanan

Failing to plan is Planning to fail

android.sm
Starting Member

36 Posts

Posted - 2010-04-27 : 10:01:02

hi...i executed wrongly. thanks, its working :)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-04-27 : 10:04:28