February 27, 2008 at 8:20 am
Just one work of caution. The sql that I listed to parse the error text takes as an assumption that the error text will always start with 'MSG '.
Dave Novak
February 27, 2008 at 8:31 am
Hi Dave!
I got following error from your modified script
Msg 102, Level 15, State 1, Line 6
Incorrect syntax near ')'.
It looks to me okay. But something is wrong there. Here i send you the modified script that I try to run:
create table #a ( aline varchar(max))
DECLARE @vcText varchar(200)
EXEC @vcText = master..xp_restore_verifyonly @filename='\\ctsqladp01\Backup$\InstA\msb.lsb'
INSERT INTO #a (aline)
VALUES (CASE WHEN @vcText = '0' THEN @vcText ELSE SUBSTRING(@vcText, 5, CHARINDEX(',', @vcText, 1) - 5))
if exists (select * from #a where aline=0)
BEGIN
SELECT 'Good DB'
return
END
else
BEGIN
SELECT ' Backup is bad'
return
END
drop table #a
February 27, 2008 at 8:36 am
You have to end the case statement.
(CASE WHEN @vcText = '0' THEN @vcText ELSE SUBSTRING(@vcText, 5, CHARINDEX(',', @vcText, 1) - 5) end)
February 27, 2008 at 8:37 am
I forgot the END statement on the CASE statement. Here is the correct SQL
INSERT INTO #a (aline)
VALUES (CASE WHEN @vcText = '0' THEN @vcText ELSE SUBSTRING(@vcText, 5, CHARINDEX(',', @vcText, 1) - 5) END)
Dave Novak
February 27, 2008 at 8:46 am
Hi Dave!
Now I am getting the following Error. It runs but showing some extra error messagw which I try to avoid.Any Idea?
Msg 536, Level 16, State 5, Line 5
Invalid length parameter passed to the SUBSTRING function
The statement has been terminated.
backup is bad
Msg 50204, Level 19, State 1, Line 0
LiteSpeed 2005 could not initialise the backup file. The previous system message is the reason for the failure.
Please contact support for further assistance.
Thank you.
Mohammad Aziz
February 27, 2008 at 9:07 am
The error you are now getting is caused by a length value for the 3rd parameter in the SUBSTRING function that is less then 1. Try replacing the CASE statment inside the VALUES clause with this CASE statement:
CASE WHEN @vcText = '0' THEN @vcText ELSE SUBSTRING(@vcText, 5, CHARINDEX(',', @vcText, 5) - 5) END
If you are still getting the invalid length error, then the error statement you are getting back has no commas in it. If this is the case, you need to review the various error statements and add addtional WHEN .... THEN clauses to parse the various error clauses.
Dave Novak
February 27, 2008 at 10:33 am
Hi!
Instead of giving database path I want to give database name by using parameter. Itried following script
DECLARE @vcText int
,@db varchar(50)
set @db='msdb'
EXEC @vcText = master..xp_restore_verifyonly @filename='\\ctsqladp01\Backup$\InstA\'+@db+'.lsb'
But It shows the error below:
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near '+'.
Can you please tell me how can I get rid of it?
Thank you
February 27, 2008 at 10:39 am
Try doing the concatenation before the execution.
DECLARE @vcText int,
@db varchar(50),
@FilePath VARCHAR(500)
SET @db='msdb'
SET @FilePath = '\\ctsqladp01\Backup$\InstA\'+@db+'.lsb'
EXEC @vcText = master..xp_restore_verifyonly @filename = @FilePath
February 27, 2008 at 2:35 pm
Hi Guys!
when I run this module to my database refresh job it runs good but when it get any bad backup file the job quit and shows the same error message in the backup history. Can anyone tell me how can I handle this bad backup file and run the job successfully.
Thank you.
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply