Unanswered: Job calling Store Proc Loop exits after first iteration
SQLSERVER 2000 Sp3, Windows 2000 AS
I have written a stored procedure which uses a while loop to run though a backup file and generates the Restore commands and brings a DR database up to date. The transaction file is appended to during the day on the Production system. The TRN file is copied to DR and the proc reads through applying the files it requires. The TRN file is appended to, so if an earlier run already applied the file, it is skipped having displayed an error about the log being too early - this is fine as it whips though a get to the files that are relevant and applies those. I run this procedure in QA without a problem. I have set up a Job to automate this process, but am finding that the Stored Procedure exits after trying to apply the first file which is not require (as it's too early). This situation seems to cause the job to exit out, whereas in QA the error is igorned and the loop continues?
ALTER PROCEDURE APPLY_TRN @db_name char(10), @trn_file char(500), @undo_file char(500), @cmd char(500) OUTPUT
DECLARE @cnt numeric
SET @cnt = 1
--Apply up to 5 days worth of TRN's
WHILE (@cnt < 240)
SET @cmd = 'RESTORE LOG ' + rtrim(@db_name) + ' FROM DISK = ''' + rtrim(@TRN_FILE) +
'''WITH FILE = ' + convert(char(4), @cnt) +
', STANDBY = ''' + rtrim(@UNDO_FILE) + ''''
APPLYING PRT4PRD [SQLSTATE 01000]
Msg 4326, Sev 16: The log in this backup set terminates at LSN 7684000000033300001, which is too early to apply to the database. A more recent log backup that includes LSN 7702000000017200001 can be restored. [SQLSTATE 42000]
Msg 3013, Sev 16: RESTORE LOG is terminating abnormally. [SQLSTATE 42000]
Instead of ignoring this, it aborts. Can I bypass this behaviour?