Problem with xp_delete_file

Question

Problem with xp_delete_file

torpkev

SSCarpal Tunnel

Points: 4819
More actions
November 13, 2009 at 7:12 am

#136126

Hi all,
Yesterday I made a script to delete some old .trn and .bak files, utilizing xp_delete_file.
Here's the section of the script where I call xp_delete_file:
(note: @maxfull is an INT = 1, @full_path is a varchar with my path)
/* Cleanup */
BEGIN TRY
DECLARE @dateFullBackup NVARCHAR(23)
SET @dateFullBackup = CONVERT(NVARCHAR(23),DATEADD(dd,@maxfull * -1,GETDATE()),126)
EXECUTE master.dbo.xp_delete_file 0, @full_path, N'bak', @dateFullBackup
EXECUTE master.dbo.xp_delete_file 0, @full_path, N'BAK', @dateFullBackup
END TRY
BEGIN CATCH
SET @errMsg = @errMsg + 'Error deleting old full backups. - ' + ERROR_MESSAGE()
END CATCH
Here's the error message I get:
Message
Executed as user: MYDOMAIN\MSSQLUser. xp_delete_file() returned error 2, 'The system cannot find the file specified.' [SQLSTATE 42000] (Error 22049). The step failed.
Now this fails the whole job, which I thought putting the try/catch around would avoid.. if I don't delete the file, no big deal, I still need it to continue through the job.
Any ideas why its just dying instead of jumping to the catch block and continuing?
Thanks
--------------------------
I long for a job where my databases dont have any pesky users accessing them 🙂

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

torpkev SSCarpal Tunnel Points: 4819 More actions · Answer 1

Well,

I know WHY it was failing.. i was sending a path that doesn't exist to the job.. but still pretty bummed out that the failure kills my whole job.

--------------------------

I long for a job where my databases dont have any pesky users accessing them 🙂

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 2

Change the job step to continue to the next step in case of failure.. succeed or fail the next step runs..

And that was the question I was going to ask, are you SURE that the path exists..

CEWII

torpkev SSCarpal Tunnel Points: 4819 More actions · Answer 3

Its not an individual step, its part of a larger script

CREATE PROCEDURE myProc AS

-- do stuff

xp_delete_file

-- do some more stuff

GO

then the job step is

EXEC myProc

--------------------------

I long for a job where my databases dont have any pesky users accessing them 🙂

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 4

Well that does present a problem.. And it won't respond to a try/catch block..

When you run it outside of agent, what severity does it give for the error, because as I remember there are some severities that try/catch won't do..

CEWII

torpkev SSCarpal Tunnel Points: 4819 More actions · Answer 5

Msg 22049, Level 16, State 1, Line 0

xp_delete_file() returned error 2, 'The system cannot find the file specified.'

is what I get if I run it outside of my script

--------------------------

I long for a job where my databases dont have any pesky users accessing them 🙂

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 6

Well BOL says:

A TRY…CATCH construct catches all execution errors that have a severity higher than 10 that do not close the database connection.

So it should work..

I just thought of this.. Does the script continue to run or does it die? If it continues to run the step indicates failure even though the rest of it ran?

CEWII

rohim.k2009 SSC Rookie Points: 41 More actions · Answer 7

It was suppose to be fixed in SP1 but you may also want to

try creating a cleanup task for each subdirectory.

People sometimes spend too much time troubleshooting

maintenance plans when they can just write scripts that have

more flexibility in less time then they spend addressing

maintenance plan issue.

iklan gratis[/url]

Kerja Keras Adalah Energi Kita [/url]