capture the message associated with the error

DoraMDoraM Posts: 4
edited January 22, 2008 7:56PM in SQL Backup Previous Versions
Hi, My backup runs as a t-SQL job with the following syntax.

select @sql = '-SQL "BACKUP DATABASE ADMIN_COMPONENTS TO DISK = ''D:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\dbname_200710.sqb''
WITH COMPRESSION = 1, ERASEFILES = 3, VERIFY"'

EXEC MASTER..SQLBACKUP @SQL ,@exit_code OUTPUT,@sql_error OUTPUT

Is there away to get the error message associated with the exit code programmatically ?

Comments

  • peteypetey Posts: 2,358 New member
    There isn't a function to return the error message given an error code. One suggestion is to store the output of the process to a temporary table, and store it to a permanent table if an error/warning is detected.

    The key is to use the SINGLERESULTSET option, so that the output can be stored to a table directly. E.g.
    DECLARE @sql nvarchar(512)
    DECLARE @exit_code int
    DECLARE @sql_error int
    
    CREATE TABLE #sqbtemp (output nvarchar(256))
    
    SET @sql = N'-SQL "BACKUP DATABASE ADMIN_COMPONENTS TO DISK = ''D:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\dbname_200710.sqb'' 
    WITH COMPRESSION = 1, ERASEFILES = 3, VERIFY, SINGLERESULTSET"' 
    
    INSERT INTO #sqbtemp EXEC master..sqlbackup @sql, @exit_code OUTPUT, @sql_error OUTPUT
    
    IF (@exit_code <> 0) OR (@sql_error <> 0)
    	INSERT INTO sqbbackuperrors SELECT GETDATE(), * FROM #sqbtemp
    DROP TABLE #sqbtemp
    
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Server: Msg 213, Level 16, State 7, Procedure sqlbackup, Line 23
    Insert Error: Column name or number of supplied values does not match table definition.


    Thats the error I get when I try to run the command with singleresultset option.

    thanks,
    Dora.
  • peteypetey Posts: 2,358 New member
    What is the result when you run just the backup e.g.
    EXEC master..sqlbackup '-SQL "BACKUP DATABASE ADMIN_COMPONENTS TO DISK = ''D:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\dbname_200710.sqb"'
    
    How many result sets were returned?

    Thanks.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • The backup itself happens with no problem. It returns 2 result sets even when I specify SINGLERESULTSET option.
  • peteypetey Posts: 2,358 New member
    When you run SQL Backup from Query Analyzer/Management Studio, what is the name of the column in the first result set?

    Thanks.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
Sign In or Register to comment.