Need help with Convert command

Randy DoubRandy Doub Posts: 26
edited November 13, 2008 9:23AM in SQL Backup Previous Versions
I convert a .sqb to .bak every night. I have a job that: Step 1 backs up db to a .sqb and Step 2 Converts the .sqb to an MTF .bak. If either step reports failure they go to Step 3 which sends me an email, otherwise the job quits reporting success. The .bak gets restored later in the evening to another server not licensed for SQL Backup. I have a procedure that works but doesn't trap & report errors. So, for example, if the source file is not there, the job runs and reports success. I think my problem is I have to build the entire sqlbackup command in a string. I can't build a string and pass it to sqlbackup. So the exec (@cmd) was successful even though the code executed failed.

If I force an error by using a bad path for the source file and run the SP in query analyzer I get an error reported from SQL Backup:

output
NULL

SQL Backup v5.3.0.178
Verifying...
Error 530: File does not exist: (\\filcnc1s08pp001\sqlback01\SoftMedx\Nov_11_2008_SoftMed_FULL.SQB)


SQL Backup exit code: 530

name value
exitcode 530
sqlerrorcode 0
filename001 \\filcnc1s08pp001\sqlback01\SoftMedx\Nov_11_2008_SoftMed_FULL.SQB

If I run the same procedure as a job step it completes reporting success.

Here is the SP:
alter procedure khs_convert_sqb_sp
as
/*
	Convert the .sqb backup file created from Red-Gate's SQL Backup
	to an MTF .bak file to be restore on the SoftReport database.
*/

declare @cmd varchar(500)
declare @f1 varchar(200), @f2 varchar(200), @rtn int
declare @exitcode int, @sqlerrorcode int
select @f1 = '\\filcnc1s08pp001\sqlback01\SoftMed\' + replace(replace(convert(varchar(12),getdate(),107),' ','_'),',','') + '_SoftMed_FULL.SQB'
select @f2 = '\\filcnc1s08pp001\sqlback01\SoftMed\SoftReport_Restore.bak'

select @cmd = 'del ' + @f2
exec @rtn = master..xp_cmdshell @cmd
if @rtn <> 0
      begin
      raiserror('SoftReport Convert - Delete .bak failed', 16,1)
      return 1
      end
else
      begin
      select @exitcode OUT, @sqlerrorcode OUT
      select @cmd = 'master..sqlbackup ''-SQL "CONVERT ' + '''''' + @f1 + '''''' + ' to ' + '''''' + @f2 + '''''' + '"' + ''''
      exec (@cmd)
            if (@exitcode >= 500) or (@sqlerrorcode <> 0)
            begin
            raiserror('SoftReport Convert  - Convert .sqb failed', 16,1)
            return 1
            end
      end
· Share on Twitter

Comments

  • peteypetey Posts: 2,358 New member
    Change the following:
    select @cmd = 'master..sqlbackup ''-SQL "CONVERT ' + '''''' + @f1 + '''''' + ' to ' + '''''' + @f2 + '''''' + '"' + '''' 
exec (@cmd)
    to 
    select @cmd = '-SQL "CONVERT ' + '''''' + @f1 + '''''' + ' to ' + '''''' + @f2 + '''''' + '"' + ''
exec master..sqlbackup @cmd, @exitcode OUT, @sqlerrorcode OUT
    This allows you to retrieve the exit code and sql error codes, and respond accordingly.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • Randy DoubRandy Doub Posts: 26
    Thanks for looking into my question. Your example gives a syntax error. When I correct the syntax as I think it should be, I get error 870 (No command passed to SQL Backup). Is it possible to pass a variable to sqlbackup as the -SQL parameter? I manipulate the @cmd string to be a syntactically correct sqlbackup -SQL command and I get exitcode error 870 and a list of parameter options or else I've set the string up incorrectly and get a syntax error. This is only when I try to incorporate @exitcode and @sqlerrorcode.

    This works: 
    select @cmd = 'master..sqlbackup ''-SQL "CONVERT ' + '''''' + @f1 + '''''' + ' to ' + '''''' + @f2 + '''''' + '"' + ''''
exec (@cmd)

    I just can't get the exit and error codes with it.
    · Share on Twitter
  • Matthew FlattMatthew Flatt Posts: 255 Bronze 5
    Hi,

    Try this :

    SET @cmd = '-SQL "CONVERT ''' + @f1 + ''' TO ''' + @f2 + '''"'

EXEC MASTER..sqlbackup @cmd, @exitcode OUT, @sqlerrorcode OUT

    I hope it helps,
    Matthew Flatt
    Redgate Foundry
    · Share on Twitter
  • Randy DoubRandy Doub Posts: 26
    Matthew,

    It works perfectly. A single quote solution! I never even attempted that angle. I kept creating the -SQL parameter with the same syntax that the GUI creates. You can't imagine my frustration quoting quotes, always thinking a few more in the right place would get the string '''just' + ' ' + 'right'''.

    Thank you very much.
    · Share on Twitter
Sign In or Register to comment.